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 -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
