return query execute SQL-problem

[Maximilian Tyrtania]

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



--
Sent via pgsql-general mailing list (pgsql-...@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Condor]

You can use AS
select f_aliastest() AS alias;


Regards,
C

[hubert depesz lubaczewski]

On Tue, Oct 16, 2012 at 09:44:03AM +0200, Maximilian Tyrtania wrote:
> Is there a way that I can make my function return the field aliases?

CREATE OR REPLACE FUNCTION f_aliastest() RETURNS setof text AS

function defined as above returns set of values without any name. name
is chosen by pg.
you can't rename the columns in the "return query" part, but you can by
changing definition of function to:
create or replace function f_aliastest(OUT alias TEXT) returns setof record AS ...

Best regards,

depesz

--
The best thing about modern society is how easy it is to avoid contact with it.
http://depesz.com/

[Maximilian Tyrtania]

Am 16.10.2012 um 11:37 schrieb hubert depesz lubaczewski <dep...@depesz.com>:

> create or replace function f_aliastest(OUT alias TEXT) returns setof record AS …

Ah, yes, I forgot about the OUT parameters, thanks for the reminder!

Maximilian Tyrtania
Contact King Software Entwicklung
Maximilian Tyrtania
http://www.contactking.de

[Maximilian Tyrtania]

Am 16.10.2012 um 10:56 schrieb Condor <con...@stz-bg.com>:

> You can use AS
> select f_aliastest() AS alias;

Yeah, thanks, well, my question is basically if there is a way to make the function alias-savvy. In the meantime I realized that the problem is not limited to return query execute SQL but to return query in general.

Maximilian Tyrtania
http://www.contactking.de

[David Johnston]

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.