PostgreSQL dynamic fetch foward count value

Question

I have the following function used to retrieve a batch of Ids from a table. This function is being used as the OFFSET and LIMIT clauses seem to offer poor performance.

CREATE OR REPLACE FUNCTION getBatch(_workloadId VARCHAR, _offSet INT, _limit INT)
  RETURNS SETOF NUMERIC AS $$
DECLARE 
    c SCROLL CURSOR FOR
      SELECT id FROM workload WHERE workload_id = $1 ORDER BY id ASC; 
BEGIN

OPEN c;
MOVE FORWARD $2 IN c; 
RETURN QUERY FETCH FORWARD 10 FROM c;

END;
$$ LANGUAGE plpgsql;

I want the FETCH FORWARDcount to be passed in as a parameter, but I'm unable to find a way to do this. Referencing $3 does not work, I've also tried the following:

EXECUTE 'RETURN QUERY FETCH FORWARD ' || $3 || ' FROM c;';

Any help would be greatly appreciated.

Hi Erwin, thanks for your reply. Sorry it's taken me so long to comment. The requirement changed meaning that the problem became moot. I appreciate the solutions you offered, however the FETCH FORWARD count did work other than the fact the count value couldn't be dynamic. So FETCH FORWARD 10 would happily return 10 results. — Jake Daly
– Jake Daly, Commented Apr 18, 2013 at 15:13

Erwin Brandstetter · Accepted Answer · 2022-05-12 00:15:49Z

You are confusing SQL cursors with PL/pgSQL cursors, which are similar but not the same. In particular, there is no FETCH FORWARD count in PL/pgSQL:

The direction clause can be any of the variants allowed in the SQL FETCH command except the ones that can fetch more than one row; namely, it can be NEXT, PRIOR, FIRST, LAST, ABSOLUTE count, RELATIVE count, FORWARD, or BACKWARD.

In PL/pgSQL you can only fetch one row at a time and process (or return) it. There is also a disclaimer in the manual:

Note: This page describes usage of cursors at the SQL command level. If you are trying to use cursors inside a PL/pgSQL function, the rules are different.

You could open that cursor in PL/pgSQL and loop to return rows. But that's only relevant if you want to fetch multiple distinct pieces from a big cursor to save overhead. Else a plain FOR loop (with automatic cursor) or a simple SELECT with OFFSET and LIMIT are certainly faster. Cursors are primarily meant to be returned to and used by the client:

CREATE OR REPLACE FUNCTION getbatch_ref(_cursor refcursor, _workload_id text)
  RETURNS refcursor
  LANGUAGE plpgsql AS
$func$
BEGIN
   OPEN $1 SCROLL FOR
   SELECT id
   FROM   workload
   WHERE  workload_id = $2
   ORDER  BY id; 

   RETURN $1;
END
$func$;

You can use this function in SQL:

BEGIN;
SELECT getbatch_ref('c', 'foo');
MOVE  FORWARD 10 IN c; 
FETCH FORWARD 10 FROM c;

ROLLBACK; -- or COMMIT;

You could also just use plain SQL:

BEGIN;
DECLARE c SCROLL CURSOR FOR
   SELECT id
   FROM   workload
   WHERE  workload_id = 'foo'
   ORDER  BY id; 

-- OPEN c; -- only relevant in plpgsql
-- The PostgreSQL server does not implement an OPEN statement for cursors;
-- a cursor is considered to be open when it is declared. 
MOVE  FORWARD 10 IN c; 
FETCH FORWARD 10 FROM c;

ROLLBACK; -- or COMMIT;

Collectives™ on Stack Overflow

PostgreSQL dynamic fetch foward count value

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related