INSERT and RETURNING multiple values SQL

Question

I have two tables where relationship one to many, and I want INSERT to mytable some values and RETURNING multiple values INTO variables to use them later. Here example:

 IF NOT EXISTS
        (SELECT id FROM mytable WHERE label_id = xxx)
    THEN
        INSERT INTO mytable
        (label_id, "name")
        VALUES
        (xxx,'foo'),
        (xxx,'bar')
    RETURNING 
        (SELECT id INTO mytable_id1 FROM mytable WHERE name = 'foo'),
        (SELECT id INTO mytable_id2 FROM mytable WHERE name = 'bar');
    ELSE 
        (SELECT id INTO mytable_id1 FROM mytable WHERE name = 'foo'),
        (SELECT id INTO mytable_id2 FROM mytable WHERE name = 'bar');
 END IF;

But I get always error:

ERROR: INTO specified more than once at or near "into"

I trying google problem and for example INSERT INTO ... RETURNING multiple columns (PostgreSQL) and Error: INTO specified more than once at or near "INTO"

unfortunately solutions it doesn't help for me (or I do something wrong). Any ideas?

TeoK · Accepted Answer · 2022-09-13 05:05:44Z

0

I went the other way and a separate IF for each variable. Not very smart but its work

 IF NOT EXISTS
        (SELECT id FROM mytable WHERE label_id = xxx and "name"='foo')
    THEN
        INSERT INTO mytable
        (label_id, "name")
        VALUES
        (xxx,'foo'),
    RETURNING 
        (SELECT id INTO mytable_id1 FROM mytable WHERE name = 'foo');
    ELSE 
        (SELECT id INTO mytable_id1 FROM mytable WHERE name = 'foo');
 END IF;
 IF NOT EXISTS
        (SELECT id FROM mytable WHERE label_id = xxx and "name"='bar')
    THEN
        INSERT INTO mytable
        (label_id, "name")
        VALUES
        (xxx,'bar'),
    RETURNING 
        (SELECT id INTO mytable_id1 FROM mytable WHERE name = 'bar');
    ELSE 
        (SELECT id INTO mytable_id1 FROM mytable WHERE name = 'bar');
 END IF;

answered Sep 13, 2022 at 5:05

TeoK

5212 gold badges6 silver badges15 bronze badges

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

Collectives™ on Stack Overflow

INSERT and RETURNING multiple values SQL

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related