Re: Any risks in using FUNCTIONs (stored procedures) instead of raw sql queries?

On Wed, Nov 19, 2008 at 06:40:04PM +0900, Craig Ringer wrote:
> What you really
> want is "Ensure that the form info is in the database and up to date",
> ie an UPSERT / REPLACE. There's a fairly convenient way to do that:
>
> -- If the form is already there, update it.
> -- If it's not there, this is a no-op.
> UPDATE table SET val1 = blah, val2 = blah, etc
> WHERE form_identifier = whatever;
>
> -- Otherwise, insert it. If it's already there, this
> -- only costs us an index lookup.
> INSERT INTO table (form_identifier, val1, val2, etc)
> SELECT whatever, blah, blah2
> WHERE NOT EXISTS (SELECT 1 FROM table WHERE form_identifer = whatever)
>
> You can of course conveniently bundle this into a PL/PgSQL stored
> procedure. If you like you can also use GET DIAGNOSTICS to see whether
> the UPDATE did anything and skip the INSERT if it did (allowing you to
> structure the INSERT the usual way instead of INSERT ... SELECT ... WHERE).

There's a magic pl/pgsql variable called "FOUND" that helps here:

UPDATE tbl SET x = 1 WHERE id = 10;
IF NOT FOUND THEN
INSERT INTO tbl (id,x) VALUES (10,1);
END IF;

would be the unparameterized version.

Sam