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 there any other method of achieving this? Or what option would you choose? Ordering/limiting outside the function, or plpgsql?

I am using postgresql 9.1.

Edit

I modified the CREATE FUNCTION statement like this:

CREATE OR REPLACE FUNCTION getStuff(param character varying, orderby character varying)
  RETURNS SETOF stuff AS
$BODY$
    select t.*
    from stuff t
    where col = $1
    ORDER BY
        CASE WHEN $2 = 'parent' THEN t.parent END,
        CASE WHEN $2 = 'type' THEN t."type" END, 
        CASE WHEN $2 = 'title' THEN t.title END

$BODY$
  LANGUAGE sql;

This throws:

ERROR: CASE types character varying and integer cannot be matched ŘÁDKA 13: WHEN $1 = 'parent' THEN t.parent

The stuff table looks like this:

CREATE TABLE stuff
    (
      id integer serial,
      "type" integer NOT NULL,
      parent integer,
      title character varying(100) NOT NULL,
      description text,
      CONSTRAINT "pkId" PRIMARY KEY (id),
    )

Edit2

I have badly read Dems code. I have corrected it to question. This code is working for me.

Answer 1

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.

Answer 2

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 STUFF
LANGUAGE SQL
STABLE
AS $$ ... $$;

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 stuff
LANGUAGE SQL
STABLE
AS $$
    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 stuff
ORDER BY <sort_col> <sort_dir>

which you can verify using EXPLAIN.

Answer 3

As to the ORDER BY you could try something like this:

SELECT
    <column list>
FROM
    Stuff
WHERE
    col1 = $1
ORDER 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.

Answer 4

Using Format function Even with ilike fancy operator.

CREATE OR REPLACE FUNCTION get_customer(
      _param text, _orderby text, _limit int)
      RETURNS SETOF customer AS
$func$

BEGIN
   RETURN QUERY EXECUTE format('
      SELECT *
      FROM   customer
      WHERE  first_name ilike ''%%%s%%''
      ORDER  BY  %I DESC
      LIMIT  %L',
      _param, _orderby, _limit );
END
$func$  LANGUAGE plpgsql;

Format reference:https://www.postgresql.org/docs/current/functions-string.html

Answer 5

You can pass limit value as a function argument without any problems. As for ordering you can use ODER BY in combination with CASE statement. This unfortunately won't work for something like

ORDER BY CASE condition_variable
WHEN 'asc' THEN column_name ASC
ELSE column_name DESC
END;