Function with dynamic command (EXECUTE) not working
| От | Robert Blixt |
|---|---|
| Тема | Function with dynamic command (EXECUTE) not working |
| Дата | |
| Msg-id | 20051102144251.B1866D830C@svr1.postgresql.org обсуждение исходный текст |
| Список | pgsql-sql |
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
В списке pgsql-sql по дате отправления: