Prev: [HACKERS] proof concept: do statement parametrization
Next: [HACKERS] Always truncate segments before unlink
From: Florian Pflug on 4 Jul 2010 03:59
On Jul4, 2010, at 08:41 , Pavel Stehule wrote:
> I enhanced DO statement syntax to allowing a parameters. Syntax is
> relative simple:
>
> do ([varname] vartype := value, ...) $$ ... $$


I think it'd be more useful to put the values at the very end of the statement, not somewhere in the middle. For positional parameters I envision

do (vartype, ...) $$ ... $$ using value, ...

and for named parameters it'd be

do (varname vartype) $$ ... $$ using varname := value, ...

I won't make a difference for your use-case, but it'd make it easier to call the same DO block with different parameters, like in the following shell snippet.

COMMANDS="DO (arg int) $$ ... $$"
(for a in arg1, arg2, arg3, arg4; do
echo "$COMMANDS USING $a;"
done) | psql

best regards,
Florian Pflug


--
Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

From: Florian Pflug on 4 Jul 2010 07:36
On Jul4, 2010, at 11:59 , Pavel Stehule wrote:
> 2010/7/4 Florian Pflug <fgp(a)phlo.org>:
>> On Jul4, 2010, at 08:41 , Pavel Stehule wrote:
>>> I enhanced DO statement syntax to allowing a parameters. Syntax is
>>> relative simple:
>>>
>>> do ([varname] vartype := value, ...) $$ ... $$
>>
>> I think it'd be more useful to put the values at the very end of the statement, not somewhere in the middle. For positional parameters I envision
>>
>> do (vartype, ...) $$ ... $$ using value, ...
>>
>> and for named parameters it'd be
>>
>> do (varname vartype) $$ ... $$ using varname := value, ...

> Your syntax is longer and less readable (my personal view). With
> proposed syntax it is ensured so every parameter has a value. Next -
> my syntax is reflecting fact, so these are not true parameters - it's
> +/- similar to default values of function parameters.

Yeah, with your syntax omitting a value is syntactically invalid, while with mine it'd parse OK and fail later on. But I fail to see the drawback of that. I do agree that my suggestion is slightly more verbose, but it think thats compensated by the increase in usefulness.

> I understand to your motivation - but you can use a printf command and
> do it same work.

Sure. But by the very same argument, printf makes DO-block parameters redundant as a whole.

> or better and safer - use a psql variables (it is preferred solution)

I don't really buy that argument. By using a psql variable, you simply move the quoting & escaping business from SQL to the shell where psql is called. True, you avoid SQL injectiont, but in turn you make yourself vulnerable to shell injection.

best regards,
Florian Pflug


--
Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

From: Pavel Stehule on 4 Jul 2010 07:57
2010/7/4 Florian Pflug <fgp(a)phlo.org>:
> On Jul4, 2010, at 11:59 , Pavel Stehule wrote:
>> 2010/7/4 Florian Pflug <fgp(a)phlo.org>:
>>> On Jul4, 2010, at 08:41 , Pavel Stehule wrote:
>>>> I enhanced DO statement syntax to allowing a parameters. Syntax is
>>>> relative simple:
>>>>
>>>> do ([varname] vartype := value, ...) $$ ... $$
>>>
>>> I think it'd be more useful to put the values at the very end of the statement, not somewhere in the middle. For positional parameters I envision
>>>
>>> do (vartype, ...) $$ ... $$ using value, ...
>>>
>>> and for named parameters it'd be
>>>
>>> do (varname vartype) $$ ... $$ using varname := value, ...
>
>> Your syntax  is longer and less readable (my personal view). With
>> proposed syntax it is ensured so every parameter has a value. Next -
>> my syntax is reflecting fact, so these are not true parameters - it's
>> +/- similar to default values of function parameters.
>
> Yeah, with your syntax omitting a value is syntactically invalid, while with mine it'd parse OK and fail later on. But I fail to see the drawback of that. I do agree that my suggestion is slightly more verbose, but it think thats compensated by the increase in usefulness.
>
>> I understand to your motivation - but you can use a printf command and
>> do it same work.
>
> Sure. But by the very same argument, printf makes DO-block parameters redundant as a whole.
>

printf isn't nice, agree - it is just workaround for some special case
- when you don't store code in variable, then you have not any
problems.

>> or better and safer - use a psql variables (it is preferred solution)
>
> I don't really buy that argument. By using a psql variable, you simply move the quoting & escaping business from SQL to the shell where psql is called. True, you avoid SQL injectiont, but in turn you make yourself vulnerable to shell injection.

can you show some example of shell injection? For me, this way via
psql variables is the best. There are clean interface between outer
and inner space. And I can call simply just psql scripts - without
external bash.

best regards
Pavel

p.s. theoretically do statement can support both syntax, maybe mix of
all. It's only about 20 lines more in parser. But code will be little
bit more complex and I am not sure if it is necessary. I dislike the
space between variable definition and values - and you have to put
param list on the statement end.

>
> best regards,
> Florian Pflug
>
>

--
Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

From: Pavel Stehule on 4 Jul 2010 05:59
2010/7/4 Florian Pflug <fgp(a)phlo.org>:
> On Jul4, 2010, at 08:41 , Pavel Stehule wrote:
>> I enhanced DO statement syntax to allowing a parameters. Syntax is
>> relative simple:
>>
>> do ([varname] vartype := value, ...) $$ ... $$
>
>
> I think it'd be more useful to put the values at the very end of the statement, not somewhere in the middle. For positional parameters I envision
>
> do (vartype, ...) $$ ... $$ using value, ...
>
> and for named parameters it'd be
>
> do (varname vartype) $$ ... $$ using varname := value, ...
>
> I won't make a difference for your use-case, but it'd make it easier to call the same DO block with different parameters, like in the following shell  snippet.
>
> COMMANDS="DO (arg int) $$ ... $$"
> (for a in arg1, arg2, arg3, arg4; do
>  echo "$COMMANDS USING $a;"
> done) | psql
>
Your syntax is longer and less readable (my personal view). With
proposed syntax it is ensured so every parameter has a value. Next -
my syntax is reflecting fact, so these are not true parameters - it's
+/- similar to default values of function parameters. You cannot to
write do (a int := $1) $$ ... $$ - because utils statements hasn't
have variables.

I understand to your motivation - but you can use a printf command and
do it same work

CMD='do(a int := %s) $$ begin raise notice ''%%'',a; end; $$'
for a in $1 $2 $3 $4
do
if [ -n "$a" ]
then
echo `printf "$CMD" $a` | psql postgres
fi
done;

or better and safer - use a psql variables (it is preferred solution)

################################
for a in $1 $2 $3 $4
do
if [ -n "$a" ]
then
psql postgres --quiet --variable a=$a <<EOT

do (a int := :a) \$\$
begin
raise notice '%', a;
end; \$\$

EOT

fi
done
###############################

psql variables can be escaped more secure - so it is prefered

for a in `cat /etc/passwd | cut -d: -f1`
do
psql postgres --quiet --variable usrname=$a <<EOT
do (usrname varchar := :'usrname') \$\$
begin
raise notice '%', usrname;
end; \$\$
EOT
done

Regards

Pavel Stehule


> best regards,
> Florian Pflug
>
>

--
Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

From: Tom Lane on 4 Jul 2010 09:58
Pavel Stehule <pavel.stehule(a)gmail.com> writes:
> my syntax is reflecting fact, so these are not true parameters - it's
> +/- similar to default values of function parameters.

FWIW, that doesn't seem like a positive to me.

> You cannot to
> write do (a int := $1) $$ ... $$ - because utils statements hasn't
> have variables.

Yet. I don't particularly want to relax that either, but the syntax of
this feature shouldn't assume it'll be true forever.

I think it's better to not confuse these things with default parameters,
so Florian's idea looks better to me.

BTW, we intentionally didn't put any provision for parameters into DO
originally. What's changed to alter that decision?

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

 |  Next  |  Last
Pages: 1 2 3
Prev: [HACKERS] proof concept: do statement parametrization
Next: [HACKERS] Always truncate segments before unlink