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.
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:
$function$after theEND; 2) The '$$ .. $$' around the query is not needed. 3) The'$1'should be just$1. See plpgsql structure and [Return query](43.6.1.2. RETURN NEXT and RETURN QUERY) 43.6.1.2. RETURN NEXT and RETURN QUERY.