Thread: View updating and nextval() workaround - will this ever break?

Basically, I'm wondering if anyone can see a problem with my standard 
workaround to the macro-expansion-vs-nextval problem with view. I can't 
see how PG changes might break it, but I might be using it in a 
presentation to others so thought I'd best check.


BEGIN;

CREATE TABLE foo (f_id serial, f1 int4, PRIMARY KEY (f_id));
CREATE TABLE bar (f_id int4 NOT NULL REFERENCES foo, b1 int4, PRIMARY 
KEY (f_id,b1));


-- This one suffers from the macro-expanding-nextval problem
--
CREATE VIEW foobar_bad AS SELECT f_id, f1, b1 FROM foo NATURAL JOIN bar;
CREATE RULE foobar_bad_ins AS ON INSERT TO foobar_bad
DO INSTEAD (INSERT INTO foo (f_id, f1) VALUES (nextval('foo_f_id_seq'), NEW.f1);INSERT INTO bar (f_id, b1) VALUES
(currval('foo_f_id_seq'),NEW.b1);
 
);


-- This one doesn't
--
CREATE VIEW foobar_good AS SELECT f_id, f1, b1 FROM foo NATURAL JOIN bar;

CREATE FUNCTION foobar_ins_fn(p_f1 int4, p_b1 int4) RETURNS void AS $$
BEGIN  INSERT INTO foo (f_id, f1) VALUES (nextval('foo_f_id_seq'), p_f1);  INSERT INTO bar (f_id, b1) VALUES
(currval('foo_f_id_seq'),p_b1);    
 
END;
$$ LANGUAGE plpgsql;

CREATE RULE foobar_good_ins AS ON INSERT TO foobar_good
DO INSTEAD SELECT foobar_ins_fn(NEW.f1, NEW.b1);


-- Bad version...
INSERT INTO foobar_bad (f1,b1)  SELECT generate_series, generate_series+10 FROM generate_series(1,3);
SELECT * FROM foobar_bad;
DELETE FROM bar;
DELETE FROM foo;

-- Good version...
INSERT INTO foobar_good (f1,b1)  SELECT generate_series, generate_series+10 FROM generate_series(1,3);
SELECT * FROM foobar_bad;
DELETE FROM bar;
DELETE FROM foo;

COMMIT;

Richard Huxton <dev@archonet.com> writes:
> Basically, I'm wondering if anyone can see a problem with my standard 
> workaround to the macro-expansion-vs-nextval problem with view.

> CREATE FUNCTION foobar_ins_fn(p_f1 int4, p_b1 int4) RETURNS void AS $$
> BEGIN
>    INSERT INTO foo (f_id, f1) VALUES (nextval('foo_f_id_seq'), p_f1);
>    INSERT INTO bar (f_id, b1) VALUES (currval('foo_f_id_seq'), p_b1);    
> END;
> $$ LANGUAGE plpgsql;

> CREATE RULE foobar_good_ins AS ON INSERT TO foobar_good
> DO INSTEAD SELECT foobar_ins_fn(NEW.f1, NEW.b1);

The main problem with this is that instead of an "INSERT n" command
completion response, you'll get back a useless SELECT result and then
"INSERT 0" (because the original INSERT was suppressed by the INSTEAD
rule).  If your application can deal with that, it's OK, but some don't
like it ...
        regards, tom lane

Tom Lane wrote:
> Richard Huxton <dev@archonet.com> writes:
>> Basically, I'm wondering if anyone can see a problem with my standard 
>> workaround to the macro-expansion-vs-nextval problem with view.
> 
>> CREATE FUNCTION foobar_ins_fn(p_f1 int4, p_b1 int4) RETURNS void AS $$
>> BEGIN
>>    INSERT INTO foo (f_id, f1) VALUES (nextval('foo_f_id_seq'), p_f1);
>>    INSERT INTO bar (f_id, b1) VALUES (currval('foo_f_id_seq'), p_b1);    
>> END;
>> $$ LANGUAGE plpgsql;
> 
>> CREATE RULE foobar_good_ins AS ON INSERT TO foobar_good
>> DO INSTEAD SELECT foobar_ins_fn(NEW.f1, NEW.b1);
> 
> The main problem with this is that instead of an "INSERT n" command
> completion response, you'll get back a useless SELECT result and then
> "INSERT 0" (because the original INSERT was suppressed by the INSTEAD
> rule).  If your application can deal with that, it's OK, but some don't
> like it ...

I can live with that, so long as there's no 
extremely-clever-look-inside-plpgsql feature anyone is planning.

--   Richard Huxton  Archonet Ltd