How to add items to local function array in postgress?

Question

I need to do multiple inserts in the table that number depends on how many records we got from select. I need to iterate over records from select and then insert them to another table. I want to get all new Ids from insert to array to use them later in the following inserts, how can I do this?

I can't collect them using select after insert, because there can be old records.

for record in (select test, test1, test2
        from public.a  
        join public.b on a.reg_id  = b.id
        where a.id =  arg_id) loop
                                        
        (INSERT into public.c  
        ( a, b, c)
        (select test, test1, test2
        from record)--need to get ids from this
end loop;

---
some block where I have old_ids
---

--to insert them there
insert into public.d(d,e,f,g)values(..,..,old_id,(id from previous insert))

upd Tried to make like this:

with a2 as(
    INSERT INTO public.reg
    (name_, code, state)
    (select a.secondname, a.code, b.state_name--multiple rows from select
    from public.client a 
    left join public.states b on a.state_id  = b.id
    where a.id  =  id_p) RETURNING id 
    )


INSERT INTO public.request
(phone, address, qty, prod_id, reg_id)
(select phone, address, qty, prod_id, (select id from a2)--maybe something wrong there, but error happend before
    from public.shp a
where a.id = id_p);

but getting an error: more than one row returned by a subquery used as an expression

Are you looking for https://stackoverflow.com/q/29256888/1048572? — Bergi, Aug 10 '21 at 11:20
Why are you even doing a loop instead of a simple `INSERT into public.c(a, b, c) SELECT test, test1, test2 FROM public.a JOIN public.b ON a.reg_id = b.id WHERE a.id = arg_id;`? — Bergi, Aug 10 '21 at 11:21
Because I can have more than one record, and I need to insert all records from select, and the get all created ids — FeoJun, Aug 10 '21 at 11:24
That's exactly what the `INSERT` statement does: it inserts all records from the query. — Bergi, Aug 10 '21 at 11:24
Sure you can: just use `RETURNING id`. You currently weren't returning any ids anyway? — Bergi, Aug 10 '21 at 11:26
I'm getting an error, that there are more than one row if I'm trying to return it into declared variable, I tried to make variable as array, but it doesn't help — FeoJun, Aug 10 '21 at 11:28
Please stop thinking about loops and arrays. SQL is about sets & tables. — wildplasser, Aug 10 '21 at 11:43
`RETURNING id );` <<-- remove the `;` The CTE should be part of the final query. [and remove the excessive `()`, too ] And: get rid of these scalar subqueries. Join the CTE just like you would join a table. — wildplasser, Aug 10 '21 at 12:40
How can I join cte if only have id from it, that not related to other tables? — FeoJun, Aug 10 '21 at 12:46
"*if I'm trying to return it into declared variable*" - ah, yes, but your code snippet doesn't do that, and you shouldn't be doing this. Especially if you planned to use the variable outside of the loop. — Bergi, Aug 10 '21 at 13:26
The main idea is that I need to use it outside of the loop because I need a loop to fill it. And I can't understand how can I get set of ids or array of ids to use them in the following `insert` — FeoJun, Aug 10 '21 at 13:39

score 0 · Answer 1 · answered Aug 10 '21 at 11:41

Demonstration of using the result of a query:

\i tmp.sql

CREATE TABLE aa(aa integer not null primary key);
CREATE TABLE bb(bb integer not null primary key);
CREATE TABLE cc(cc integer not null primary key);

WITH x0 AS (
        INSERT INTO aa(aa) values (1),(2),(3)
        returning aa
        )
, x1 AS (
        INSERT INTO bb(bb)
        SELECT aa*aa from x0
        returning bb
        )
, x2 AS (
        INSERT INTO cc(cc)
        SELECT bb*bb from x1
        returning cc
        )

        -- main query
SELECT *
FROM x2
        ;


        -- Check cc
SELECT *
FROM cc
        ;

Output:

DROP SCHEMA
CREATE SCHEMA
SET
CREATE TABLE
CREATE TABLE
CREATE TABLE
 cc 
----
  1
 16
 81
(3 rows)

 cc 
----
  1
 16
 81
(3 rows)

`more than one row returned by a subquery used as an expression`. Tried to make like you. But getting an error from 1st cte — FeoJun, Aug 10 '21 at 12:06
Please fix your question. Add the table definitions, replace the `...` by actual code. — wildplasser, Aug 10 '21 at 12:09

How to add items to local function array in postgress?

1 Answers1