Tuesday, March 10, 2009

Experimental psql

Hello

I spent some on psql's face lifting. Some new features are really experimental. I don't expect early adopting it for core. It's more game for now. epsql will be available with release 8.4. What epsql can do?

UTF8 boxes

  • one new border style, five new border lines styles

Stored function's listing

  • should help with debuging

    postgres=# select test(10);
    ERROR: division by zero
    CONTEXT: PL/pgSQL function "test" line 3 at assignment

    postgres=# \lf test
    *** CREATE OR REPLACE FUNCTION public.test(a integer)
    *** RETURNS integer
    *** LANGUAGE plpgsql
    *** AS $function$
    1 declare b int;
    2 begin
    3 b := a/0;
    4 return a;
    5 end;
    *** $function$

Cursor's support

  • allows using data from database for statement parameters
    • \fetch
      postgres=# BEGIN;
      BEGIN
      postgres=# DECLARE c CURSOR FOR SELECT * FROM pg_database;
      DECLARE CURSOR
      postgres=# \fetch c \echo :datname :datcollate
      template1 cs_CZ.UTF-8
      postgres=# \fetch c \echo :datname :datcollate
      template0 cs_CZ.UTF-8
      postgres=# \fetch c \echo :datname :datcollate
      postgres cs_CZ.UTF-8
      postgres=# \fetch c \echo :datname :datcollate
      ERROR: (not available)
      postgres=# COMMIT;
      COMMIT
    • \fetchall
      postgres=# CREATE TABLE test1(a int); CREATE TABLE test2(a int);
      CREATE TABLE
      CREATE TABLE
      postgres=# BEGIN;
      BEGIN
      postgres=# DECLARE c CURSOR FOR SELECT tablename FROM pg_tables WHERE tablename LIKE 'test%';
      DECLARE CURSOR
      postgres=# \fetchall c DROP TABLE :tablename;
      DROP TABLE
      DROP TABLE
      postgres=# COMMIT;
      COMMIT
      postgres=#
I invite any ideas, comments.

Pavel

Labels: