PostgreSQL: Basic Cursor Handling

Table of Contents

I summarized about cursor operation in PostgreSQL PL/pgSQL.

Environment

psql 9.6
PostgreSQL 9.3

Cursor

Speaking simply, cursor is used when we operate to each record we got by SELECT query, one by one.

Use Cursor with Declaration

There are several ways to use cursor. First, I write about basic usage. In Oracle, this patter is called explicit cursor. PostgreSQL document says it, Bound Cursor.

Declare cursor with CURSOR statement, and declare variable of RECORD type. LOOP about all rows of SELECT result.

DO $$
DECLARE
C CURSOR FOR
SELECT *
FROM table_name
WHERE some_condition = TRUE;
CR RECORD;
BEGIN
OPEN C;
LOOP
FETCH C INTO CR;
IF NOT FOUND THEN
EXIT;
END IF;
RAISE NOTICE '%', CR.id;
END LOOP;
CLOSE C;
END$$;

DO $$

DECLARE

C CURSOR FOR

SELECT *

FROM table_name

WHERE some_condition = TRUE;

CR RECORD;

BEGIN

OPEN C;

LOOP

FETCH C INTO CR;

IF NOT FOUND THEN

EXIT;

END IF;

RAISE NOTICE '%', CR.id;

END LOOP;

CLOSE C;

END$$;

FOR after CURSOR statement can be replaced to IS. Public document only says about FOR, but IS is also OK.

There, カーソルを開き、クエリ結果の行を変数(ここではCR)に入れて全行の処理を行います。 When the cursor processed all rows, exit the loop with EXIT statement. Close opened cursor at last.

EXIT is in IF conditional branch, but you can make it simpler for executing EXIT. Insert EXIT WHEN NOT FOUND;.

DO $$
DECLARE
C CURSOR FOR
SELECT *
FROM table_name
WHERE some_condition = TRUE;
CR RECORD;
BEGIN
OPEN C;
LOOP
FETCH C INTO CR;
EXIT WHEN NOT FOUND;
RAISE NOTICE '%', CR.id;
END LOOP;
CLOSE C;
END$$;

DO $$

DECLARE

C CURSOR FOR

SELECT *

FROM table_name

WHERE some_condition = TRUE;

CR RECORD;

BEGIN

OPEN C;

LOOP

FETCH C INTO CR;

EXIT WHEN NOT FOUND;

RAISE NOTICE '%', CR.id;

END LOOP;

CLOSE C;

END$$;

This pattern, FETCH timing is not complicated, so it can be shortening.

DO $$
DECLARE
C CURSOR FOR
SELECT *
FROM table_name
WHERE condition = true;
BEGIN
FOR CR IN C LOOP
RAISE NOTICE '%', CR.id;
END LOOP;
END$$;

DO $$

DECLARE

C CURSOR FOR

SELECT *

FROM table_name

WHERE condition = true;

BEGIN

FOR CR IN C LOOP

RAISE NOTICE '%', CR.id;

END LOOP;

END$$;

In this case, CR can be used only in the loop.

Use Parameter for Cursor

Cursor can take parameter like function in programming languages.

DO $$
DECLARE
C CURSOR(
I_MIN_ID INTEGER, I_MAX_ID INTEGER) FOR
SELECT *
FROM table_name
WHERE id BETWEEN I_MIN_ID AND I_MAX_ID;
BEGIN
FOR CR IN C(10, 20) LOOP
RAISE NOTICE '%', CR.id;
END LOOP;
END$$;

DO $$

DECLARE

C CURSOR(

I_MIN_ID INTEGER, I_MAX_ID INTEGER) FOR

SELECT *

FROM table_name

WHERE id BETWEEN I_MIN_ID AND I_MAX_ID;

BEGIN

FOR CR IN C(10, 20) LOOP

RAISE NOTICE '%', CR.id;

END LOOP;

END$$;

Use Cursor without Declaration

This is more technical cursor usage. In Oracle, it is called implicit cursor.

DO $$
DECLARE
CR RECORD;
BEGIN
FOR CR IN (SELECT * FROM MEMBERS WHERE ID BETWEEN 1 AND 10) LOOP
RAISE NOTICE '%', CR.id;
END LOOP;
END$$;

DO $$

DECLARE

CR RECORD;

BEGIN

FOR CR IN (SELECT * FROM MEMBERS WHERE ID BETWEEN 1 AND 10) LOOP

RAISE NOTICE '%', CR.id;

END LOOP;

END$$;

There, I didn’t declare cursor variable. Record variable has to be declared.(In Oracle PL/SQL, record variable doesn’t have to be declared.)

The Life

PostgreSQL: Basic Cursor Handling

Environment

Cursor

Use Cursor with Declaration

Use Parameter for Cursor

Use Cursor without Declaration

Related Posts

Readers who viewed this page, also viewed:

A Life Summary of an Gypsy