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
I hope so documentation is clean.
So you cannot to use temporary table as target. Use record variable if there are not any special requirement:
For this simple task is better to use
FOR IN SELECT
statement.