Re: how to call stored procedures that are writes

On Thu, 2012-05-31 at 01:23 -0400, Tom Lane wrote:
> Ross Boylan <ross(at)biostat(dot)ucsf(dot)edu> writes:
> > Suppose I have a function (stored procedure) whose purpose is to write
> > to the database. How would I call the function from a client? select?
>
> Yup. Functions are functions.
Thanks for confirming that. Originally I was concerned there was no way
to call such a function.
>
> > It seems strange to use a select for something that writes rather than
> > reads.
>
> Perhaps. We have resisted adding a separate "CALL" type of command,
> though, as long as it would only be syntactic sugar for calling the same
> kind of function object. There has been a lot of discussion about
> adding a different sort of stored procedure that would execute "outside"
> rather than "within" the transaction engine, so that it could start and
> commit multiple transactions; and if and when that happens, it would
> need a different type of statement to call it.
>
> regards, tom lane
I hadn't appreciated that little wrinkle til I saw it in the FAQ. It
does "raise" a related question.
Is trapping errors (BEGIN/EXCEPTION in plpgsql) relatively cheap or
expensive? That is, is it better to
1) SELECT to see if an object exists; INSERT if not. or
2) INSERT the object, trapping errors if it already exists (assuming
constraints on the table prevent duplicates)?

I assume this depends partly on the ratio of new to old objects in the
request, but are there other reasons to favor one approach over the
other? (Context: a function with semantics "give me the id of this
object, creating it if necessary").

I gather that trapped exceptions do not abort the transaction in
plpgsql; I think they do if I trap them in python using psycopg2.

Ross