Printing run time messages in postgres

Question

Can we use RAISE NOTICE in postgres as equivalent of RAISERROR 'message to display' WITH NOWAIT in SQL Server, or is there a better way to print intermediate messages while postgres queries are running? Please suggest if there is better way to print run time messages in postgres.

INSERT INTO tbl1 (col1) values (val1);
DO $$
begin
raise notice 'insert tbl1 done!';
end;
$$;
UPDATE tbl2 set col2='val2' where ...;
DO $$
begin
raise notice 'update tbl2 done!';
end;
$$;

I apologize if this code is too bad to comment, pls do suggest a better way to do it, Thanks

MichielB · Accepted Answer · 2016-09-19 10:25:10Z

76

Yes, you can use RAISE NOTICE like below. It's correct the way you are doing.

RAISE NOTICE 'i want to print % and %', var1,var2;

See here for more information https://www.postgresql.org/docs/current/static/plpgsql-errors-and-messages.html

EDIT:

begin
INSERT INTO tbl1 (col1) values (val1);
raise notice 'insert tbl1 done!';
end;

edited Sep 19, 2016 at 10:25

MichielB

4,3441 gold badge33 silver badges39 bronze badges

answered Mar 19, 2014 at 14:13

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

RAFIQ Over a year ago

Thanks, Rahul, Ok but i was hoping if we can simplify the syntax around RAISE NOTICE just like how we run INSERT and UPDATE is it possible?

Rahul Over a year ago

Not sure what's your Q. Give an example? But yes it's. With RAISE NOTICE you give the msg you want to print (like the way you are already doing); those % are placeholders.

Craig Ringer Over a year ago

RAISE is a PL/PgSQL statement, not an SQL statement. So you cannot use it in top-level SQL, only within procedures. You can always create a trivial procedure that you can call as raise_notice('message') though.

olivmir Over a year ago

Where can I see these messages after the function has run?

Shwe Over a year ago

@olivmir - It is a late response. I hope you found where it is. For those who are still looking for the output, it is written into "Database Server Output log" window. If you are using DBeaver, you can open that window by clicking on 'Show server output' icon on the left side of the SQL Editor window/pane".

|

Gaurav Koradiya · Accepted Answer · 2019-09-26 07:31:29Z

14

you can use very simple statement in function everywhere.

DO $$ begin raise notice '%',now(); end; $$;

function for reference:

create or replace function test() RETURNS bool AS '
begin
raise notice ''%'',now();
for i IN 0..50000000  loop
     end loop
     raise notice ''%'',now();
     return true;
end;

LANGUAGE 'plpgsql';

edited Sep 26, 2019 at 7:31

answered Sep 26, 2019 at 7:04

Gaurav Koradiya

4216 silver badges8 bronze badges

Comments

What Would Be Cool · Accepted Answer · 2020-04-30 20:54:54Z

4

You could also do normal selects without the DO block.

INSERT INTO tbl1 (col1) values (val1);

SELECT 'insert tbl1 done!' as msg;

UPDATE tbl2 set col2='val2' where ...;

SELECT 'update tbl2 done!' as msg;

The tradeoff is that it does add extra clutter to the output, like

UPDATE 1
      msg
-----------------
update tbl2 done!
(1 row)

answered Apr 30, 2020 at 20:54

What Would Be Cool

7,0066 gold badges50 silver badges44 bronze badges

Comments

Emery Lapinski · Accepted Answer · 2014-03-20 02:39:02Z

2

RAISE NOTICE is part of PL/pgSQL so it's only legal in a function or an anonymous DO block. I guess you could make a function that raises the notice and call that.

answered Mar 20, 2014 at 2:39

Emery Lapinski

1,6621 gold badge19 silver badges28 bronze badges

1 Comment

RAFIQ Over a year ago

Thanks for comment, yes function may be a better approach if we want to print many messages, but currently I am sticking to DO blcok

Crashmeister · Accepted Answer · 2024-12-13 20:30:06Z

0

Not sure if this is what you are looking for, but it is useful anyway. Print to the SQL output can be done using \qecho.

Example:

\qecho '\nDrop Trigger Functions:'
drop function if exists trade_tx_before();
drop function if exists trade_tx_after();

answered Dec 13, 2024 at 20:30

Crashmeister

3852 silver badges14 bronze badges

Collectives™ on Stack Overflow

Printing run time messages in postgres

5 Answers 5

8 Comments

Comments

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

8 Comments

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related