PostgreSQL parameterized Order By / Limit in table function

by

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:

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:

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.

More Related Contents:

  1. Refactor a PL/pgSQL function to return the output of various SELECT queries
  2. DROP FUNCTION without knowing the number/type of parameters?
  3. COPY with dynamic file name
  4. How to check if a table exists in a given schema
  5. Difference between language sql and language plpgsql in PostgreSQL functions
  6. How to return result of a SELECT inside a function in PostgreSQL?
  7. How to update selected rows with values from a CSV file in Postgres?
  8. Transpose latest rows per user to columns
  9. Pass multiple values in single parameter
  10. GROUP BY and aggregate sequential numeric values
  11. How can I send email from PostgreSQL trigger?
  12. PostgreSQL IF statement
  13. Record returned from function has columns concatenated
  14. Query that ignore the spaces
  15. How to find a table having a specific column in postgresql
  16. Postgres function returning table not returning data in columns
  17. INSERT COMMAND :: ERROR: column “value” does not exist
  18. PostgreSQL convert columns to rows? Transpose?
  19. Return multiple columns and rows from a function PostgreSQL instead of record
  20. Bulk/batch update/upsert in PostgreSQL
  21. Postgres Error: More than one row returned by a subquery used as an expression
  22. Grant privileges for a particular database in PostgreSQL
  23. Create PostgreSQL ROLE (user) if it doesn’t exist
  24. How to compare dates in datetime fields in Postgresql?
  25. PostgreSQL how to create a copy of a database or schema?
  26. How to perform a select query in a DO block?
  27. Postgres trigger after insert accessing NEW
  28. Temporal database design, with a twist (live vs draft rows)
  29. Postgres: convert single row to multiple rows (unpivot)
  30. PostgreSQL – dynamic value as table name [duplicate]

Leave a Comment