On 09/16/2014 09:44 AM, Pavel Stehule wrote: > > > 2014-09-16 9:24 GMT+02:00 Heikki Linnakangas <hlinnakan...@vmware.com > <mailto:hlinnakan...@vmware.com>>: > > On 09/16/2014 10: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); > > > Why we don't introduce a temporary functions instead? > > > You can already do that: > > create function pg_temp.tempfunc(i int4) returns int4 as $$ begin > end; $$ language plpgsql; > > > it looks much more like workaround than supported feature. a straightforward CREATE TEMPORARY FUNCTION implementation would do exactly that. > > > > Compared to DO, you have to do extra steps to create the function, > and drop it when you're done. And you can't use them in a hot > standby, because it changes the catalogs. (although a better > solution to that would be to make it work, as well as temporary > tables, but that's a much bigger project). > > Maybe we don't need any of this, you can just use temporary > function. But clearly someone though that DO statements are useful > in general, because we've had temporary functions for ages and we > nevertheless added the DO statement. > > > I afraid so we create little bit obscure syntaxes, without real effect > and real cost I would agree with you if we had session-level "temporary" functions
But then we would still miss anonymous/in-line/on-the-spot functions > > Any new useful syntax should be clean, simple, natural and shorter > than create function ... This is not how SQL works, nor ADA nor pl/pgsql ;) > > and without risks a conflicts with ANSI SQL > > I prefer a typed session variables, where is not risk of SQL injection > or some performance lost. The benefit of typed server side variables > can be for wide group of users. Agreed but this would be a much bigger project, as Heikki already mentioned re. temp things on replicas. especially if typed session variables could hold temporary functions . DECLARE FUNCTION mytempfucntion () ... Cheers -- Hannu Krosing PostgreSQL Consultant Performance, Scalability and High Availability 2ndQuadrant Nordic OÜ
