2

I couldn't get my first stored procedure to work, so I copied this example from the manual:

CREATE PROCEDURE insert_data(a integer, b integer)
LANGUAGE SQL
AS $$
INSERT INTO tbl VALUES (a);
INSERT INTO tbl VALUES (b);
$$;

CALL insert_data(1, 2);

and I get the following error message:

ERROR:  syntax error at or near "PROCEDURE"
LINE 1: CREATE PROCEDURE insert_data(a integer, b integer)
               ^
SQL state: 42601
Character: 8

What am I doing wrong?

I'm running the database on localhost, running as admin user. I installed it myself. Installation process completed without errors, and I am able to create tables, functions, triggers, etc.

  • ... can't use them in Postgres 10 is what @a_horse meant to say. – Erwin Brandstetter Mar 8 '19 at 17:17
  • That's crazy! Any semi-decent SQL should have stored procedures...Thanks anyway! – Granny Aching Mar 8 '19 at 17:25
  • @GrannyAching: just create a function - you can do essentially everything with them as well. Or just upgrade to Postgres 11 – a_horse_with_no_name Mar 8 '19 at 17:44
  • Unrelated, but: you don't need to insert statement to insert to rows: insert into tbl values (a), (b); would do just fine – a_horse_with_no_name Mar 8 '19 at 17:52
  • @a_horse_with_no_name - I could upgrade to 11 on my local copy. But we have 10 running in production, and I want the same testing environment. About your second (unrelated) comment, I copied and pasted the sample code from the manual. – Granny Aching Mar 8 '19 at 18:08
0

As mentioned in the comments, PostgreSQL-10 does not have stored procedures. Same is true about earlier versions.

Workaround alternatives:

  • Upgrade to version 11 or later
  • Use a function

Thanks, @Erwin Brandstetter and @a_horse_with_no_name

Not the answer you're looking for? Browse other questions tagged or ask your own question.