CODEWITHSUNDEEP
sqlpostgresqlplpgsqlpostgresql-9.2database-cursor
Jake Daly
Jake Daly

Reputation: 41

PostgreSQL dynamic fetch foward count value

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.

Upvotes: 4

Views: 3907

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658472

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;

Upvotes: 4

Related Questions