Question 260642 in PostgreSQL Question posted in
The official documentation can be found here.

How to enter "desc" parameter in postgresql function?

I need to enter value for direction of sort in a function, but how to do it?

Tried like that, but this doesn’t work of course.

create or replace function invest_ideas 
(order_by_direction varchar default 'desc')

create or replace function invest_ideas 
(order_by_direction RECORD default desc)

in query line looks like that:

order by creation_time order_by_direction
Tags:

2

Answers


  1. 0

    You can do:

    SELECT *
FROM test
ORDER BY id * CASE WHEN order_by_direction='desc' THEN -1 ELSE 1 END

    For a life test see: DBFIDDLE

    Login or Signup to reply.
  2. 0

    Use dynamic SQL with format:

    create table created_at(creation_time timestamp);
insert into created_at values (now()), (now() - interval '1 day'), (now() + interval '1 day');

CREATE OR REPLACE FUNCTION public.order_fnc(order_by_direction character varying DEFAULT 'desc'::character varying)
 RETURNS SETOF timestamp without time zone
 LANGUAGE plpgsql
AS $function$
BEGIN
    RETURN QUERY 
        EXECUTE format(
        'SELECT creation_time FROM created_at ORDER BY creation_time  %s', 
        order_by_direction);
END;
$function$
;


select order_fnc('asc');
         order_fnc          
----------------------------
 10/06/2023 14:04:25.123912
 10/07/2023 14:04:25.123912
 10/08/2023 14:04:25.123912

select order_fnc();
         order_fnc          
----------------------------
 10/08/2023 14:04:25.123912
 10/07/2023 14:04:25.123912
 10/06/2023 14:04:25.123912
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search