Обсуждение: 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