Print the output of anonymous DO block execution

Question

I have an anonymous DO block in a Postgres database, as shown 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 you execute this block of code, it only outputs as DO .

How to output the entire code block to the console?

Answer 1

Use the RAISE NOTICE statement

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

See the related documentation for more details.

Answer 2

Your code is twisted in several 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 answer END IF; END $do$; 

You should probably have a serial primary key by drawing the next value from SEQUENCE . What you have depends on race conditions and a typical anti-pattern.

Also, I would not use function as an identifier, even if it is allowed in Postgres. This is 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 target operator may 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.