9.5.1 DBMS_OUTPUT

Overview: Sends messages to clients such as psql from PL/pgSQL.

Features

Features	Description
ENABLE	Features of this package are enabled.
DISABLE	Features of this package are disabled.
SERVEROUTPUT	Controls whether messages are sent.
PUT	Messages are sent.
PUT_LINE	Messages are sent with a newline appended.
NEW_LINE	Newlines are sent as messages.
GET_LINE	1 line is read from the buffer.
GET_LINES	Multiple lines are read from the buffer.

Specification format

This section explains the procedures available in the DBMS_OUTPUT package.

ENABLE

ENABLE enables the use of PUT, PUT_LINE, NEW_LINE, GET_LINE, and GET_LINES.
With multiple executions of ENABLE, the value specified last is the buffer size (in bytes). Specify in the range between 2000 and 1000000 if a buffer size is to be specified.
The default value of the buffer size is 20000. If NULL is specified as the buffer size, 1000000 will be used.
If ENABLE has not been executed, PUT, PUT_LINE, NEW_LINE, GET_LINE, and GET_LINES are ignored even if they are executed.

Example

PERFORM DBMS_OUTPUT.ENABLE(20000);

DISABLE

DISABLE disables the use of PUT, PUT_LINE, NEW_LINE, GET_LINE, and GET_LINES.
Remaining buffer information is discarded.

Example

PERFORM DBMS_OUTPUT.DISABLE();

SERVEROUTPUT

SERVEROUTPUT controls whether messages are sent.
The logical value specifies whether to send messages.
If the "true" logical value is specified, then when PUT, PUT_LINE, or NEW_LINE is executed, the message is sent to a client such as psql and not stored in the buffer.
If the "false" logical value is specified, then when PUT, PUT_LINE, or NEW_LINE is executed, the message is stored in the buffer and not sent to a client such as psql.
See
Refer to "Boolean Type" in "The SQL Language" in the PostgreSQL Documentation for information on logical values.

Example

PERFORM DBMS_OUTPUT.SERVEROUTPUT(TRUE);

PUT

PUT configures the message that is sent.
The string is the message that is sent.
When "True" is specified in the logical value at SERVEROUTPUT, the messages are sent to clients such as psql.
When "False" is specified in the logical value at SERVEROUTPUT, the messages are retained in the buffer.
PUT does not append a newline, so to append a newline, execute NEW_LINE.
If a string longer than the buffer size specified in ENABLE is sent, an error occurs.

Example

PERFORM DBMS_OUTPUT.PUT('abc');

PUT_LINE

PUT_LINE appends a newline to messages for sending.
The string is the message that is sent.
When "True" is specified in the logical value at SERVEROUTPUT, the messages are sent to clients such as psql.
When "False" is specified in the logical value at SERVEROUTPUT, the messages are retained in the buffer.
PUT_LINE appends a newline to the end of messages.
If a string longer than the buffer size specified in ENABLE is sent, an error occurs.

Example

PERFORM DBMS_OUTPUT.PUT_LINE('abc');

NEW_LINE

NEW_LINE sets a newline in the message that is sent.
When "True" is specified in the logical value at SERVEROUTPUT, the messages are sent to clients such as psql.
When "False" is specified in the logical value at SERVEROUTPUT, the messages are retained in the buffer.

Example

PERFORM DBMS_OUTPUT.NEW_LINE();

GET_LINE

GET_LINE extracts one line messages stored in the buffer.
The information extracted is acquired as the values of the line and status columns with the SELECT statement.
line is a TEXT type. A one line message extracted from the buffer is stored.
status is an INTEGER type. The extracted result will be stored. 0 is stored when successful. 1 is stored when it fails.
If GET_LINE or GET_LINES is executed, then PUT, PUT_LINE, or NEW_LINE is executed with message still not extracted from the buffer, the messages not extracted from the buffer will be discarded.

Example

DECLARE
    buff1   VARCHAR(20);
    stts1   INTEGER;
BEGIN
    SELECT line,status INTO buff1,stts1 FROM DBMS_OUTPUT.GET_LINE();

GET_LINES

GET_LINES extracts multiple line messages stored in the buffer.
The information extracted is acquired as the values of the lines and numlines columns with the SELECT statement.
lines is a TEXT type. It stores the lines acquired from the buffer.
numlines is an INTEGER type. It stores the number of lines acquired from the buffer.
The number of lines acquired is an INTEGER type. It is the maximum number of lines extracted from the buffer.
If GET_LINE or GET_LINES is executed, then PUT, PUT_LINE, or NEW_LINE is executed with message still not extracted from the buffer, the messages not extracted from the buffer will be discarded.

Example

DECLARE
    buff    VARCHAR(20)[10];
    stts    INTEGER := 10;
BEGIN
    SELECT lines, numlines INTO buff,stts FROM DBMS_OUTPUT.GET_LINES(stts);

A usage example of DBMS_OUTPUT is shown below.

CREATE FUNCTION dbms_output_exe() RETURNS VOID AS $$
DECLARE
	buff1	VARCHAR(20);
	buff2	VARCHAR(20);
	stts1	INTEGER;
	stts2	INTEGER;
BEGIN
	PERFORM DBMS_OUTPUT.DISABLE();
	PERFORM DBMS_OUTPUT.ENABLE();
	PERFORM DBMS_OUTPUT.SERVEROUTPUT(FALSE);
	PERFORM DBMS_OUTPUT.PUT('DBMS_OUTPUT TEST 1');
	PERFORM DBMS_OUTPUT.NEW_LINE();
	PERFORM DBMS_OUTPUT.PUT_LINE('DBMS_OUTPUT TEST 2');
	SELECT line,status INTO buff1,stts1 FROM DBMS_OUTPUT.GET_LINE();
	SELECT line,status INTO buff2,stts2 FROM DBMS_OUTPUT.GET_LINE();
	PERFORM DBMS_OUTPUT.SERVEROUTPUT(TRUE);
	PERFORM DBMS_OUTPUT.PUT_LINE(buff1);
	PERFORM DBMS_OUTPUT.PUT_LINE(buff2);
END;
$$ LANGUAGE plpgsql;
SELECT dbms_output_exe();
DROP FUNCTION dbms_output_exe();

9.5.1 DBMS_OUTPUT

9.5.1.1 Description

9.5.1.2 Example