[SOLVED] Postgres CTE with refcursors

Postgres CTE with refcursors

How can I open multiple refcursors using expressions generated in a CTE?

I use refcursors to return multiple record sets from the same function, and CTEs for data set re-use throughout processing.

I would use temp tables, but I'm scarred after this experience.

I tried this, but it results in a syntax error:

WITH 
  expr1 (
    select ... from ...
  ),
  expr2 (
    select ... from expr1 inner join ...
  ),
  expr3 (
    OPEN refcursor1 FOR select ... from expr2
  )
  OPEN refcursor2 FOR select ... from expr2 inner join ...

Solution

You cannot do it that way, because you cannot embed a PL/pgSQL statement (OPEN) in an SQL statement.

You'll have to run the query twice if you want two refcursors.

Perhaps you should find out what was the problem with the temporary table — for example, check the memory context dump in the log.