12

I tried to simulate my problem in the code example below. In the code below, I am doing a select * from test in a procedure. As we know, we must use the perform keyword for this. This works great:

perform * from test;

However, if I try to rewrite that simple query as a CTE, I cannot get it working. I am getting a syntax error.

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

Is this possible? What is the correct syntax? I tried several alternatives and going through documentation, but without any success so far.

(Please note that this is just an example to explain my problem. I know the queries do not really make any sense.)

create table test
(
    key int primary key  
);

create function test() returns trigger as
$$
begin
    raise notice 'hello there';
    -- this does work
    perform * from test;
    -- this doesn't work
    with test_as_cte as(select * from test) perform * from test_as_cte;
    return new;
end;
$$
language plpgsql;

create trigger test after insert on test for each row execute procedure test();

insert into test(key) select 1;
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Caroline Kwerts
  • 225
  • 2
  • 10
  • Consider the follow-up question: https://stackoverflow.com/q/71040855/939860 – Erwin Brandstetter Feb 08 '22 at 22:15
  • @ErwinBrandstetter >>Sometimes it is useful to evaluate an expression or SELECT query but discard the result, for example when calling a function that has side-effects but no useful result value. So the whole perform clause does not make sense. in this case. so it's same like `begin raise notice 'hello there'; return new; end` – jian Feb 12 '22 at 05:45

1 Answers1

12

try:

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

I would never think you might need CTE and ignore the result, but if you need, thinking of perform as of "select no return" logic leads to above semantics. or perform * from (CTE) or alike

Vao Tsun
  • 47,234
  • 13
  • 100
  • 132