Riaan Stander <rstander@xxxxxxxxx> writes:
The intended use is use-once. The reason is that the statements might
differ per call, especially when we start doing updates. The ideal would
be to just issue the sql statements, but I was trying to cut down on
network calls. To batch them together and get output from one query as
input for the others (declare variables), I have to wrap them in a
function in Postgres. Or am I missing something? In SQL Server TSQL I
could declare variables in any statement as required.
Hm, well, feeding data forward to the next query without a network
round trip is a valid concern.
How stylized are these commands? Have you considered pushing the
generation logic into the function, so that you just have one (or
a few) persistent functions, and the variability slack is taken
up through EXECUTE'd strings? That'd likely be significantly
more efficient than one-use functions. Even disregarding the
pg_proc update traffic, plpgsql isn't going to shine in that usage
because it's optimized for repeated execution of functions.
regards, tom lane
The commands are generated from a complex object/type in the
application. Some of them can be quite large. With modifications they do
state tracking too, so that we only update fields that actually changed
and can do optimistic concurrency checking.
It'll probably make more sense to try create a function per type of
object that deals with the query generation. That way I can create a
Postgres type that maps from the application object.
Thanks for the advice. I'll give that a shot.
Regards
Riaan Stander
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
