Обсуждение: 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