PostgreSQL Discussion - Problem creating stored procedure

Author

Problem creating stored procedure

Ted Byers

2005-12-27, 1:23 pm

I am puzzled. Can ayone explain why I get an error from Postgres on this simple stored procedure?

The following is from the pgAdmin III History window:
-- Executing query:
CREATE PROCEDURE addEntity (
fn IN VARCHAR,
ln IN VARCHAR,
ivar IN VARCHAR,
hi IN VARCHAR,
pw IN VARCHAR,
ea IN VARCHAR,
ad IN VARCHAR,
c IN VARCHAR,
p IN VARCHAR,
co IN VARCHAR,
pc IN VARCHAR
)
AS
DECLARE
varID INTEGER
BEGIN
SELECT int varID uid from uids where email_address=ea;
IF varID IS NOT NULL THEN
INSERT INTO addy (uid,address,city,pr
ovince,country,posta
l_code)
VALUES (varID,ad,c,p,co,pc)

ELSE
INSERT INTO uids(family_name,fir
st_name,initials,hid
,pword,email_address
)
VALUES (ln,fn,ivar,hi,pw,ea
)
INSERT INTO addys(...) VALUES (currval('seq'),ad,c
,p,co,pc)
END IF;
END
LANGUAGE 'sql' VOLATILE;

ERROR: syntax error at or near "PROCEDURE" at character 8

Judging from the examples in the manual (around page 600), my procedure ought to be fine, but clearly Postgres doesn't like it.

Thanks,

Ted

R.E. (Ted) Byers, Ph.D., Ed.D.
R & D Decision Support Software
http://www. randddecisionsupport
solutions.com/

Uwe C. Schroeder

2005-12-27, 1:23 pm

Try
CREATE FUNCTION .....

On Tuesday 27 December 2005 09:41, Ted Byers wrote:
> I am puzzled. Can ayone explain why I get an error from Postgres on this
> simple stored procedure?
>
> The following is from the pgAdmin III History window:
> -- Executing query:
> CREATE PROCEDURE addEntity (
> fn IN VARCHAR,
> ln IN VARCHAR,
> ivar IN VARCHAR,
> hi IN VARCHAR,
> pw IN VARCHAR,
> ea IN VARCHAR,
> ad IN VARCHAR,
> c IN VARCHAR,
> p IN VARCHAR,
> co IN VARCHAR,
> pc IN VARCHAR
> )
> AS
> DECLARE
> varID INTEGER
> BEGIN
> SELECT int varID uid from uids where email_address=ea;
> IF varID IS NOT NULL THEN
> INSERT INTO addy (uid,address,city,pr
ovince,country,posta
l_code)
> VALUES (varID,ad,c,p,co,pc)

> ELSE
> INSERT INTO uids(family_name,fir
st_name,initials,hid
,pword,email_address
)
> VALUES (ln,fn,ivar,hi,pw,ea
)
> INSERT INTO addys(...) VALUES (currval('seq'),ad,c
,p,co,pc)
> END IF;
> END
> LANGUAGE 'sql' VOLATILE;
>
> ERROR: syntax error at or near "PROCEDURE" at character 8
>
> Judging from the examples in the manual (around page 600), my procedure
> ought to be fine, but clearly Postgres doesn't like it.
>
>
> Thanks,
>
> Ted
>
>
> R.E. (Ted) Byers, Ph.D., Ed.D.
> R & D Decision Support Software
> http://www. randddecisionsupport
solutions.com/

--
UC

--
Open Source Solutions 4U, LLC 1618 Kelly St
Phone: +1 707 568 3056 Santa Rosa, CA 95401
Cell: +1 650 302 2405 United States
Fax: +1 707 568 6416

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Jaime Casanova

2005-12-27, 1:23 pm

On 12/27/05, Ted Byers <r.ted.byers@rogers.com> wrote:
> I am puzzled. Can ayone explain why I get an error from Postgres on this
> simple stored procedure?
>
> The following is from the pgAdmin III History window:
> -- Executing query:
> CREATE PROCEDURE addEntity (

one reason could be that PROCEDURE's doesn't exist in postgres... you
have to create a FUNCTION...

> fn IN VARCHAR,
> ln IN VARCHAR,
> ivar IN VARCHAR,
> hi IN VARCHAR,
> pw IN VARCHAR,
> ea IN VARCHAR,
> ad IN VARCHAR,
> c IN VARCHAR,
> p IN VARCHAR,
> co IN VARCHAR,
> pc IN VARCHAR

i think it's [IN|OUT|INOUT] var_name datatype... note the order...

> )
> AS

needs a $$ sign to begin function

> DECLARE
> varID INTEGER

needs a semicolon

> BEGIN
> SELECT int varID uid from uids where email_address=ea;

select into... note the missing "o"

> IF varID IS NOT NULL THEN
> INSERT INTO addy
> (uid,address,city,pr
ovince,country,posta
l_code)
> VALUES (varID,ad,c,p,co,pc)

> ELSE
> INSERT INTO
> uids(family_name,fir
st_name,initials,hid
,pword,email_address
)
> VALUES (ln,fn,ivar,hi,pw,ea
)
> INSERT INTO addys(...) VALUES (currval('seq'),ad,c
,p,co,pc)
> END IF;
> END

needs a semicolon

needs a $$ sign to end function

> LANGUAGE 'sql' VOLATILE;
>

it is not sql language but plpgsql

> ERROR: syntax error at or near "PROCEDURE" at character 8
>
> Judging from the examples in the manual (around page 600), my procedure
> ought to be fine, but clearly Postgres doesn't like it.
>

maybe are you looking at the examples in how to convert oracle
procedures tu postgres functions? read carefully...

>
> Thanks,
>
> Ted
>
> R.E. (Ted) Byers, Ph.D., Ed.D.
> R & D Decision Support Software
> http://www. randddecisionsupport
solutions.com/

--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Michael Fuhr

2005-12-27, 1:23 pm

On Tue, Dec 27, 2005 at 12:41:44PM -0500, Ted Byers wrote:
> I am puzzled. Can ayone explain why I get an error from Postgres
> on this simple stored procedure?

There are several mistakes in the code you posted:

* PostgreSQL doesn't have a CREATE PROCEDURE command. Use CREATE
FUNCTION.

* You didn't declare a return type or any OUT or INOUT parameters.

* You didn't quote the function body.

* Several statements are missing terminating semicolons.

* You wrote "SELECT int" instead of "SELECT INTO".

* You wrote "INSERT INTO addys(...)" instead of providing a column
list. If this is the actual code then it's a syntax error, and
if it's not the actual code then we need to see what you're
really doing.

* You wrote plpgsql code but declared the function to be sql.

> Judging from the examples in the manual (around page 600), my
> procedure ought to be fine, but clearly Postgres doesn't like it.

What section of the manual are you looking at, and for what version
of PostgreSQL (many of us use the online documentation so page
numbers don't mean anything)? Are you mixing Oracle syntax with
PL/pgSQL syntax?

--
Michael Fuhr

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly

Sponsored Links