My foo
s each have a bar
.
At least they're supposed to.
I now want to create and assign bar
s to all foo
s 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
);
bar
s are attached to foo
s 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?
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$;