PostgreSQL parameterized Order By / Limit in table function
I have sql procedure that do regular 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 as it would be normal select statement with `order by` and `limit` clauses?

My guess is that query:

    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 also if I am right, there is no easy way how to pass the orderby by argument of 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 very nice aproach 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.

Thank you for every idea,
Josh
PostgreSQL parameterized Order By / Limit in table function
===

I have sql procedure that do regular 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 as it would be normal select statement with `order by` and `limit` clauses?

My guess is that query:

    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 also if I am right, there is no easy way how to pass the orderby by argument of 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 very nice aproach 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.

Thank you for every idea,
Josh