Postgres nesting functions

Question

I have a multitude of functions which insert different data into several tables. In some cases, for all functions, a staff ID column will need to be populated. Since all of the data inserting functions return the ID of the inserted row, I could do set_staff_id(staff_id, insert_data1(...)); Instead of adding an optional staff_id argument to all data inserting functions, and coppying the set staff_id code too.

I'm trying to avoid code duplication, but wonder if this is the correct aproach. Thanks.

Gordon Linoff · Accepted Answer · 2016-10-16 21:43:23Z

1

Postgres allows you to use insert inside a CTE. So, this formulation might help:

with s as (
      insert into staff(. . .)
          values (. . .)
          returning *
     ),
     t1 as (
      insert into t1(staffid, . . . )
          select s.staffid, . . . 
          from s
     )
insert into t2(staffid, . . . )
    select s.staffid, . . . 
    from s;

answered Oct 16, 2016 at 21:43

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Comment

Dylan Over a year ago

Thanks for the answer, I haven't used CTEs at all before.

Collectives™ on Stack Overflow

Postgres nesting functions

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related