Print the output of execution of DO anonymous block

Question

I have an anonymous DO block in a Postgres database like below:

DO LANGUAGE plpgsql $$ 
DECLARE
     FUNC_ID smallint;
BEGIN
    SELECT COUNT(1) INTO FUNC_ID FROM FUNCTION WHERE NAME = 'addition';
    IF FUNC_ID = 0 THEN 
       INSERT INTO FUNCTION ( ID, NAME, DESCRIPTION, FUNCTION)
       values ((select (max(id)+1) from FUNCTION), 'subtraction'
              , 'calculate', 'catalog');
    END IF;

END;
$$;

If execute this block of code it gives output as only DO.

How to output the whole block of code to console?

Answer 1

Use a RAISE NOTICE statement

postgres=# DO $$BEGIN RAISE NOTICE 'Hello %', SESSION_USER; END; $$;
NOTICE:  Hello pavel
DO

See more in related documentation.

Answer 2

Your code is twisted in multiple ways. Use instead:

DO
$do$
BEGIN
   IF EXISTS (SELECT 1 FROM function WHERE name = 'addition') THEN
      INSERT INTO function(id, name, description, function)
      SELECT max(id) + 1, 'subtraction', 'calculate', 'catalog'
      FROM   function;

      RAISE NOTICE 'Whatever'; -- see Pavel's answer
   END IF;
END
$do$;

You probably should have a serial primary key, drawing the next value from a SEQUENCE. What you have is prone to race conditions and a typical anti-pattern.

Also I wouldn't use function as identifier even if that's allowed in Postgres. It's a reserved word in the SQL standard.

CREATE TABLE func
   func_id serial PRIMARY KEY
 , func text NOT NULL
 , description text
 , find_proper_name text)
);

Then your whole statement can be:

INSERT INTO func(func, description, find_proper_name)
SELECT 'subtraction', 'calculate', 'catalog'
WHERE  EXISTS (SELECT 1 FROM func WHERE func = 'addition');

You don't need a DO statement at all here.