PostgreSQL parameterized Order By / Limit in table function

Question

I have a sql function that does a simple sql select statement: ``` CREATE OR REPLACE FUNCTION getStuff(param character varying) RETURNS SETOF stuff AS $BODY$ select * from stuff where col = $1 $BODY$ LANGUAGE sql; ``` For now I am invoking this function like this: ``` select * from getStuff('hello'); ``` What are my options if I need to order and limit the results with `order by` and `limit` clauses? I guess a query like this: ``` select * from getStuff('hello') order by col2 limit 100; ``` would not be very efficient, because all rows from table `stuff` will be returned by function `getStuff` and only then ordered and sliced by limit. But even if I am right, there is no easy way how to pass the order by argument of an sql language function. Only values can be passed, not parts of sql statement. Another option is to create the function in `plpgsql` language, where it is possible to construct the query and execute it via `EXECUTE`. But this is not a very nice approach either. So, is th…

Accepted Answer

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.

PostgreSQL parameterized Order By / Limit in table function

Solution

Problem

Related problems