Re: Problem creating stored procedure

On 12/27/05, Ted Byers <r(dot)ted(dot)byers(at)rogers(dot)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,province,country,postal_code)
> VALUES (varID,ad,c,p,co,pc)
> ELSE
> INSERT INTO
> uids(family_name,first_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.randddecisionsupportsolutions.com/

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