Skip site navigation (1) Skip section navigation (2)

Re: proof concept: do statement parametrization

From: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
To: Florian Pflug <fgp(at)phlo(dot)org>
Cc: PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: proof concept: do statement parametrization
Date: 2010-07-04 09:59:36
Message-ID: AANLkTin6hJe0U4M4lkq1HkcgJcJ0GO2R_tLhp-DtYk4W@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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
>
>

In response to

Responses

pgsql-hackers by date

Next:From: Florian PflugDate: 2010-07-04 11:36:25
Subject: Re: proof concept: do statement parametrization
Previous:From: Florian PflugDate: 2010-07-04 07:59:40
Subject: Re: proof concept: do statement parametrization

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group