Try
CREATE FUNCTIN

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 PRCEDURE 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 NT NULL THEN
INSERT INT addy (uid,address,city,province,country,postal_code)
VALUES (varID,ad,c,p,co,pc)
ELSE
INSERT INT uids(family_name,first_name,initials,hid,pword,ema il_address)
VALUES (ln,fn,ivar,hi,pw,ea)
INSERT INT addys() VALUES (currval('seq'),ad,c,p,co,pc)
END IF;
END
LANGUAGE 'sql' VLATILE;

ERRR: syntax error at or near "PRCEDURE" 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

12/27/05, Ted Byers <r.ted.byers (AT) rogers (DOT) comwrote:
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 PRCEDURE addEntity (

one reason could be that PRCEDURE's doesn't exist in postgres you
have to create a FUNCTIN

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|UT|INUT] 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 NT NULL THEN
INSERT INT addy
(uid,address,city,province,country,postal_code)
VALUES (varID,ad,c,p,co,pc)
ELSE
INSERT INT
uids(family_name,first_name,initials,hid,pword,ema il_address)
VALUES (ln,fn,ivar,hi,pw,ea)
INSERT INT addys() VALUES (currval('seq'),ad,c,p,co,pc)
END IF;
END

needs a semicolon

needs a $$ sign to end function

LANGUAGE 'sql' VLATILE;

it is not sql language but plpgsql

ERRR: syntax error at or near "PRCEDURE" 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

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 PRCEDURE command. Use CREATE
FUNCTIN.

* You didn't declare a return type or any UT or INUT parameters.

* You didn't quote the function body.

* Several statements are missing terminating semicolons.

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

* You wrote "INSERT INT 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 syntax with
PL/pgSQL syntax?