Loop through tables in postgresql trigger

Question

I'm trying to build a trigger on a table, depending on other tables. So after search I have something like this in the trigger :

begin

table_name=select (...) from information schema ;

execute format('some stuff 

    for i in select ... loop

    insert into table (...) select (...) from %I

    end loop',table)

But when firing the trigger I get this error:

SQL Error [42601]: ERROR: syntax error on or near « FOR »

I can't understand why-any ideas ?

score 1 · Accepted Answer · answered Mar 21 '19 at 16:39

1

With EXECUTE (dynamic SQL) you can only execute SQL statements. You are trying to execute a PL/pgSQL block.

You have three options:

The query string only contains the INSERT statement, and the loop is regular SQL.
The query string is a DO SQL statement that contains the whole block.
Rather than writing a FOR loop, write a dynamic statement like
```
INSERT INTO ...
SELECT ... FROM %I
```

answered Mar 21 '19 at 16:39

Laurenz Albe

167,868
16
137
184

Thanks a lot. I pass the execute format into the loop and it worked : Changed from `execute format('for ... loop insert into ... select from %I end loop',table_name)` to `for ... loop execute format('insert into ... select from %I',table_name) end loop`. – nantodevison Mar 22 '19 at 08:07

Loop through tables in postgresql trigger

1 Answers1