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 ?

Laurenz Albe · Accepted Answer · 2019-03-21 16:39:22Z

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, 2019 at 16:39

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

nantodevison Over a year ago

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.

Collectives™ on Stack Overflow

Loop through tables in postgresql trigger

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related