Thread: 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
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
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
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 realizedthat the problem is not limited to return query execute SQL but to return query in general. 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.