44

I'm trying to run this in PostgreSQL 9.2:

RAISE NOTICE 'Hello, World!';

And the server says:

Error : ERROR:  syntax error at or near "RAISE"
LINE 1: RAISE NOTICE 'Hello, World!'
            ^

Why?

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
yegor256
  • 102,010
  • 123
  • 446
  • 597

3 Answers3

87

Use an anonymous code block:

DO language plpgsql $$
BEGIN
  RAISE NOTICE 'hello, world!';
END
$$;

Variables are referenced using %:

RAISE NOTICE '%', variable_name;
GregM
  • 1,818
  • 1
  • 13
  • 18
Tomas Greif
  • 21,685
  • 23
  • 106
  • 155
  • 2
    To make it shorter you could remove line breaks and *language plpgsql* – Ruut Jan 29 '14 at 15:14
  • @ruut In the pg 9.6 version I am using I frequently get errors trying to create functions where I've forgotten to specify the language `ERROR: no language specified` Maybe it was previously a default? – Davos Nov 13 '17 at 12:01
24

raise is PL/pgSQL only.

See 43.9. Errors and Messages.

create or replace function r(error_message text) returns void as $$
begin
    raise notice '%', error_message;
end;
$$ language plpgsql;

select r('an error message');
NOTICE:  an error message
Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
Clodoaldo Neto
  • 118,695
  • 26
  • 233
  • 260
0

A simple example:

CREATE OR REPLACE FUNCTION test()
RETURNS TRIGGER AS
'
DECLARE


num int;

 BEGIN
IF TG_OP = ''INSERT'' THEN
select count(*) into num from test_table;
IF num >= 1 THEN
RAISE WARNING ''Cannot Insert more than one row'';
RETURN OLD;
END IF;
ELSE
RETURN NEW;
END IF;

END;
' LANGUAGE plpgsql;
Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
Mehdi Sadighian
  • 108
  • 1
  • 8
  • An explanation would be in order. E.g., what is the idea/gist? From [the Help Center](https://stackoverflow.com/help/promotion): *"...always explain why the solution you're presenting is appropriate and how it works"*. Please respond by [editing (changing) your answer](https://stackoverflow.com/posts/44130519/edit), not here in comments (***without*** "Edit:", "Update:", or similar - the answer should appear as if it was written today). – Peter Mortensen Apr 23 '22 at 12:59