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

Question

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.

Myrica

Answer 1

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.)

I think this could be clearer. I'll suggest a fix for the documentation.

Stuck

NOTE to reader: At least on my system, PERFORM will not work in isolation and I must surround it with all the other elements .... DO $proc$ DECLARE StartTime timestamptz; EndTime timestamptz; Delta double precision; BEGIN StartTime := clock_timestamp(); PERFORM * FROM (with test_as_cte as (select * from myTable) select * from test_as_cte t) sub; EndTime := clock_timestamp(); Delta := 1000 * ( extract(epoch from EndTime) - extract(epoch from StartTime) ); RAISE NOTICE 'Duration in millisecs=%', Delta; END; $proc$; – Myrica

Yeah, guess it makes sense to remind casual readers that we are talking about a PL/pgSQL code block here. – Stuck