return query execute SQL-problem

On 2012-10-16 10:44, Maximilian Tyrtania wrote:

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

You can use AS
select f_aliastest() AS alias;

Regards,
C

Am 16.10.2012 um 10:56 schrieb Condor <condor@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

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/

Am 16.10.2012 um 11:37 schrieb hubert depesz lubaczewski <depesz@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

-----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.