Re: proof concept: do statement parametrization

2010/7/4 Florian Pflug <fgp(at)phlo(dot)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
>
>