From: Robert Blixt <robert.blixt@transpa.se>
To: pgsql-sql@postgresql.org
Subject: Function with dynamic command (EXECUTE) not working
Date: Wed, 2 Nov 2005 15:40:50 +0100
Message-ID: <20051102144251.B1866D830C@svr1.postgresql.org> (raw)
Hello,
I am trying to create a function that will allow
me to dynamically choose the ORDER BY sequence.
I also want the result of the SELECT statement to
be returned. Far as I can tell this can not be done
with EXECUTE alone rather I should use FOR .. IN EXECUTE.
The result of the statement is a single column of
type varchar. The result amount can be 0 - n.
However, I can not get it to work
This is pretty much how far I have come..
[CODE]
CREATE OR REPLACE FUNCTION "public"."testfunction"(
lisnotactive boolean,
lorderby1 varchar,
lorderby2 varchar
)
RETURNS SETOF record AS
$BODY$
DECLARE
rRec RECORD;
BEGIN
FOR rRec IN EXECUTE( 'SELECT DISTINCT stationplace.name FROM stationplace,
employee WHERE employee.isnotactive = ' || lisnotactive ||
'ORDER BY ' || quote_ident(lorderby1) || ', ' || quote_ident(lorderby2)
) LOOP
END LOOP;
RETURN NEXT rRec;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
[/CODE]
Any help is highly appreciated.
Kind Regards,
Robert
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: robert.blixt@transpa.se
Subject: Re: Function with dynamic command (EXECUTE) not working
In-Reply-To: <20051102144251.B1866D830C@svr1.postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox