Thread

1. proposal: using PQexecParams in psql (using variables as real params)

   Pavel Stehule <pavel.stehule@gmail.com> — 2009-11-15T22:01:55Z

   Hello
   
   I propose to add possibility to use psql variables as real query
   parameters. The goal of this proposal is simplification of creating
   psql based commands. Current using of psql variables based on
   substitution has large area of using, but has some risks. a) there are
   possible sql injection, b) we have to have to do some special (not too
   much readable quoting) - see Bruce's book, psql chapter.
   
   I checked, so this doesn't need much work. Attachment contains a prototype.
   
   [pavel@nemesis ~]$ echo "select upper(:message)" | psql -r -v
   message="Pavel's cat" postgres
       upper
   ─────────────
    PAVEL'S CAT
   (1 row)
   
   [pavel@nemesis ~]$ psql -v message="Pavel's cat" postgres
   psql (8.5devel)
   Type "help" for help.
   
   postgres=# \pexec
   Separately passing parameters is on.
   postgres=# select upper(:message);
       upper
   ─────────────
    PAVEL'S CAT
   (1 row)
   
   This small feature simplify integration psql to shell environment.
   
   comments, notes??
   
   Regards
   Pavel Stehule
   

2. Re: proposal: using PQexecParams in psql (using variables as real params)

   Takahiro Itagaki <itagaki.takahiro@oss.ntt.co.jp> — 2009-11-16T05:46:09Z

   Pavel Stehule <pavel.stehule@gmail.com> wrote:
   
   > I propose to add possibility to use psql variables as real query
   > parameters. The goal of this proposal is simplification of creating
   > psql based commands.
   
   > postgres=# \pexec
   > Separately passing parameters is on.
   > postgres=# select upper(:message);
   
   There might be good if we can use both old and new behaviors at once
   instead of \pexec switch. Something like:
   
       SELECT * FROM :tablename WHERE id = $value
   
   BTW, this proposal is a partial solution for scripting in psql.
   But we will want other goodies soon - assignment, if, loop...
   If there is a total solution, it would be better than a partial one.
   I've heard pgAdmin uses pgScript as a client-side language.
   Should we also take the language (or client-side plpgsql) in psql?
   
   Regards,
   ---
   ITAGAKI Takahiro
   NTT Open Source Software Center
   
   
   
   

3. Re: proposal: using PQexecParams in psql (using variables as real params)

   Pavel Stehule <pavel.stehule@gmail.com> — 2009-11-16T08:14:29Z

   2009/11/16 Itagaki Takahiro <itagaki.takahiro@oss.ntt.co.jp>:
   >
   > Pavel Stehule <pavel.stehule@gmail.com> wrote:
   >
   >> I propose to add possibility to use psql variables as real query
   >> parameters. The goal of this proposal is simplification of creating
   >> psql based commands.
   >
   >> postgres=# \pexec
   >> Separately passing parameters is on.
   >> postgres=# select upper(:message);
   >
   > There might be good if we can use both old and new behaviors at once
   > instead of \pexec switch. Something like:
   >
   >    SELECT * FROM :tablename WHERE id = $value
   >
   > BTW, this proposal is a partial solution for scripting in psql.
   > But we will want other goodies soon - assignment, if, loop...
   > If there is a total solution, it would be better than a partial one.
   > I've heard pgAdmin uses pgScript as a client-side language.
   > Should we also take the language (or client-side plpgsql) in psql?
   
   I don't would to reimplement pgScript. Personally I prefer some
   scripting possibilities in psql too, but when pgScript exists, then we
   could to use it. What more - we have a DO statement. So there are
   tools. DO is great, now I missing some like USING clause:
   
   DO (tablename varchar, value varchar)
   $$
     EXECUTE 'SELECT * FROM '|| tablename || 'WHERE id = $1' USING value;
   $$ USING :tablename, :value;
   
   I don't would to create new syntax for identifiers - see parallel
   thread. Proposed feature is maximally simple and transparent.
   >
   > Regards,
   > ---
   > ITAGAKI Takahiro
   > NTT Open Source Software Center
   >
   >
   >