PostgreSQL: Print Values on the SCREEN

Table of Contents

Here I wrote the way to print values in PostgreSQL PL/pgSQL onto your screen. (In Oracle PL/SQL, we can use DBMS_OUTPUT to print values.)

Tested Environment

Client: version 9.6
Server: version 9.3

Process

Use RAISE. RAISE is something like error, but in Pl/pgSQL it shows not only error.

RAISE handles following 6 kinds of messages and error (PostgreSQL 9.6.3)。

DEBUG
LOG
INFO
NOTICE
WARNING
EXCEPTION

Only EXCEPTION means error, other means messages at certain level.

In PostgreSQL, you can set minimum level to log in server, and the minimum level to notify to client. As default, the minimum level to log is WARNING, and the minimum level to notify to client is NOTICE. The levels order is different between for server logging and for client notification.

DO $$
DECLARE
BEGIN
RAISE NOTICE    '%', 1;
RAISE LOG       '%', 2;
RAISE INFO      '%', 3;
RAISE NOTICE    '%', 4;
RAISE WARNING   '%', 5;
RAISE EXCEPTION '%', 6;
END$$;<code>

DO $$

DECLARE

BEGIN

RAISE NOTICE '%', 1;

RAISE LOG '%', 2;

RAISE INFO '%', 3;

RAISE NOTICE '%', 4;

RAISE WARNING '%', 5;

RAISE EXCEPTION '%', 6;

END$$;<code>

NOTICE:  1
INFO:  3
NOTICE:  4
WARNING:  5
ERROR:  6

NOTICE: 1

INFO: 3

NOTICE: 4

WARNING: 5

ERROR: 6

Show Variable

If you want to show the variables, use %.

RAISE NOTICE '% % %', VAR1, VAR2, VAR3;

1	RAISE NOTICE '% % %', VAR1, VAR2, VAR3;

Show the Result of SELECT

The select result of 1 row 1 column can be shown like variable.

RAISE NOTICE '% %', (SELECT NOW()), (SELECT 1);

1	RAISE NOTICE '% %', (SELECT NOW()), (SELECT 1);

SELECT statement must be surrounded by parentheses.

The Life