Enhanced-psql

Z PostgreSQL
Verze z 4. 1. 2010, 10:25, kterou vytvořil imported>Pavel (→‎Statement execution with storing a result)
(rozdíl) ← Starší verze | zobrazit aktuální verzi (rozdíl) | Novější verze → (rozdíl)
Skočit na navigaci Skočit na vyhledávání

Enhanced-psql is standard PostgreSQL console with some new functionality for basic scripting support. It is distributed as patch for PostgreSQL.

Installation

If you would to test it, you need last PostgreSQL's source code from CVS or GIT.

Second - download the latest patch from http://pgfoundry.org/frs/?group_id=1000430.

Then apply patch:

[pavel@nemesis pgsql]$ patch -p0 < psql-enhanced.diff 
patching file ./src/bin/psql/command.c
patching file ./src/bin/psql/help.c
patching file ./src/bin/psql/mainloop.c
patching file ./src/bin/psql/Makefile
patching file ./src/bin/psql/print.c
patching file ./src/bin/psql/print.h
patching file ./src/bin/psql/psqlscan.l
patching file ./src/bin/psql/psqlscript.c
patching file ./src/bin/psql/psqlscript.h
patching file ./src/bin/psql/settings.h
patching file ./src/bin/psql/startup.c
patching file ./src/bin/psql/tab-complete.c

[pavel@nemesis psql]$ make clean
rm -f psql command.o common.o help.o input.o stringutils.o mainloop.o copy.o startup.o prompt.o variables.o 
large_obj.o print.o describe.o tab-complete.o mbprint.o dumputils.o keywords.o kwlookup.o sql_help.o 
psqlscript.o  dumputils.c keywords.c kwlookup.c

[pavel@nemesis psql]$ make all
make -C ../../../src/interfaces/libpq all
make[1]: Entering directory `/home/pavel/src/pgsql/src/interfaces/libpq'
make[1]: Nothing to be done for `all'.
make[1]: Leaving directory `/home/pavel/src/pgsql/src/interfaces/libpq'
make -C ../../../src/port all
 ....

[pavel@nemesis psql]$ su
Heslo: 
[root@nemesis psql]# make install
make -C ../../../src/interfaces/libpq all
make[1]: Entering directory `/home/pavel/src/pgsql/src/interfaces/libpq'
make[1]: Nothing to be done for `all'.
make[1]: Leaving directory `/home/pavel/src/pgsql/src/interfaces/libpq'
make -C ../../../src/port all
make[1]: Entering directory `/home/pavel/src/pgsql/src/port'
make[1]: Nothing to be done for `all'.
make[1]: Leaving directory `/home/pavel/src/pgsql/src/port'
/bin/mkdir -p '/usr/local/pgsql/bin'
/bin/sh ../../../config/install-sh -c  psql '/usr/local/pgsql/bin/psql'
/bin/sh ../../../config/install-sh -c -m 644 ./psqlrc.sample '/usr/local/pgsql/share/psqlrc.sample'

After installation you can use a new functionality.

Features

Richer table formatting

Border level 3

\pset border 3

Print horizontal lines between rows.

Custom line styles

psql 8.5 comming with three line's styles: ascii, old-ascii, unicode.

Enhanced psql adds other:

  • custom-single - modified original unicode style,
  • custom-double-border - like original unicode style, but border uses double lines,
  • custom-double-border-header - like previous, but line between header and data uses double lines,
  • custom-double-border-header-columns - only data are separated by single line, all other uses double lines,
  • custom-elegant - single line is used for data, border and header use double lines.
-- default PostgreSQL look
postgres=# SELECT * FROM tab;
    a     |   b    
----------+--------
 first    | second
 previous | last
(4 rows)

-- enhanced PostgreSQL look:
postgres=# \pset linestyle custom-double-border-header-columns
Line style is custom-double-border-header-columns.
postgres=# SELECT * FROM tab;
╔══════════╦════════╗
║    a     ║   b    ║
╠══════════╬════════╣
║ first    ║ second ║
╟──────────╫────────╢
║ previous ║ last   ║
╚══════════╩════════╝
(4 rows)

postgres=# \pset linestyle custom-elegant
Line style is custom-elegant.
postgres=# SELECT * FROM tab;
╔══════════╦════════╗
║    a     ║   b    ║
╠══════════╩════════╣
║ first    │ second ║
╟──────────┼────────╢
║ previous │ last   ║
╚═══════════════════╝
(4 rows)

Human text wrapping

With textwrapping on, psql insert breaks on end of words.

Default output:

                                 ?column?                                  
──────────────────────────────────────────────────────────────────────────
PostgreSQL 8.4 is the first version to properly support certificate name v…
alidation, and also the first version to support client certificate authen…
tication, both of which are vulnerable to this bug, neither of which is en…
abled by default. However, previous versions are also indirectly vulnerabl…
e, because they exposed the CN field of the certificate to the application…
 for further validation. So you could have a stored procedure checking the…
 client certificate, or just the libpq application checking the server cer…
tificate, even in earlier versions. And given the API structure, there was…
 no way for these outside processes to know if they were being fooled or n…
ot. So if you are using an application that makes use of this on previous …
versions of PostgreSQL, you still need the patch - there is no way to fix …
the bug from the application.                                             ↵
                                                                          ↵
The summary of this post is that this vulnerability is a lot less serious …
in PostgreSQL than in many other systems that had the issue. That doesn't …
mean it's not there, and that it should be (and have been) fixed. But it m…
eans that this vulnerability alone is likely not reason enough to rush an …
upgrade on your production systems - most likely you're not affected by it…
. On the PostgreSQL security page it is tagged with classification A, whic…
h is the highest. This is more an indication that the system we're using f…
or classification really doesn't take these things into consideration - 
(1 row)

With activated flush-left wrapping mode:

postgres=# \pset textwrapping flush-left
textwrapping mode is flush-left 
postgres=# select :{x};
                                 ?column?                                  
──────────────────────────────────────────────────────────────────────────
PostgreSQL 8.4 is the first version to properly support certificate name  …
validation, and also the first version to support client certificate      …
authentication, both of which are vulnerable to this bug, neither of which…
is enabled by default. However, previous versions are also indirectly     …
vulnerable, because they exposed the CN field of the certificate to the   …
application for further validation. So you could have a stored procedure  …
checking the client certificate, or just the libpq application checking   …
the server certificate, even in earlier versions. And given the API       …
structure, there was no way for these outside processes to know if they   …
were being fooled or not. So if you are using an application that makes   …
use of this on previous versions of PostgreSQL, you still need the patch -…
there is no way to fix the bug from the                                   …
application.                                                              ↵
                                                                          ↵
The summary of this post is that this vulnerability is a lot less serious …
in PostgreSQL than in many other systems that had the issue. That doesn't …
mean it's not there, and that it should be (and have been) fixed. But it  …
means that this vulnerability alone is likely not reason enough to rush an…
upgrade on your production systems - most likely you're not affected by   …
it. On the PostgreSQL security page it is tagged with classification A,   …
which is the highest. This is more an indication that the system we're    …
using for classification really doesn't take these things into            …
consideration -
(1 row)

Multiline headers

epsql interpret char "|" as new line separator for headers. Is interesting, so this functionality needs only ten rows of code.

postgres=# \pset linestyle custom-single
Line style is custom-single.
postgres=# \pset multiline-header on
Multiline header is enabled.
postgres=# SELECT 10 AS "Row|[number]", 'Pavel Stehule' AS "Name";
┌──────────┬───────────────┐
│   Row    │     Name      │
│ [number] │               │
├──────────┴───────────────┤
│       10 │ Pavel         │
│          │ Stehule       │
└──────────────────────────┘
(1 row)

Features for developing of stored procedures

Print row numbers for source code of procedure

With new command \lf you can get source code of stored procedures. It can help to identify "bad" rows.

postgres=# SELECT foo(10);
 foo 
-----
   1
(1 row)

Time: 38,324 ms
postgres=# SELECT foo(0);
ERROR:  division by zero
CONTEXT:  PL/pgSQL function "foo" line 2 at RETURN
postgres=# \lf foo
****	CREATE OR REPLACE FUNCTION public.foo(a integer)
****	 RETURNS integer
****	 LANGUAGE plpgsql
****	AS $function$
   1	BEGIN
   2	  RETURN (10.0 / a)::int;
   3	END;
****	$function$

Scripting features

This feature allows some simply scripting on psql level.

Get first ten rows from all tables in some schema (I use it for faster database identification):

BEGIN;
-- loops are based on cursors
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;

Statement execution with storing a result

\execute SQL into var1 [ var2 [...]]

Example:

postgres=# \execute select 10,20 into a b
postgres=# \echo :a :b
10 20

Variables quoting

psql applies custom variables without any adjustments. So we have to be carefully with using them:

postgres=# \set variable 'my text'
postgres=# select :variable;
ERROR:  syntax error at or near "text"
LINE 1: select my text;
                  ^

In enhanced psql you can use new syntax :{variable} for literal values and :[variable] for identifier values.

-- use value of variable as literal
postgres=# select :{variable};
 ?column? 
----------
 my text
(1 row)

-- use value of variable as identifier
postgres=# select * from :[variable];
ERROR:  relation "my text" does not exist
LINE 1: select * from "my text";
                      ^
-- it is error, but not syntax error - query is correct

Using this syntax is protection to SQL injection too.removed - little bit modified of this feature will be in core.

Conditional executing support

If

\if expr
....
\elseif expr
....
\else
....
\endif

Example:

\if position('PostgreSQL 8.5' in version()) <> 0
\echo 'PostgreSQL 8.5'
\else
\echo 'This isn't PostgreSQL 8.5'
\endif

Ifdef

Syntax:

\ifdef variable
....
\elseifdef variable
....
\else
....
\endifdef

Loops

forc

It is loop over cursor. You have to explicitly declare cursor, and you have to close cursor. Forc creates necessary psql variables for fetching row. If these variables exists, then they are overwritten. Attention: psql variables are not stacked. Syntax:

\forc cursorname
....
\endforc [cursorname]

Example:

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

Custom macros

\newcommand
...
\endnewcommand

Parametres are in values named as int from interval 1..10.

-- macro \tt 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

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