1

I'm kinda new on Postgres, so I need your help for this one.

postgres=# select version();
                          version
------------------------------------------------------------
 PostgreSQL 12.3, compiled by Visual C++ build 1914, 64-bit
(1 row)

1. CREATE PROCEDURE test (INT,varchar(200))
2. LANGUAGE plpgsql    
3. AS $$
4. BEGIN
5.
6.  create table test1 as
7.  select id,name from mst_user_mobile limit 5
8.
9.    COMMIT;
10. END;
11. $$;

From what I read, Postgres version above 10, support PROCEDURE method. But when I execute the code it always error on line 1 (on word PROCEDURE)

here error that i got:

ERROR:  syntax error at or near "PROCEDURE"
LINE 1: CREATE PROCEDURE test (INT,varchar(200))
               ^
SQL state: 42601
Character: 8

any helps are welcome

nstrtm
  • 15
  • 6
  • 1
    What is the full error message ? I cannot reproduce. – pifor Jun 26 '20 at 08:54
  • 1
    Please **[edit]** your question (by clicking on the [edit] link below it) and add the **complete** error message you get ([edit] your question do not put additional information into comments) –  Jun 26 '20 at 08:57
  • Terminate the `create table` statement with a semicolon (`;`): `... limit 5;`. If there are more problems, come back with the full error message. – Erwin Brandstetter Jun 29 '20 at 01:11
  • I already add the semicolon, but still have same issues. I add some details above, hope you can help me. thanks. – nstrtm Jun 29 '20 at 04:51
  • This [works just fine](https://dbfiddle.uk/?rdbms=postgres_12&fiddle=0319b6c07470dd5b6effced7c2de2e64) there must be something else you are not telling us. Btw: you should give your parameter names. –  Jun 29 '20 at 05:45

2 Answers2

0

You report to be using Postgres 12.3. Yet, the reported error message is exactly what I see in Postgres 10 (or older):

ERROR:  syntax error at or near "PROCEDURE"
LINE 1: CREATE PROCEDURE test (INT,varchar(200))

db<>fiddle here

CREATE PROCEDURE was introduced with Postgres 11.
I suspect you are connected to the wrong / a different database.

All that aside, your example could just be a function. The COMMIT is pointless as last command. See:

Or, it's another missing semicolon before the CREATE PROCEDURE command. (You show another one of those in the question.) I can reproduce the error message this way, too:

db<>fiddle here

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
-1

I guess the problem are the ; on END and the last $$. You shouldn't use them in this cases!

danielr0d
  • 1