Search Postgresql Archives

Re: Problem creating stored procedure

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On 12/27/05, Ted Byers <r.ted.byers@xxxxxxxxxx> 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 ;)


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux