How do I bind a variable in postgres functions-CodePudding

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.

CodePudding user response：

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.