Обсуждение: how to call stored procedures that are 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?
It seems strange to use a select for something that writes rather than
reads.

Is there something other than functions I should use for this purpose?

I realize in some cases I could install the function as a trigger on
inserts or updates, but that also seems obscure.

E.g. I have a function f(a, b, c), where a, b, and c are single pieces
of information (e.g., a name, a date, a place) and the function f is
supposed to update one or more tables, perhaps after some processing.

Thanks.
Ross Boylan

Ross Boylan <ross@biostat.ucsf.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.

> 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

On Thu, 2012-05-31 at 01:23 -0400, Tom Lane wrote:
> Ross Boylan <ross@biostat.ucsf.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

Ross Boylan <ross@biostat.ucsf.edu> writes:
> 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)?

An exception block is a fairly expensive thing, because it's basically a
subtransaction.  My gut feel is that it's better to have a fast path
that avoids using one; but you'd really be best advised to measure both
ways for your particular situation, if you have a case where it's worth
your trouble to worry about which is faster.

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

Right, because in one case you're failing only a subtransaction.
If you only look for the error on the client side, it's too late
as far as the server is concerned.

            regards, tom lane