[GENERAL] Update two tables returning id from insert CTE Query

[Patrick B]

Hi guys,

I've got 2k rows in a table:

> CREATE TABLE
>     public.not_monthly
>     (
>     id BIGINT DEFAULT "nextval"('"id_seq"'::"regclass") NOT NULL,
>     clientid BIGINT,
>     name_first CHARACTER VARYING(80) DEFAULT ''::CHARACTER VARYING,
>     name_last CHARACTER VARYING(80) DEFAULT ''::CHARACTER VARYING
> );


I want to insert data from public.not_monthly to
public.table_1(clientid,name_first) and public.table_2(client_id,c_id*(FROM
table_1)*,name_last)

table_2.c_id must have the ID from the insert on the table_1 table.

I did this:

> WITH rows AS (
> SELECT
>                t1.id,
>                     t1.clientid,
>                     t1.name_first,
>                     t1.name_last
>                     row_number() OVER (ORDER BY t1.id) AS rn
>             FROM
>                     public.not_monthly t1
>                     ),
> ins_table_1 AS (
> INSERT INTO public.table_1 (clientid,name_first)
>         SELECT
>                 clientid,
>                 name_first
>         FROM rows
> RETURNING id
> ),
> ins_table_2 AS (
> INSERT INTO public.table_2 (name_last,clientid)
>         SELECT
>                 name_last,
>                 clientid
>         FROM rows
> RETURNING id
> )


Then, I was able to select the table_1.id using:

> SELECT i.id AS table_1_id, s.id AS not_monthly_id
> FROM (select id, row_number() OVER (ORDER BY id) AS rn FROM ins_table_1) i
> JOIN rows s USING (rn)


So I'd imagine now I would do the update? How can I update table_2.c_id
with the ins_table_1.id value?
I'm using Postgres 9.2

Thanks
Patrick