How to Efficiently Order and Limit Results from a PostgreSQL Table Function?

PostgreSQL Parameterized Ordering and Limiting in Table Functions

Question:

How can one efficiently order and limit results when utilizing a SQL table function like getStuff? Ordering and limiting outside the function or using plpgsql are being considered. Is there a better approach?

Answer:

Despite its initial appearance, a plpgsql function is the preferred option for handling more complex scenarios. Performance may only suffer when plpgsql functions are nested, where the query planner cannot fully optimize code within the context of the outer query.

A simplified plpgsql function that avoids excessive CASE clauses:

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 = 
      ORDER  BY ' || quote_ident(_orderby) || ' ASC
      LIMIT  '
   USING _param, _limit;
END
$func$  LANGUAGE plpgsql;

Usage:

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

Notes:

• Use RETURN QUERY EXECUTE to return query results in a single operation.
• Ensure identifiers are SQLi-proof with quote_ident().
• Utilize USING to pass parameter values, preventing casting and SQLi.
• Avoid naming conflicts between parameters and column names.
• The initial edited function fails because it returns parent but is declared to return SETOF stuff. Consider using RETURNS TABLE instead.

The above is the detailed content of How to Efficiently Order and Limit Results from a PostgreSQL Table Function?. For more information, please follow other related articles on the PHP Chinese website!