Postgres cursor targets

Question

I would appreciate advice on best practices to create a pg9.6 structure to hold multiple rows from multiple joined tables as a cursor target. The docs were not clear on this, other searches seemed to indicate using temp table, such as below.

DECLARE
    mycursor refcursor;
BEGIN
CREATE TEMPORARY TABLE tmp_persons (
    personid INTEGER,
    primaryconnect INTEGER
);
OPEN mycursor FOR SELECT p.personid,c.primaryconnect FROM tpersons p JOIN tconnections c ON .....
LOOP
    FETCH mycursor INTO tmp_persons;

    .. do something using tmp_persons.personid, tmp_persons.primaryconnect

Answer 1

I hope so documentation is clean.

FETCH retrieves the next row from the cursor into a target, which might be a row variable, a record variable, or a comma-separated list of simple variables, just like SELECT INTO. If there is no next row, the target is set to NULL(s). As with SELECT INTO, the special variable FOUND can be checked to see whether a row was obtained or not.

So you cannot to use temporary table as target. Use record variable if there are not any special requirement:

DECLARE 
  r record;
  mycursor refcursor;
BEGIN
  OPEN mycursor ..
  LOOP
    FETCH mycursor INTO r;
    EXIST WHEN NOT FOUND;
    RAISE NOTICE 'personid: %', r.personid;

For this simple task is better to use FOR IN SELECT statement.

DECLARE r record;
BEGIN
  FOR r IN SELECT p.personid, ...
  LOOP
    RAISE NOTICE 'personid: %', r.personid;