CODEWITHSUNDEEP
postgresql
manthan davda
manthan davda

Reputation: 329

Dynamic Limit in PostgreSQL

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?

Upvotes: 8

Views: 10451

Answers (4)

Seethapriyanka Peddada
Seethapriyanka Peddada

Reputation: 590

I used the below approach in postgres

Create Function employees_limited(limit integer, offset interger, pagination boolean) RETURNS SET OF employees AS $$
    BEGIN
        if pagination = false then --skip offset and limit
            offset = 0;
            limit = 2147483647; -- int max value in postgres
        end if;
        RETURN QUERY 
        SELECT * FROM employees order by createddate 
        LIMIT (limit) Offset offset; 
      RETURN;
    END; $$ LANGUAGE plpgsql STRICT;

Upvotes: 0

Dmitry
Dmitry

Reputation: 125

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.

Upvotes: 2

fthiella
fthiella

Reputation: 49049

Please note that a SELECT with a LIMIT should always include an ORDER BY, because if not explicitly specified the order of the returned rows can be undefined.

Insead of LIMIT you could use ROW_NUMBER() like in the following query:

SELECT *
FROM (
  SELECT *, row_number() OVER (ORDER BY id) AS rn
  FROM Employees
) AS s
WHERE
  (rn>:offset AND rn<=:limit+:offset) OR :limit=0

Upvotes: 0

Patrick
Patrick

Reputation: 32199

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.

Upvotes: 6

Related Questions