2014-09-16 9:10 GMT+02:00 Heikki Linnakangas <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? Pavel > > > > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers >