Postgresql Parameterized Order By/Limit in Table Function

PostgreSQL parameterized Order By / Limit in table function

There is nothing wrong with a plpgsql function for anything a little more complex. The only situation where performance can suffer is when a plpgsql function is nested, because the query planner cannot further optimize the contained code in the context of the outer query which may or may not make it slower.

More details in this later answer:

• Difference between language sql and language plpgsql in PostgreSQL functions

This is much simpler than lots of CASE clauses in a query:

CREATE OR REPLACE FUNCTION get_stuff(_param text, _orderby text, _limit int)
  RETURNS SETOF stuff AS
$func$
BEGIN
   RETURN QUERY EXECUTE '
      SELECT *
      FROM   stuff
      WHERE  col = $1
      ORDER  BY ' || quote_ident(_orderby) || ' ASC
      LIMIT  $2'
   USING _param, _limit;
END
$func$  LANGUAGE plpgsql;

Call:

SELECT * FROM get_stuff('hello', 'col2', 100);

Notes

Use RETURN QUERY EXECUTE to return the results of query in one go.

Use quote_ident() for identifiers to safeguard against SQLi.

Or format() for anything more complex. See:

• Table name as a PostgreSQL function parameter

Pass parameter values with the USING clause to avoid casting, quoting and SQLi once again.

Be careful not to create naming conflicts between parameters and column names. I prefixed parameter names with an underscore (_) in the example. Just my personal preference.

Your second function after the edit cannot work, because you only return parent while the return type is declared SETOF stuff. You can declare any return type you like, but actual return values have to match the declaration. You might want to use RETURNS TABLE for that.

PLPGSQL Function: Use Input Parameter in ORDER BY

Per the link, change your select thusly:

ORDER BY
   CASE WHEN p_sort_order = 'asc' THEN min(t.field2) END ASC,
   CASE WHEN p_sort_order = 'desc' THEN min(t.field2) END DESC
LIMIT p_limit;

The magic here is that both case instances return either test or null with either asc or desc following. If the p_sort_order is desc, then you get:

  ORDER BY 
    NULL ASC,
    test DESC

The NULL ASC makes all rows equal in collation, so the order by falls through to the test DESC term.

This works for me on PG10 (updated per your comment):

create or replace function do_sort(p_sort_order text)
  returns table (id int, name text)
as $$
  begin
    return query select users.id, min(users.email)
                   from users
                  group by users.id
                  order by case when p_sort_order = 'asc' then min(users.email) end asc,
                           case when p_sort_order = 'desc' then min(users.email) end desc
                  limit 10;
end; $$ language plpgsql;
CREATE FUNCTION
Time: 10.849 ms

select * from do_sort('desc');

┌───────┬───────────────────────┐
│  id   │         name          │
├───────┼───────────────────────┤
│     9 │ 9user@example.com     │
│    99 │ 99user@example.com    │
│   999 │ 999user@example.com   │
│  9999 │ 9999user@example.com  │
│ 99999 │ 99999user@example.com │
│ 99998 │ 99998user@example.com │
│ 99997 │ 99997user@example.com │
│ 99996 │ 99996user@example.com │
│ 99995 │ 99995user@example.com │
│ 99994 │ 99994user@example.com │
└───────┴───────────────────────┘
(10 rows)

Time: 87.236 ms

select * from do_sort('asc');

┌────────┬────────────────────────┐
│   id   │          name          │
├────────┼────────────────────────┤
│ 100000 │ 100000user@example.com │
│ 100001 │ 100001user@example.com │
│ 100002 │ 100002user@example.com │
│ 100003 │ 100003user@example.com │
│ 100004 │ 100004user@example.com │
│  10000 │ 10000user@example.com  │
│  10001 │ 10001user@example.com  │
│  10002 │ 10002user@example.com  │
│  10003 │ 10003user@example.com  │
│  10004 │ 10004user@example.com  │
└────────┴────────────────────────┘
(10 rows)

Time: 85.219 ms

POSTGRESQL limit a resulting query given a condition

You can use row_number():

SELECT t.* 
FROM (SELECT t.*, ROW_NUMBER() OVER (ORDER BY ?) as seqnum
      FROM table t
     ) t
WHERE (extract(hour from now()) = 9 AND seqnum <= 143) OR
      (extract(hour from now()) = 10 AND seqnum <= 178) OR
      . . . 

The more likely solution would be to handle this at the application layer.

Note the ?: this represents the column to use for ordering the data. Normally when using LIMIT, you want ORDER BY.

Dynamic ORDER BY and ASC / DESC in a plpgsql function

I would do it like this:

CREATE OR REPLACE FUNCTION list(
      _category varchar(100)
    , _limit int
    , _offset int
    , _order_by varchar(100)
    , _order_asc_desc text = 'ASC')  -- last param with default value
  RETURNS TABLE(id int, name varchar, clientname varchar, totalcount bigint)
  LANGUAGE plpgsql AS
$func$
DECLARE
   _empty text := '';
BEGIN
   -- Assert valid _order_asc_desc
   IF upper(_order_asc_desc) IN ('ASC', 'DESC', 'ASCENDING', 'DESCENDING') THEN
      -- proceed
   ELSE
      RAISE EXCEPTION 'Unexpected value for parameter _order_asc_desc.
                       Allowed: ASC, DESC, ASCENDING, DESCENDING. Default: ASC';
   END IF;
   
   RETURN QUERY EXECUTE format(
     'SELECT id, name, clientname, count(*) OVER() AS full_count
      FROM   design_list
      WHERE ($1 = $2 OR category ILIKE $1) 
      ORDER  BY %I %s
      LIMIT  %s
      OFFSET %s'
    , _order_by, _order_asc_desc, _limit, _offset)
   USING _category, _empty;
END
$func$;

Core feature: use format() to safely and elegantly concatenate your query string. Related:

• INSERT with dynamic table name in trigger function
• Format specifier for integer variables in format() for EXECUTE?

ASC / DESC (or ASCENDING / DESCENDING) are fixed key words. I added a manual check (IF ...) and later concatenate with a simple %s. That's one way to assert legal input. For convenience, I added an error message for unexpected input and a parameter default, so the function defaults to ASC if the last parameter is omitted in the call. Related:

• Optional argument in PL/pgSQL function
• ERROR: input parameters after one with a default value must also have defaults in Postgres

Addressing Pavel's valid comment, I concatenate _limit and _offset directly, so the query is already planned with those parameters.

_limit and _offset are integer parameters, so we can use plain %s without the danger of SQL injection. You might want to assert reasonable values (exclude negative values and values too high) before concatenating ...

Other notes:

• Use a consistent naming convention. I prefixed all parameters and variables with an underscore _, not just some.

• Not using table qualification inside EXECUTE, since there is only a single table involved and the EXECUTE has its separate scope.

• I renamed some parameters to clarify. _order_by instead of _sort_by; _order_asc_desc instead of _order.

Returning table with specific parameters from a PostgreSQL function

A function declared to RETURN TABLE can return 0-n rows.

You must actively return rows, or nothing will be returned (no row). One way to do this:

RETURN NEXT; -- as last line before END;

There are other ways, see the manual.

However, it seems you want to return exactly one row every time. So rather use OUT parameters:

CREATE OR REPLACE FUNCTION public toggle2(uid numeric, gid numeric, OUT follow int, OUT game_name text) AS ...

Then it's enough to assign those OUT parameters, they are returned in the single result row automatically.

See:

• Returning from a function with OUT parameter
• plpgsql error "RETURN NEXT cannot have a parameter in function with OUT parameters" in table-returning function
• How to return result of a SELECT inside a function in PostgreSQL?

Passing the table as a parameter

AS @dezso mentioned, you'll need dynamic SQL in this case.

Dynamic SQL with EXECUTE

So, you're on the right track; forming a dynamic SQL statement using PL/pgSQL, but you just need the finishing touches:

CREATE or REPLACE FUNCTION convert_from_lon_lat(long float, lat float, _table text) 
RETURNS integer AS $$
BEGIN
RETURN QUERY EXECUTE format('SELECT id FROM %I AS vertices 
  ORDER BY vertices.geom <->ST_SetSrid(ST_MakePoint(long,lat),4326) LIMIT 1;',_table);
END
$$ LANGUAGE plpgsql;

I believe this should solve your issues.

Note: We've discovered an error with the above solution and using SETOF, I've attempted to correct the issues below.

EDIT:

A few edits here, hopefully one solution will fix your issue. Also, please excuse any syntax errors in my previous & current solutions; I don't have time to test them right now. :(

1) You could just try returning a SETOF integers, knowing that of course you'll only return the one. Your return type in this case will then be a single, one-column row containing an integer.

CREATE or REPLACE FUNCTION convert_from_lon_lat(long float, lat float, _table text) 
RETURNS SETOF integer AS $$
BEGIN
RETURN QUERY EXECUTE format('SELECT id FROM %I AS vertices 
  ORDER BY vertices.geom <->ST_SetSrid(ST_MakePoint(long,lat),4326) LIMIT 1;',_table);
END
$$ LANGUAGE plpgsql;

and then call as:

SELECT * FROM convert_from_lon_lat(...);

2) To specifically return an integer, I think you can try this:

CREATE or REPLACE FUNCTION convert_from_lon_lat(long float, lat float, _table text) 
RETURNS integer AS $$

DECLARE
return_id integer;

BEGIN
EXECUTE format('SELECT id FROM %I AS vertices 
  ORDER BY vertices.geom <->ST_SetSrid(ST_MakePoint(long,lat),4326) LIMIT 1;',_table)
  INTO return_id;

RETURN return_id;
END
$$ LANGUAGE plpgsql;

What are my options for using dynamic sort field/order in postgresql?

You cannot use a query parameter to specify ASC or DESC in a query like this:

ORDER BY $3 $4

You should pass the direction as a boolean and construct the query like that:

sql := 'SELECT ... ORDER BY $3 '
       || CASE WHEN p_ascending THEN 'ASC' ELSE 'DESC' END
       || 'LIMIT ...';

---

Related Topics