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:
Have you considered integrating it with pgscript, the one that's included in pgadmin?
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.
Is the source available somewhere?
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)
Post a Comment
Subscribe to Post Comments [Atom]
<< Home