7

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.

6
  • ... can't use them in Postgres 10 is what @a_horse meant to say. Mar 8, 2019 at 17:17
  • That's crazy! Any semi-decent SQL should have stored procedures...Thanks anyway! Mar 8, 2019 at 17:25
  • @GrannyAching: just create a function - you can do essentially everything with them as well. Or just upgrade to Postgres 11
    – user1822
    Mar 8, 2019 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
    – user1822
    Mar 8, 2019 at 17:52
  • 1
    I'm surprised that this question is labelled off-topic. Version 10 is still widely used, so I'm sure someone else switching to PostgreSQL could end up using 10 and expect stored procedures to work. Mar 13, 2019 at 19:19

1 Answer 1

8

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

2
  • Even in PostgreSQL 12, PROCEDURE is not supported. Jul 26, 2020 at 3:42
  • @RavindraGullapalli v12 has them, at least according to the output of pg_dump --schema-only.
    – RonJohn
    Sep 22, 2023 at 19:56

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