Macros for epsql

Hello I finished scripting in psql. Now, we are able to write some simply macros for epsql. This feature allows some better customisation. The syntax is trivial:

\newcommand string
... commands ...
\endnewcommand

We can use a parameters inside command definition. It is usual psql variables named 1 .. 10.

\newcommand desc
\ifdef 1
\d :1
\else
\echo 'missing table name'
\endifdef
\endnewcommand

Usage is very simple. New command is written to .psqlrc file. After registration, we can use new command:

postgres=# desc tab
       Table "public.tab"
┌────────┬─────────┬───────────┐
│ Column │  Type   │ Modifiers │
├────────┼─────────┼───────────┤
│ a      │ integer │           │
│ b      │ integer │           │
│ c      │ integer │           │
└────────┴─────────┴───────────┘
Indexes:
    "ff" btree (a)

postgres=# desc
missing table name
postgres=# 

You can download this patch from pgFoundry.

Samples

I wrote some macros:

Top Ten

\newcommand \tt
\ifdef 1
select relname, relpages, reltuples from pg_class order by relpages desc limit :1;
\else
select relname, relpages, reltuples from pg_class order by relpages desc;
\endifdef
\endnewcommand

Usage:

postgres=# \tt 3
┌──────────────┬──────────┬───────────┐
│   relname    │ relpages │ reltuples │
├──────────────┼──────────┼───────────┤
│ pg_proc      │       54 │      2232 │
│ pg_depend    │       41 │      5557 │
│ pg_attribute │       36 │      1960 │
└──────────────┴──────────┴───────────┘
(3 rows)

Regtype

\newcommand \rt
select :{1}::regtype as :[1];
\endnewcommand

Usage:

postgres=# \rt 23
┌─────────┐
│   23    │
├─────────┤
│ integer │
└─────────┘
(1 row)

Show tables

\newcommand show tables
\ifdef 1
\dt :1
\else
\dt
\endifdef
\endnewcommand

Usage:

postgres=# show tables
        List of relations
┌────────┬──────┬───────┬───────┐
│ Schema │ Name │ Type  │ Owner │
├────────┼──────┼───────┼───────┤
│ public │ tab  │ table │ pavel │
└────────┴──────┴───────┴───────┘
(1 row)

show create table

\newcommand show create table
\echo `/usr/local/pgsql/bin/pg_dump --schema-only -t :1 :DBNAME| grep -v -P '(^SET)|(^[-])|(^ALTER)|(^$)'`
\endnewcommand

Usage:

postgres=# show create table tab
CREATE TABLE tab (
    a integer,
    b integer,
    c integer
);
CREATE INDEX ff ON tab USING btree (a);

New release of epsql

I released a new version of epsql - for 8.5. More on http://www.postgres.cz/index.php/Enhanced-psql.

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;
               ^

How not to write a plpgsql functions

I found very strange PL/pgSQL code. PL/pgSQL has some specifics, and is good, if programmer has good knowledge about it. The basic rule - don't repeat string or array concat too often inside loop in function. So bad code:

CREATE OR REPLACE FUNCTION generate_uuid_v4() RETURNS uuid AS $$
DECLARE
    value VARCHAR(36);
BEGIN
    value =          lpad(to_hex(ceil(random() * 255)::int), 2, '0');
    value = value || lpad(to_hex(ceil(random() * 255)::int), 2, '0');
    value = value || lpad(to_hex(ceil(random() * 255)::int), 2, '0');
    value = value || lpad(to_hex(ceil(random() * 255)::int), 2, '0');
    value = value || '-';
    value = value || lpad(to_hex(ceil(random() * 255)::int), 2, '0');
    value = value || lpad(to_hex(ceil(random() * 255)::int), 2, '0');
    value = value || '-';
    value = value || lpad((to_hex((ceil(random() * 255)::int & 15) | 64)), 2, '0');
    value = value || lpad(to_hex(ceil(random() * 255)::int), 2, '0');
    value = value || '-';
    value = value || lpad((to_hex((ceil(random() * 255)::int & 63) | 128)), 2, '0');
    value = value || lpad(to_hex(ceil(random() * 255)::int), 2, '0');
    value = value || '-';
    value = value || lpad(to_hex(ceil(random() * 255)::int), 2, '0');
    value = value || lpad(to_hex(ceil(random() * 255)::int), 2, '0');
    value = value || lpad(to_hex(ceil(random() * 255)::int), 2, '0');
    value = value || lpad(to_hex(ceil(random() * 255)::int), 2, '0');
    value = value || lpad(to_hex(ceil(random() * 255)::int), 2, '0');
    value = value || lpad(to_hex(ceil(random() * 255)::int), 2, '0');
    RETURN value::uuid;
END;
$$ LANGUAGE 'plpgsql';

postgres=# select generate_uuid_v4() from generate_series(1,10000);
           generate_uuid_v4           
--------------------------------------
 9833de6f-4ba6-49f1-8d5f-d98d10d79346
 340c6008-b8fe-4267-bcc5-96bcddb5f675
...
 6f54c524-bd02-4efc-84b3-b56d1c93f3ec
 96ee245e-2990-4842-9d36-45a66dab8adb
Time: 407,082 ms

This is good sample of wrong using PL/pgSQL language. PLPerl, maybe PLPython will do much better work. When we cannot use these languages, we have to use some tricks. With some modifications original code - we can have 2x faster code.

CREATE OR REPLACE FUNCTION _generate_uuid_v4() RETURNS uuid AS $$
BEGIN
    RETURN  (lpad(to_hex(ceil(random() * 255)::int), 2, '0')
   || lpad(to_hex(ceil(random() * 255)::int), 2, '0')
   || lpad(to_hex(ceil(random() * 255)::int), 2, '0')
   || lpad(to_hex(ceil(random() * 255)::int), 2, '0')
   || '-'
   || lpad(to_hex(ceil(random() * 255)::int), 2, '0')
   || lpad(to_hex(ceil(random() * 255)::int), 2, '0')
   || '-'
   || lpad((to_hex((ceil(random() * 255)::int & 15) | 64)), 2, '0')
   || lpad(to_hex(ceil(random() * 255)::int), 2, '0')
   || '-'
   || lpad((to_hex((ceil(random() * 255)::int & 63) | 128)), 2, '0')
   || lpad(to_hex(ceil(random() * 255)::int), 2, '0')
   || '-'
   || lpad(to_hex(ceil(random() * 255)::int), 2, '0')
   || lpad(to_hex(ceil(random() * 255)::int), 2, '0')
   || lpad(to_hex(ceil(random() * 255)::int), 2, '0')
   || lpad(to_hex(ceil(random() * 255)::int), 2, '0')
   || lpad(to_hex(ceil(random() * 255)::int), 2, '0')
   || lpad(to_hex(ceil(random() * 255)::int), 2, '0'))::uuid;
END;
$$ LANGUAGE 'plpgsql'

postgres=# select _generate_uuid_v4() from generate_series(1,10000);
          _generate_uuid_v4           
--------------------------------------
 5134fe69-5d2a-4419-bf84-d72dc290f72f
 919e8663-b384-4eae-89d9-1026d6be4127
 f23f8f4f-6914-47e8-983f-155ace0d8860
...
 39951d8f-fbd1-48bf-9c5d-157eefbd5af6
 73318784-2cfa-4bcc-ad42-307fc4c173fc
Time: 203,516 ms

Original needs 400ms for ten thousand values. Edited code needs only 200ms for same number of values. This code should be inlined - when we use SQL language:

CREATE OR REPLACE FUNCTION _generate_uuid_v4() RETURNS uuid AS $$
SELECT (lpad(to_hex(ceil(random() * 255)::int), 2, '0')
   || lpad(to_hex(ceil(random() * 255)::int), 2, '0')
   || lpad(to_hex(ceil(random() * 255)::int), 2, '0')
   || lpad(to_hex(ceil(random() * 255)::int), 2, '0')
   || '-'
   || lpad(to_hex(ceil(random() * 255)::int), 2, '0')
   || lpad(to_hex(ceil(random() * 255)::int), 2, '0')
   || '-'
   || lpad((to_hex((ceil(random() * 255)::int & 15) | 64)), 2, '0')
   || lpad(to_hex(ceil(random() * 255)::int), 2, '0')
   || '-'
   || lpad((to_hex((ceil(random() * 255)::int & 63) | 128)), 2, '0')
   || lpad(to_hex(ceil(random() * 255)::int), 2, '0')
   || '-'
   || lpad(to_hex(ceil(random() * 255)::int), 2, '0')
   || lpad(to_hex(ceil(random() * 255)::int), 2, '0')
   || lpad(to_hex(ceil(random() * 255)::int), 2, '0')
   || lpad(to_hex(ceil(random() * 255)::int), 2, '0')
   || lpad(to_hex(ceil(random() * 255)::int), 2, '0')
   || lpad(to_hex(ceil(random() * 255)::int), 2, '0'))::uuid;
$$ LANGUAGE SQL;

postgres=# select _generate_uuid_v4() from generate_series(1,10000);
          _generate_uuid_v4           
--------------------------------------
 e725eb84-67ad-485e-bec2-d73485db4e69
 49f605d1-226e-4d9e-8457-30a925c98a0c
 ed759055-23e7-4322-a98a-552f65a297ae
...
 414e62c3-1b84-4bc9-8fd7-ba8bd0a72403
 03e27594-42c0-4ec9-a493-636b6d130aad
Time: 148,184 ms

With SQL function we safe other 50ms. PLpgSQL is good language - but it isn't C language or Fortran. It needs some different manners.