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

Richard Huxton <dev(at)archonet(dot)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