I find store functions fairly useful; eg for any table 'foo', the function
store_foo(v1,v2,...,vn) returns int;
will perform an insert (if a unique key is not present) or an update (if
the unique key is present)
v1,...,vn may be values for columns in the table foo, or perhaps also for
tables foo_a, foo_b, c_foo related to foo by foreign keys (in a highly
normalised db, it can be convenient to combine these multiple
update/inserts into a single function call).
These functions can be implemented in an application/middleware layer (eg
perl,java,..) or as pgsql functions. Both approaches have their strengths
and weaknesses. For my purposes, sometimes pgsql is preferable. For one
thing, pgsql store functions will be faster since it involves less
client-server I/O.
However, coding these can be fairly tedious. There is the additional
problem that it's generally desirable to provide multiple signatures for
different permutations of v1,...,vn.
This can be unwieldy when a store function has lots of possible values. In
these cases, the application layer approach has a big advantage, as the
arguments to a function call generally don't have to be position
dependent. As far as I am aware, there is no equivalent way to do this in
plpgsql.
Is this a common use case, or do people typically do this in the
application/middleware layer?
If other people are doing this, are there any common design patterns they
would like to share? What about code that helps auto-generate some of
these functions? Or even super-clever middleware that can decide whether
to do it in the application layer or autogenerate some helper
functions...?
Cheers
Chris
