2

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?

user779159
  • 9,034
  • 14
  • 59
  • 89
  • I suspect you mean `insert into firsttable (col1) values (val1a), (val2a); insert into secondtable (col2) values (val1b), (val2b)` ... – Dario Dec 26 '16 at 23:19

1 Answers1

3

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:

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • 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? – user779159 Dec 27 '16 at 11:04
  • 1
    @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. – Erwin Brandstetter Dec 27 '16 at 15:48