On 22 May 2017 at 03:14, Riaan Stander <rstander@xxxxxxxxx> wrote:
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.
It sounds like you don't know about anonymous code blocks with DO
https://www.postgresql.org/docs/devel/static/sql-do.html
Yes I do know about that feature. My first implemented generated an
anonymous code block, but to my utter dismay once I tried actually doing
parameter binding from the application it did not work. This seems to be
a Postgres limitation actually stated in the documentation. The
anonymous code block is treated as a function body with no parameters.
Thanks for the suggestion though.
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
