Обсуждение: Order by parameter inside pgsql function ignored
Hi! I'm trying to use the order by parameter inside a function, but it is ignored. Any ideas why? And how can I sort by external parameters inside pgsql function? CREATE OR REPLACE FUNCTION "public"."test_order_by" (sort_key varchar) RETURNS SETOF "customers"."customers_with_mark_deleted" AS $body$ DECLARE rec RECORD; BEGIN FOR rec IN SELECT * FROM customers.customers_with_mark_deleted c ORDER BY sort_key ASC LOOP RETURN NEXT rec; END LOOP; RETURN; END; $body$ LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER COST 100 ROWS 1000; -- Best regards, Anton Marchenkov.
On Tuesday 2. June 2009, Anton Marchenkov wrote: >Hi! > >I'm trying to use the order by parameter inside a function, but it is >ignored. Any ideas why? And how can I sort by external parameters > inside pgsql function? What's the problem with SELECT * FROM foo(myvar) ORDER BY sort_key ASC ? If sort_key is the name of a column, you must use EXECUTE and build a dynamic query string like: FOR rec IN EXECUTE 'SELECT * FROM customers.customers_with_mark_deleted c ORDER BY ' || sort_key || ' ASC' -- Leif Biberg Kristensen | Registered Linux User #338009 Me And My Database: http://solumslekt.org/blog/
On Tuesday 2. June 2009, Anton Marchenkov wrote: >CREATE OR REPLACE FUNCTION "public"."test_order_by" (sort_key varchar) >RETURNS SETOF "customers"."customers_with_mark_deleted" AS >$body$ >DECLARE > rec RECORD; >BEGIN > FOR rec IN SELECT * FROM customers.customers_with_mark_deleted c > ORDER BY sort_key ASC > LOOP > RETURN NEXT rec; > END LOOP; > RETURN; >END; >$body$ >LANGUAGE 'plpgsql' >VOLATILE >CALLED ON NULL INPUT >SECURITY INVOKER >COST 100 ROWS 1000; By the way, there's no need to declare this function as VOLATILE, as it doesn't change anything in the database. STABLE will do just fine. I wrote: FOR rec IN EXECUTE 'SELECT * FROM customers.customers_with_mark_deleted c ORDER BY ' || sort_key || ' ASC' In case you're expecting CamelCased column names, you should also use the quote_ident() function: FOR rec IN EXECUTE 'SELECT * FROM customers_with_mark_deleted ORDER BY ' || quote_ident(sort_key) || ' ASC' LOOP ... -- Leif Biberg Kristensen | Registered Linux User #338009 Me And My Database: http://solumslekt.org/blog/
In response to Anton Marchenkov : > Hi! > > I'm trying to use the order by parameter inside a function, but it is > ignored. Any ideas why? And how can I sort by external parameters inside > pgsql function? You an use dynamic SQL with EXECUTE, for instance: create or replace function my_order (var_order text) ... as $$ declare my_sql text; begin ... my_sql := 'select ... from table order by ' || var_order'; execute my_sql; ... Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net