Enhanced-psql
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)