BUG #16181: Error Creating Stored Procedures
The following bug has been logged on the website:
Bug reference: 16181
Logged by: Shahzad Ahmad
Email address: shahzadahmad00@gmail.com
PostgreSQL version: 12.1
Operating system: Ubuntu 18.04.3 LTS
Description:
I tried both pgAdmin GUI and psql using a script file. In both instances i
got the same error.
CREATE OR REPLACE PROCEDURE admin.my_test(IN role_id integer DEFAULT 0)
LANGUAGE 'plpgsql'
AS $BODY$
IF my_test.user_id = 0 THEN
Select *
from admin.roles
order by role_name;
ELSE
Select *
from admin.roles
WHERE role_id = my_test.user_id
order by role_name;
END IF;
$BODY$;
ERROR: syntax error at or near "IF"
LINE 5: IF my_test.user_id = 0 THEN
^
SQL state: 42601
Character: 103
On Wed, Jan 1, 2020 at 4:43 PM PG Bug reporting form <noreply@postgresql.org>
wrote:
The following bug has been logged on the website:
Bug reference: 16181
Logged by: Shahzad Ahmad
Email address: shahzadahmad00@gmail.com
PostgreSQL version: 12.1
Operating system: Ubuntu 18.04.3 LTS
Description:I tried both pgAdmin GUI and psql using a script file. In both instances i
got the same error.CREATE OR REPLACE PROCEDURE admin.my_test(IN role_id integer DEFAULT 0)
LANGUAGE 'plpgsql'
Not a bug...the documentation shows how to write a pl/pgsql function/stored
procedure.
https://www.postgresql.org/docs/12/plpgsql-structure.html
Note the "BEGIN" and "END" portions of that syntax diagram are not optional.
David J.
David,
Thanks for the quick reply.
It is working with BEGIN and END.
The code was generated by pgAdmin4 GUI and I somehow incorrectly assumed that GUI would include BEGIN and END statements.
Thanks for your help.
Shahzad
From: David G. Johnston <david.g.johnston@gmail.com>
Sent: Wednesday, January 1, 2020 7:02 PM
To: shahzadahmad00@gmail.com; PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
Subject: Re: BUG #16181: Error Creating Stored Procedures
On Wed, Jan 1, 2020 at 4:43 PM PG Bug reporting form <noreply@postgresql.org <mailto:noreply@postgresql.org> > wrote:
The following bug has been logged on the website:
Bug reference: 16181
Logged by: Shahzad Ahmad
Email address: shahzadahmad00@gmail.com <mailto:shahzadahmad00@gmail.com>
PostgreSQL version: 12.1
Operating system: Ubuntu 18.04.3 LTS
Description:
I tried both pgAdmin GUI and psql using a script file. In both instances i
got the same error.
CREATE OR REPLACE PROCEDURE admin.my_test(IN role_id integer DEFAULT 0)
LANGUAGE 'plpgsql'
Not a bug...the documentation shows how to write a pl/pgsql function/stored procedure.
https://www.postgresql.org/docs/12/plpgsql-structure.html
Note the "BEGIN" and "END" portions of that syntax diagram are not optional.
David J.