up vote 4 down vote favorite
2

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.

share|improve this question
95% accept rate
1  
Why is using PL/pgSQL and EXECUTE not a nice approach? Should not make a big difference in terms of performance and is the only solution I can think of. – a_horse_with_no_name Nov 15 '11 at 16:30
1  
using EXECUTE will be a bit slower (because of the additional parsing going on), but I doubt you'll be able to measure the difference. – a_horse_with_no_name Nov 15 '11 at 16:37
2  
@JoshuaBoshi: Guessing about performance impact doesn't usually work well. – Catcall Nov 15 '11 at 16:41
1  
Ok:-) feel free to write an answer and I will accept it, if nobody will come up with other solution :-) – JoshuaBoshi Nov 15 '11 at 16:42
1  
@JoshuaBoshi - You'd have to test it for your particular cases. Amount of data, allowable combinations of sorting fields, available indexes, fragmentation of data, etc, can all have an impact. Dynamic SQL with EXECUTE will invariably yield a plan that performs equal to or better than a single CASE based expression. But it feels messier and so can be harder to maintain. Testing would show the performance differences. I often value maintenance over performance if the performance differences are not marked. – Dems Nov 15 '11 at 17:44
show 5 more comments
feedback

3 Answers

up vote 4 down vote accepted

There is nothing wrong with a plpgsql function. It is the most elegant and fastest solution for anything a little more complex. The only situation where performance could suffer is when you nest the output of set returning functions because the query planner cannot further optimize the innards of the function.

Other than that, it is much simpler than lots of CASE clauses in a query:

CREATE OR REPLACE FUNCTION getStuff(_param text, _orderby text, _limit int)
  RETURNS SETOF stuff AS
$BODY$
BEGIN

RETURN QUERY EXECUTE '
    SELECT *
    FROM   stuff
    WHERE  col = $1
    ORDER  BY ' || quote_ident(_orderby) || '
    LIMIT  $2'
USING _param, _limit;

END;
$BODY$
  LANGUAGE plpgsql;

Call:

SELECT * FROM getStuff('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.
  • Use USING to hand in parameter values to avoid casting, quoting and SQLi once more.
  • Be careful not to create naming conflicts between parameters and columns names. I prefixed my parameter names with "_" in the example.

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 the actual return values have to match that declaration. You might want to use RETURNS TABLE for that.

share|improve this answer
Brandstetter: Wow, now you have learned me some plpgsql :-) I have had no idea about RETURN QUERY EXECUTE and USING. This is really elegant solution, and i have no worries about plpgsql method now. Thank you very much! – JoshuaBoshi Nov 16 '11 at 10:02
feedback
up vote 2 down vote

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;
share|improve this answer
1) you need to replace 1 with a. 2) This will prevent the optimiser from being able to use indexes, etc. It does yield a single query for multiple purposes, but one should this against dynamic sql to check what the performance overhead is. (Only one plan can be created for each single query, but different order by clauses may require different plans to be efficient.) – Dems Nov 15 '11 at 16:54
It's correct, it orders by first column descending or ascending depending on whether a is equal to 'asc' or not. but i'll edit anyway to make it clearer. – soulcheck Nov 15 '11 at 16:59
feedback
up vote 1 down vote

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.

share|improve this answer
To avoid the data type problem... ORDER BY CASE WHEN $2 = 'a' THEN a END, CASE WHEN $2 = 'b' THEN b END, etc, etc. But note, this has the same optimisation problem as I mentioned on soulcheck's answer. – Dems Nov 15 '11 at 16:56
That's not the data type problem that I was referring to. Obvisously $2 will always be the same data type. If column a and column b are different data types though then it might cause some issues. – Tom H. Nov 15 '11 at 17:08
1  
The example I gave deals with that by having each field as a separate clause in the ORDER BY. The CASE statements yield ORDER BY null, null, x, null (for example), and so results in data type independence. – Dems Nov 15 '11 at 17:13
Thank you for your answers and comments. I am using Dems's version of CASE but postgresql throws: ERROR: CASE types character varying and integer cannot be matched when i try to create the function (on position after THEN - weird). I will edit the question and add full source... – JoshuaBoshi Nov 15 '11 at 17:20
Exactly the issue that I was talking about. You need to make sure that you convert all of the results to the same data type. – Tom H. Nov 15 '11 at 17:41
show 3 more comments
feedback

Your Answer

 
or
required, but never shown
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.