Splitting a postgres SQL insert statement into 2 tables using triggers

Question

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?

I suspect you mean insert into firsttable (col1) values (val1a), (val2a); insert into secondtable (col2) values (val1b), (val2b) ... — Dario
– Dario, Commented Dec 26, 2016 at 23:19

Community · Accepted Answer · 2017-05-23 11:53:10Z

3

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

INSERT ...RETURNING .. comes up empty when BEFORE trigger cancels statement

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.

CommunityBot

11 silver badge

answered Dec 26, 2016 at 23:53

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

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

2 Comments

user779159 Over a year ago

I'm interested in the RULE method in the page you linked to, but you say rules can be tricky. I read the page you suggested to read there but I'm not sure about a few things. If the rule has a few different statements in an INSTEAD clause, are all those statements executed transactionally? So if one fails they all fail?

Erwin Brandstetter Over a year ago

@user779159: Yes. If anything inside the same transaction raises an exception, the whole transaction is rolled back. RULEs are always atomic for any number of statements.

Collectives™ on Stack Overflow

Splitting a postgres SQL insert statement into 2 tables using triggers

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related