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:
In the case at hand 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.
If your function is stable (does not modify the database), the query planner will typically inline it. Therefore, doing SELECT * FROM getStuff('x') LIMIT 10
will produce the same query plan as if the limit were inside getStuff()
.
However, you need to tell PG your function is stable by declaring it as such:
CREATE OR REPLACE FUNCTION getStuff(param varchar)RETURNS setof STUFFLANGUAGE SQLSTABLEAS $$ ... $$;
Now doing EXPLAIN SELECT * FROM getStuff('x') LIMIT 1
should produce the same query plan as writing out the equivalent query would.
The inlining should also work for ORDER BY
clauses outside the function. But if you wanted to parameterize the function to determine the order by, you could do it like this to also control the sort direction:
CREATE FUNCTION sort_stuff(sort_col TEXT, sort_dir TEXT DEFAULT 'asc')RETURNS SETOF stuffLANGUAGE SQLSTABLEAS $$ SELECT * FROM stuff ORDER BY -- Simplified to NULL if not sorting in ascending order. CASE WHEN sort_dir = 'asc' THEN CASE sort_col -- Check for each possible value of sort_col. WHEN 'col1' THEN col1 WHEN 'col2' THEN col2 WHEN 'col3' THEN col3 --- etc. ELSE NULL END ELSE NULL END ASC, -- Same as before, but for sort_dir = 'desc' CASE WHEN sort_dir = 'desc' THEN CASE sort_col WHEN 'col1' THEN col1 WHEN 'col2' THEN col2 WHEN 'col3' THEN col3 ELSE NULL END ELSE NULL END DESC$$;
As long as sort_col
and sort_dir
are constant within the query, the query planner should be able to simplify the verbose looking query to
SELECT *FROM stuffORDER BY <sort_col> <sort_dir>
which you can verify using EXPLAIN
.
As to the ORDER BY
you could try something like this:
SELECT <column list>FROM StuffWHERE col1 = $1ORDER BY CASE $2 WHEN 'col1' THEN col1 WHEN 'col2' THEN col2 WHEN 'col3' THEN col3 ELSE col1 -- Or whatever your default should be END
You might have to do some data type conversions so that all of the data types in the CASE
result match. Just be careful about converting numerics to strings - you'll have to prepend 0s to make them order correctly. The same goes for date/time values. Order by a format that has year followed by month followed by day, etc.
I've done this in SQL Server, but never in PostgreSQL, and I don't have a copy of PostgreSQL on this machine, so this is untested.