CODEWITHSUNDEEP
postgresqlplpgsqlpostgresql-9.3
Taher Galal
Taher Galal

Reputation: 327

execute of dynamic sql does not set found in psql

I execute an sql using dynamic sql in a trigger as the trigger will run across multiple tables. The sql will select from a table and check if there are results do nothing if no results insert into a table. it works wit the first psql select but the found variable is true even though it is empty

 create function test() returns trigger $Body$
 execute 'select * from ' || quote_ident(TG_TABLE_NAME) || '_table1 where condition';

 if not found then
 insert into x values(anything);
 end if;

execute 'select * from ' || quote_indent(TG_TABLE_NAME) || '_table2 where condition';

if not found then
insert into y values (values);
end if;
......

the second condition i am sure it produces no result but found is still true any solution to have this working?

Upvotes: 4

Views: 1359

Answers (1)

klin
klin

Reputation: 121534

You can use get diagnostics instead:

...
declare
    rcount int;
begin
    execute 'select * from some_table';
    get diagnostics rcount = row_count;
    if rcount = 0 then
        ...

Per the documentation:

Note in particular that EXECUTE changes the output of GET DIAGNOSTICS, but does not change FOUND.

Upvotes: 5

Related Questions