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