Home > OS >  How to RAISE NOTICE in PostgreSQL?
How to RAISE NOTICE in PostgreSQL?

Time:01-08

I'm using pgAdmin, and I want to have a simple raise notice; referring to this, I entered RAISE NOTICE 'note'; and got this error:

ERROR:  syntax error at or near "RAISE"
LINE 1: RAISE NOTICE 'note';

The only way I could manage to get an output was by using this (which I don't understand well either):

DO $$
BEGIN
RAISE NOTICE 'note';
END;
$$ LANGUAGE plpgsql

And got this output:

NOTICE:  note
DO

Could someone please explain this?

CodePudding user response:

RAISE is a PL/pgSQL command and can only be used inside PL/pgSQL. The DO command creates an anonymous PL/pgSQL block (something like a "temporary procedure") and therefor you can use RAISE inside that PL/pgSQL code.

RAISE can not be used in plain SQL, that's why you get the error

CodePudding user response:

Wrap RAISE into a procedure

create procedure raise_notice (s text) language plpgsql as 
$$
begin 
    raise notice '%', s;
end;
$$;

and call it in SQL

call raise_notice('note');

For PG version before 11 create a function that returns void with the same body and select from it in SQL

select raise_notice('note');
  • Related