Re: return query execute SQL-problem

> -----Original Message-----
> From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-
> owner(at)postgresql(dot)org] On Behalf Of Maximilian Tyrtania
> Sent: Tuesday, October 16, 2012 3:44 AM
> To: pgsql-general(at)postgresql(dot)org
> Subject: [GENERAL] return query execute SQL-problem
>
> Hi there,
>
> here is something I don't quite grasp (PG 9.1.3): This function:
>
> CREATE OR REPLACE FUNCTION f_aliastest()
> RETURNS setof text AS
> $BODY$
> declare sql text;
> begin
> sql:='SELECT ''sometext''::text as alias';
> return query execute SQL;
> end;
> $BODY$
> LANGUAGE plpgsql VOLATILE;
>
> returns its result as:
>
> contactking=# select * from f_aliastest();
>
> f_aliastest
> -------------
> sometext
> (1 row)
>
> I was hoping I'd get the data back as 'alias', not as 'f_aliastest'. If I
do:
>
> contactking=# select alias from f_aliastest();
> ERROR: column "alias" does not exist
> LINE 1: select alias from f_aliastest();
>
> Is there a way that I can make my function return the field aliases?
>
> Best wishes from Berlin,
>
> Maximilian Tyrtania
> http://www.contactking.de

Use the "RETURNS TABLE" form of the output definition:

CREATE FUNCTION ...
RETURNS TABLE (alias varchar)
AS $$ ... $$

There is no way to make the name dynamic or to specify it using the contents
of the function body.

David J.