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,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 ;)
