Re: Anonymous code block with parameters
От | Pavel Stehule |
---|---|
Тема | Re: Anonymous code block with parameters |
Дата | |
Msg-id | CAFj8pRBDTEGv2yhLuSLS8xU8Wo9kVmD0tkeKgCOWpQFwQ9ntGQ@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: Anonymous code block with parameters (Heikki Linnakangas <hlinnakangas@vmware.com>) |
Ответы |
Re: Anonymous code block with parameters
(Heikki Linnakangas <hlinnakangas@vmware.com>)
Re: Anonymous code block with parameters (Hannu Krosing <hannu@2ndQuadrant.com>) Re: Anonymous code block with parameters (Craig Ringer <craig@2ndquadrant.com>) |
Список | pgsql-hackers |
2014-09-16 9:10 GMT+02:00 Heikki Linnakangas <hlinnakangas@vmware.com>:
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.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.
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
В списке pgsql-hackers по дате отправления: