sqlpostgresqlpostgresql-13

Create new related entity and enter its id into a link table


My foos each have a bar.

At least they're supposed to.

I now want to create and assign bars to all foos that don't.

bar is completely detached from foo, it only has an id serial that serves as its primary key

CREATE TABLE bar
(
    id SERIAL PRIMARY KEY
);

bars are attached to foos by use of a foo_bar link table.

CREATE TABLE foo_bar
(
    foo_id INTEGER NOT NULL
        CONSTRAINT foo_bar__foo__fk
            REFERENCES foo
            ON DELETE CASCADE,
    bar_id  INTEGER NOT NULL
        CONSTRAINT foo_bar__bar__fk
            REFERENCES bar
            ON DELETE RESTRICT,
    CONSTRAINT foo_bar__pk
        PRIMARY KEY (foo_id, bar_id)
);

my first attempt

WITH
foos_without_bar AS (
    SELECT f.id
    FROM foo f
    WHERE NOT EXISTS(
        SELECT 1
            FROM foo_bar fb
            WHERE fb.foo_id = f.id
    )
),
new_bars AS (
    INSERT INTO bar(id)
        SELECT DEFAULT
        FROM foos_without_bar f
        RETURNING f.id as foo_id, bar.id as bar_id
)
INSERT INTO foo_bar(foo_id, bar_id)
    SELECT n.foo_id, n.bar_id
    FROM new_bars n
;

fails with

ERROR:  DEFAULT is not allowed in this context
LINE 13:         SELECT DEFAULT
                        ^

and my second using nextval:

WITH
foos_without_bar AS (
    SELECT f.id
    FROM foo f
    WHERE NOT EXISTS(
        SELECT 1
            FROM foo_bar fb
            WHERE fb.foo_id = f.id
    )
),
new_bars AS (
    INSERT INTO bar(id)
        SELECT nextval(pg_get_serial_sequence('bar','id'))
        FROM foos_without_bar f
        RETURNING f.id as foo_id, bar.id as bar_id
)
INSERT INTO foo_bar(foo_id, bar_id)
    SELECT n.foo_id, n.bar_id
    FROM new_bars n
;

also fails, this time because

ERROR:  missing FROM-clause entry for table "f"
LINE 15:         RETURNING f.id as foo_id, bar.id as bar_...
                           ^

and I'm not quite sure how to rectify that.

How do I make this statement work?


Solution

  • I gave up and decided to just write a function:

    DO $createMissingBars$
    DECLARE
        new_bar_id INTEGER;
        foos_without_bar INTEGER[];
    BEGIN
        foos_without_bar := ARRAY (
            SELECT f.id
            FROM foo f
            WHERE NOT EXISTS(
                SELECT 1
                FROM foo_bar fb
                WHERE fb.foo_id = f.id
            )
        );
    
        FOR i in 1 .. array_upper(foos_without_bar,1) LOOP
            INSERT INTO foo_bar
                DEFAULT VALUES
                RETURNING bar.id INTO new_bar_id
            ;
            INSERT INTO foo_bar(foo_id, bar_id)
                VALUES (foos_without_bar[i], new_bar_id)
            ;
        END LOOP;
    END $createMissingBars$;