CODEWITHSUNDEEP
sqlpostgresqltriggerssql-insert
user779159
user779159

Reputation: 9602

Splitting a postgres SQL insert statement into 2 tables using triggers

Is it possible to use postgres triggers to split an INSERT statement into 2 tables? So if you do insert into testtable (col1, col2) values (val1a, val1b), (val2a, val2b), can it be translated with triggers to something like

insert into testtable (col1) values (val1a), (val1b)
insert into anothertable (col2) values (val2a), (val2b)

Basically is it possible for testtable to not have a col2 even though the original SQL INSERT looks like col2 should exist on testtable?

How can this be accomplished using triggers?

Upvotes: 2

Views: 1521

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656784

You can have a VIEW with either triggers or rules to redirect the INSERT.

Or you can do it in a single SQL statement with data-modifying CTEs.

WITH input(col1, col2) AS (
   VALUES
     (text 'val1a', text 'val1b')  --  explicit type cast in first row
   , ('val2a', 'val2b')
   )
, ins1 AS (
   INSERT INTO testtable (col1)
   SELECT col1 FROM input
   )
INSERT INTO anothertable (col2)
SELECT col2 FROM input;

Typically, one would also store the connections between 'val1a' and 'val1b' of your input row somehow.
You might want to use a RETURNING clause to get a serial PK from the first table and store that in the second table.

Related:

Upvotes: 3

Related Questions