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;
^
Have you considered integrating it with pgscript, the one that's included in pgadmin?
ReplyDeleteNo. 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.
ReplyDeleteIs the source available somewhere?
ReplyDeleteTomorrow 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