Dynamic Limit in PostgreSQL

Question

I have function returning list of Employees, My requirement is if i pass Limit to function than i should get result with limit and offset, If i don't pass limit than all the rows should be returned

for example

When Limit is greater than 0(I am passing Limit as 10)
Select * from Employees
Limit 10 offset 0

When Limit is equal to 0 than
Select * from Employees

Is their any way to do such logic in function?

Answer 1

Yes, you can pass an expression for the LIMIT and OFFSET clauses, which includes using a parameter passed in to a function.

CREATE FUNCTION employees_limited(limit integer) RETURNS SET OF employees AS $$
BEGIN
  IF limit = 0 THEN
    RETURN QUERY SELECT * FROM employees;
  ELSE
    RETURN QUERY SELECT * FROM employees LIMIT (limit)
  END IF;
  RETURN;
END; $$ LANGUAGE plpgsql STRICT;

Note the parentheses around the LIMIT clause. You can similarly pass in an OFFSET value.

This example is very trivial, though. You could achieve the same effect by doing the LIMIT outside of the function:

SELECT * FROM my_function() LIMIT 10;

Doing this inside of a function would really only be useful for a complex query, potentially involving a large amount of data.

Also note that a LIMIT clause without an ORDER BY produces unpredictable results.

Answer 2

Sorry I can't comment. Solution is almost provided by Patrick.
First we should write function to return result without limitation.

CREATE FUNCTION test () 
RETURNS TABLE (val1 varchar, val2 integer) AS $$
    BEGIN
    RETURN QUERY SELECT val1, val2 FROM test_table;
    END;
$$ LANGUAGE plpgsql;

Then we have to write wrapper function, which will process limitation.

CREATE FUNCTION test_wrapper (l integer DEFAULT 0)
RETURNS TABLE (name varchar, id integer) AS $$
    BEGIN
        IF l = 0 THEN
            RETURN QUERY SELECT * FROM test(); -- returns everything
        ELSE
            RETURN QUERY SELECT * FROM test() LIMIT (l); -- returns accordingly
        END IF;
    END;
$$ LANGUAGE plpgsql;

In my case I needed to return tables as final result, but one can get anything required as return from wrapper function.