MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Postgres Print Debug Notes

without comments

A student asked how you print output from PL/pgSQL blocks. The student wanted to know if there was something like the following in Oracle’s PL/SQL programming language:

dbms_output.put_line('some string');

or, in Java programming the:

System.out.println("some string");

The RAISE NOTICE is the equivalent to these in Postgres PL/pgSQL, as shown in the following anonymous block:

do $$
BEGIN
  raise notice 'Hello World!';
END;
$$;

It prints:

NOTICE:  Hello World!

You can write a hello_world function as a named PL/pgSQL block:

CREATE FUNCTION hello_world()
RETURNS text AS
$$
DECLARE
  output  VARCHAR(20);
BEGIN
  /* Query the string into a local variable. */
  SELECT 'Hello World!' INTO output;
 
  /* Return the output text variable. */
  RETURN output;
END
$$ LANGUAGE plpgsql;

You can call it with the following:

SELECT hello_world();

It prints:

 hello_world  
--------------
 Hello World!
(1 row)

Here’s a full test case with stored procedure in PL/pgSQL:

-- Drop the msg table.
DROP TABLE msg;
 
-- Create the msg table.
CREATE TABLE msg
( comment  VARCHAR(400) );
 
-- Transaction Management Example.
DROP PROCEDURE IF EXISTS testing
( IN pv_one                 VARCHAR(30)
, IN pv_two                 VARCHAR(10));
 
-- Transaction Management Example.
CREATE OR REPLACE PROCEDURE testing
( IN pv_one                 VARCHAR(30)
, IN pv_two                 VARCHAR(10)) AS
$$
DECLARE
  /* Declare error handling variables. */
  err_num      TEXT;
  err_msg      INTEGER;
BEGIN
  /* Log actdual parameter values. */
  INSERT INTO msg VALUES (pv_one||'.'||pv_two);
 
EXCEPTION
  WHEN OTHERS THEN
    err_num := SQLSTATE;
    err_msg := SUBSTR(SQLERRM,1,100);
    RAISE NOTICE 'Trapped Error: %', err_msg;
END
$$ LANGUAGE plpgsql;
 
do $$
DECLARE
  lv_one VARCHAR(30) := 'INDIVIDUAL';
  lv_two VARCHAR(19) := 'R11-514-34';
BEGIN
  RAISE NOTICE '[%]', lv_one;
  RAISE NOTICE '[%]', lv_two;
  CALL testing( pv_one := lv_one, pv_two := lv_two );
END
$$;
 
-- Query any logged results.
SELECT * FROM msg;

It prints:

DROP TABLE
CREATE TABLE
DROP PROCEDURE
CREATE PROCEDURE
psql:fixed.sql:61: NOTICE:  [INDIVIDUAL]
psql:fixed.sql:61: NOTICE:  [R11-514-34]
DO
        comment        
-----------------------
 INDIVIDUAL.R11-514-34
(1 row)

I hope this helps those looking for a solution.

Written by maclochlainn

October 12th, 2019 at 5:03 pm

Posted in Java,PL/pgSQL,pl/sql,Postgres,PostgreSQL

Tagged with ,

«
»