2010/7/4 Florian Pflug <fgp@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
positionalparameters 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
>
>