CODEWITHSUNDEEP
postgresqlplpgsql
Henrietta Martingale
Henrietta Martingale

Reputation: 891

How to bind a variable in Postgres functions?

Here's my code:

CREATE OR REPLACE FUNCTION public.view_columns_f(viewname text)
 RETURNS TABLE(columnn_name text, data_type text)
 LANGUAGE plpgsql
AS $function$
BEGIN
    return query execute
    $$SELECT attname, format_type(atttypid, atttypmod) AS data_type
    FROM   pg_attribute
    WHERE  attrelid = '$1'::regclass$$
    using viewname;
END;

The error is relation "$1" doesn't exist, because I'm not binding it correctly.

Upvotes: 0

Views: 2009

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656321

Adrian pointed out a couple of problems, I fixed a couple more:

CREATE OR REPLACE FUNCTION public.view_columns_f(viewname regclass)
  RETURNS TABLE (columnn_name name, data_type text)
  LANGUAGE plpgsql AS
$func$
BEGIN
   RETURN QUERY
   SELECT attname, format_type(atttypid, atttypmod)  -- AS data_type
   FROM   pg_attribute
   WHERE  attrelid = $1
   AND    NOT attisdropped  -- exclude deleted columns
   AND    attnum > 0        -- exclude internal system columns
   ORDER  BY attnum;        -- original order
END
$func$;

Call:

SELECT * FROM public.view_columns_f('my_view');

Most importantly, you don't need dynamic SQL at all, luckily. Get a grip on plain PL/pgSQL first, before playing with trickier dynamic SQL.

Could be a simpler SQL function, nothing requires PL/pgSQL.

The function name is misleading. You get columns for any registered relation this way, not just for a view.

Further reading:

Upvotes: 1

Related Questions