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
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
Thread-index: AcXfu2uXZGE6sF5BSfey3CPEu4wgKg==
Message-Id: <20051102144251.B1866D830C@svr1.postgresql.org>


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