How do I return a table from a function with a bespoke column name?

Question

This function works:

CREATE OR REPLACE FUNCTION public.a()
 RETURNS TABLE(a text)
 LANGUAGE plpgsql
AS $function$
BEGIN
    return query execute
    'select a from ztable';
END;
$function$;

But when I try to add some text to the column name:

CREATE OR REPLACE FUNCTION public.a(prefix text)
 RETURNS TABLE(a text)
 LANGUAGE plpgsql
AS $function$
BEGIN
    return query execute
    'select a as $1_a from ztable' using prefix;
END;
$function$;

This just fails as a syntax error on $1.
Or:

CREATE OR REPLACE FUNCTION public.a(prefix text)
 RETURNS TABLE(a text)
 LANGUAGE plpgsql
AS $function$
BEGIN
    return query execute
    'select a as '||prefix||'_a from ztable';
END;
$function$;

select * from a('some prefix') doesn't work.
Is there some other syntax that does the job?

Answer 1

That's simply not possible. SQL does not allow dynamic column names.

You must assign a column alias with the call. Like:

SELECT a AS prefix_a FROM public.a();

Or in a column definition list directly attached to the function:

SELECT * FROM public.a() AS f(prefix_a);

Or, while dealing with a single output column, even just:

SELECT * FROM public.a() AS prefix_a;

See:

• RETURNING rows using unnest()?