How do I bind a variable in postgres functions

Question

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.

Three errors 1) You don't have a closing `$function$` after the `END`; 2) The '$$ .. $$' around the query is not needed. 3) The `'$1'` should be just `$1`. See [plpgsql structure](https://www.postgresql.org/docs/current/plpgsql-structure.html) and [Return query](43.6.1.2. RETURN NEXT and RETURN QUERY) 43.6.1.2. RETURN NEXT and RETURN QUERY. — Adrian Klaver, Feb 16 '22 at 00:03
That should have been [Return query](https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING) 43.6.1.2. RETURN NEXT and RETURN QUERY — Adrian Klaver, Feb 16 '22 at 00:23

score 1 · Accepted Answer · answered Feb 16 '22 at 02:48

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.

How do I bind a variable in postgres functions

1 Answers1