Riaan Stander <rstander@exa.co.za> 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