How to "PERFORM" a CTE query returning multiple rows/columns?

February 8, 2022

As a follow-up to this question:

How to "PERFORM" CTE queries in PL/pgSQL?

I try:

perform (with test_as_cte as(select * from myTable) select * from test_as_cte);

But get the following error:

SQL Error [42601]: ERROR: subquery must return only one column
Where: PL/pgSQL function inline_code_block line 9 at PERFORM

If I replace * with myCol in the above code there is no error.

However, I need to do realistic performance testing with the CTE and return multiple columns.

>Solution :

The WITH query enclosed in parentheses is treated like a sub-select. It works fine the way you have it as long as it returns a single value (one column of one row). Else you must treat it as subquery and call it like this (inside a PL/pgSQL code block!):

PERFORM * FROM (with test_as_cte as (select * from b2) select * from test_as_cte t) sub;

Or just:

PERFORM FROM (<any SELECT query>) sub;

The manual:

PERFORM query;

This executes query and discards the result. Write the query
the same way you would write an SQL SELECT command, but replace the
initial keyword SELECT with PERFORM. For WITH queries, use
PERFORM and then place the query in parentheses. (In this case, the
query can only return one row.)