On 09/16/2014 09:15 AM, Pavel Stehule wrote: > > > 2014-09-16 9:10 GMT+02:00 Heikki Linnakangas <hlinnakan...@vmware.com > <mailto:hlinnakan...@vmware.com>>: > > On 09/16/2014 09:38 AM, Kalyanov Dmitry wrote: > > I'd like to propose support for IN and OUT parameters in 'DO' > blocks. > > Currently, anonymous code blocks (DO statements) can not > receive or > return parameters. > > I suggest: > > 1) Add a new clause to DO statement for specifying names, types, > directions and values of parameters: > > DO <code> [LANGUAGE <lang>] [USING (<arguments>)] > > where <arguments> has the same syntax as in > 'CREATE FUNCTION <name> (<arguments>)'. > > Example: > > do $$ begin z := x || y; end; $$ > language plpgsql > using > ( > x text = '1', > in out y int4 = 123, > out z text > ); > > 2) Values for IN and IN OUT parameters are specified using > syntax for > default values of function arguments. > > 3) If DO statement has at least one of OUT or IN OUT > parameters then it > returns one tuple containing values of OUT and IN OUT parameters. > > Do you think that this feature would be useful? I have a > proof-of-concept patch in progress that I intend to publish soon. > > > There are two features here. One is to allow arguments to be > passed to DO statements. The other is to allow a DO statement to > return a result. Let's discuss them separately. > > 1) Passing arguments to a DO block can be useful feature, because > it allows you to pass parameters to the DO block without injecting > them into the string, which helps to avoid SQL injection attacks. > > I don't like the syntax you propose though. It doesn't actually > let you pass the parameters out-of-band, so I don't really see the > point. I think this needs to work with PREPARE/EXECUTE, and the > protocol-level prepare/execute mechanism. Ie. something like this: > > PREPARE mydoblock (text, int4) AS DO $$ ... $$ > EXECUTE mydoblock ('foo', 123); > > 2) Returning values from a DO block would also be handy. But I > don't see why it should be restricted to OUT parameters. I'd > suggest allowing a RETURNS clause, like in CREATE FUNCTION: > > DO $$ ... $$ LANGUAGE plpgsql RETURNS int4; > > or > > DO $$ ... $$ LANGUAGE plpgsql RETURNS TABLE (col1 text, col2 int4); > > - Heikki > > > Why we don't introduce a temporary functions instead?
As I see it, the DO blocks _are_ temporary (or rather in-line) functions, though quite restricted in not taking arguments and not returning anything. DO you have a better syntax for "temporary / in-line functions" ? What I would like to to is to make DO blocks equal to any other data source, so you could do WITH mydoblock(col1, col2)(DO $$ ... $$ LANGUAGE plpgsql RETURNS TABLE (col1 text, col2 int4)) SELECT * FROM mydoblock; or SELECT * FROM (DO $$ ... $$ LANGUAGE plpgsql RETURNS TABLE (col1 text, col2 int4)) mydoblock; and for the parameter-taking version SELECT (DO $$ ... $$ LANGUAGE plpgsql USING (user) RETURNS int4)(username) AS usernum FROM users; Cheers -- Hannu Krosing PostgreSQL Consultant Performance, Scalability and High Availability 2ndQuadrant Nordic OÜ
