Tuesday, March 10, 2009

Experimental psql


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;
      postgres=# DECLARE c CURSOR FOR SELECT * FROM pg_database;
      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;
    • \fetchall
      postgres=# CREATE TABLE test1(a int); CREATE TABLE test2(a int);
      postgres=# BEGIN;
      postgres=# DECLARE c CURSOR FOR SELECT tablename FROM pg_tables WHERE tablename LIKE 'test%';
      postgres=# \fetchall c DROP TABLE :tablename;
      postgres=# COMMIT;
I invite any ideas, comments.




At March 10, 2009 at 4:15 AM , Blogger David said...

I often find myself running a "select * from table" and because the rows are too long it will start to wrap around. It would be nice if I could simply push the right arrow key to see columns further to the right. Or maybe the window (I am using Windows) should automatically get a bigger horizontal character buffer.

At March 10, 2009 at 4:19 AM , Blogger David said...

Another idea is the possibility to copy the results of the last query to clipboard using ctrl+0 to copy the whole table. ctrl+1 for only the first column. Ctrl+2 for the second etc. Then one could paste the results into Excel or something similar to get a good overview.

At March 10, 2009 at 8:08 AM , Blogger Pavel Stěhule said...

To David: It's possible. But it isn't task for psql. psql use specified "pager". So we can find or write some inteligent pager (based on GUI or Ncurses, that allows horizontal an move and clipboard support).

At March 10, 2009 at 1:32 PM , Blogger Markus Bertheau said...

I have PAGER set to "less -RSFX" and in .psqlrc I have "\pset pager always", that gives me horizontal scrolling. I guess you can find a less.exe that works on the windows prompt.

At March 10, 2009 at 3:16 PM , Blogger Richard Broersma said...

I just tried:

Setting the PAGER variable = "less -RSFX" didn't work to well in windows but "less -SFX" had a very nice effect.

At November 27, 2009 at 9:46 PM , Anonymous Marcos Ortiz said...

Pavel Why these new features is not included on any 8.5 alpha to test it?

This is a very useful and gracefull info to me. What do you think?

Eng. Marcos Ortiz Valmaseda [mlortiz@uci.cu]
PostgreSQL SystemDBA
Centro de Tecnologias de Almacenamiento y Analisis de datos (CENTALAD)[centalad@uci.cu]

At November 27, 2009 at 10:56 PM , Blogger Pavel Stěhule said...

[to Marcos] Some people in pg community has different opinion than you. I am don't have a force to change it. Some times I have a different opinion than community.

8.5 will have a UTF8 decoration - that's I like, but this decoration isn't parametrizable and maybe is too rich. So I prefer external project - it is advantage of open source.


Post a Comment

Subscribe to Post Comments [Atom]

<< Home