Re: return query execute SQL-problem

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
> owner@postgresql.org] On Behalf Of Maximilian Tyrtania
> Sent: Tuesday, October 16, 2012 3:44 AM
> To: pgsql-general@postgresql.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.