Hello,
I encountered the following error when compiling a pgsql function after an upgrade from PostgresSQL 9.3 on RHEL7 to PostgreSQL 12.9 on RHEL8, and could use some help from the community to figure out why.
ERROR: cursor variable must be a simple variable
LINE XX: OPEN vQuery.cursorReturn FOR
^
SQL state: 42804
After looking through the CURSOR documentation for PG9.3 and PG12, I noticed that the following statement is identical for both versions of PostgreSQL:
The cursor variable is opened and given the specified query to execute. The cursor cannot be open already, and it must have been declared as an unbound cursor variable (that is, as a simple refcursor variable). The query must be a SELECT, or something else that returns rows (such as EXPLAIN). The query is treated in the same way as other SQL commands in PL/pgSQL: PL/pgSQL variable names are substituted, and the query plan is cached for possible reuse. When a PL/pgSQL variable is substituted into the cursor query, the value that is substituted is the one it has at the time of the OPEN; subsequent changes to the variable will not affect the cursor's behavior. The SCROLL and NO SCROLL options have the same meanings as for a bound cursor.
Based on the statement highlighted above, I understand the error message. However, what I cannot figure out is why we did not get this error during compilation on PostgreSQL 9.3 with RHEL. Why does it work on PG9.3, but not on PG12? I feel like I am missing something.
Below is a sample pgsql function and composite type that are causing the error.
Custom composite type:
CREATE TYPE customCompositeType AS
(
cursorReturn refcursor,
code integer,
message text
);
Function:
CREATE OR REPLACE FUNCTION getData()
RETURNS record AS
$BODY$
DECLARE
vQuery customCompositeType;
BEGIN
vQuery.cursorReturn := 'cursorName';
vQuery.code := 0;
vQuery.message := ‘TEST;
-- Open Cursor
OPEN vQuery.cursorReturn FOR
SELECT column1,
column2,
column3
FROM sampleTable
ORDER BY column3 ASC;
RETURN(vQuery);
END;
$BODY$
LANGUAGE plpgsql
COST 100;
I would appreciate any advice or feedback that the community has on this issue.
Thanks,
William
