Pages

Tuesday, December 15, 2009

enhanced psql console - second version

Hello I am working on new version of epsql. It has almost all old features and some new:

Scripting support

I add support for "if" and "forc" statements. It is a complement to new "DO" statement. DO is nice, but doesn't allow parametrisation and doesn't return a value. Iteration over cursor hasn't these limits:
\timing off
\set VERBOSITY terse
BEGIN;
DECLARE t CURSOR FOR SELECT generate_series(1,5) g;
\forc t
  \if :g % 2 = 0
    DECLARE t2 CURSOR FOR SELECT generate_series(1,4) z;
    \forc t2
      SELECT :g AS g, :z AS z;
    \endforc t2
    CLOSE t2;
  \endif
\endforc t
CLOSE t;
COMMIT;

\timing off
\set VERBOSITY terse
BEGIN;
DECLARE t CURSOR FOR SELECT generate_series(1,3) g;
\forc t
  DECLARE t2 CURSOR FOR SELECT generate_series(1,4) z;
  \forc t2
    DECLARE t3 CURSOR FOR SELECT generate_series(1,3) j;
    \forc t3
      DECLARE t4 CURSOR FOR SELECT generate_series(1,5) k;
      \forc t4
        SELECT :g AS g, :z AS z, :j AS j, :k AS k;
      \endforc t4
      CLOSE t4;
    \endforc t3
    CLOSE t3;
  \endforc t2
  CLOSE t2;
\endforc t
CLOSE t;
COMMIT;

\if position('PostgreSQL 8.5' in version()) <> 0
  \echo 'PostgreSQL verze 8.5'
\else
  \echo 'Other version of PostgreSQL'
\endif

BEGIN;
DECLARE t CURSOR 
   FOR SELECT table_schema || '.' || table_name AS tn 
          FROM information_schema.tables 
         WHERE table_schema = 'pg_catalog';
\forc t
  SELECT * FROM :tn LIMIT 10;
\endforc
COMMIT;

quote_ident and quote_literal integration

Using psql variables could be dificult, when we would to use it correctly. psql doesn't have a syntax for automatic quoting. epsql will use :[var] for quoting as identifier and :{var} for quoting as literal:
postgres=# \set x 'my tab'
postgres=# select * from :[x];
ERROR:  relation "my tab" does not exist
LINE 1: select * from "my tab";

postgres=# select :{x};
 ?column? 
----------
 my tab
(1 row)

-- versus
postgres=# select :x;
ERROR:  column "my" does not exist
LINE 1: select my tab;
               ^

4 comments:

  1. Have you considered integrating it with pgscript, the one that's included in pgadmin?

    ReplyDelete
  2. No. pg_script is too different. pg_script was designed for "compatibility" with T-SQL. Mix T-SQL and psql will be only total chaos. This is only "procedural" enhancing to psql. There are no new syntax for meta statements, no new syntax for local variables. I hope so it is more natural to psql.

    ReplyDelete
  3. Tomorrow I would to merge old epsql code with new - so I hope - at Friday I'll publish code. Attention - it is for 8.5 now - port for 8.4 is possible (when somebody would it)

    ReplyDelete