problem w/plpgsql proc
| От | leo |
|---|---|
| Тема | problem w/plpgsql proc |
| Дата | |
| Msg-id | 9q74ak$5v5$1@news.tht.net обсуждение исходный текст |
| Ответы |
Re: problem w/plpgsql proc
Re: problem w/plpgsql proc Re: problem w/plpgsql proc |
| Список | pgsql-sql |
Hi all - This might be the wrong forum for this, but I don't want to cross-post
unless someone tells me I should.
Anyways, I'm having a problem trying to compile this plpg proc. I'll post
the proc and the errors. I'm coming from a T-SQL background, so hopefully
when I get the hang of Pl/PgSQL I'll be able to draft up a doc for porting
from T-SQL to Pl/PgSQL.
proc:
/***editEmail: all vars required - will renumber the sequence if needed - if the email address is not found, it will
addit. returns: -1 - the user already has 8 entries 0 - the record was updated, and
thetable renumbered 1 - the record was added.**/
--DROP FUNCTION editEmail(integer, smallint, varchar, varchar);
CREATE FUNCTION editEmail(integer, smallint, varchar, varchar) RETURNS
integer AS '
DECLARE ufk ALIAS FOR $1; seq ALIAS FOR $2; em ALIAS FOR $3; emp ALIAS FOR $4;
--for renumbering the recordsrec RECORD;cnt INTEGER;found SMALLINT := 0;
BEGIN-- see if the email address exists, then see if renumbering is neededCREATE TEMP TABLE this_user AS
SELECT* FROM tblemailadd WHERE emuserfk = ufk;
GET DIAGNOSTICS cnt = ROW_COUNT; --equiv. to @@ROWCOUNT
-- see if the user exists in the table, then see if the user already-- has 8 entries. If so - return -1 (error)... max
8entries allowed :)IF (cnt > 7) THEN IF NOT EXISTS (SELECT emseqnum FROM this_user WHERE emailaddr = em; ) THEN
RETURN -1; END IF;END IF;
--see if renumbering is needed..IF (cnt > 1) THEN FOR rec IN SELECT * FROM this_user LOOP;
--renumber the sequences UPDATE tblemailadd SET
emseqnum = rec.emseqnum + 1 WHERE emuserfk = ufk AND emailaddr =
rec.emailaddr;
IF (em = rec.emailaddr) THEN found = 1; -- looks
likewe found the email addr. END IF; END LOOP;
-- if the emailaddr was found, then update the record. -- if it wasn't,
theninsert the new record. IF (found = 1) THEN UPDATE tblemailadd
SET emseqnum = seq, emailaddr = em, emprettyname = emp
121 >>> WHERE emuserfk = ufk; RETURN 0;
ELSE INSERT tblemailadd (emuserfk, emseqnum, emailaddr, emprettyname)
VALUES (ufk, seq, em, emp); RETURN 1; END IF;
ELSE IF (cnt > 7) THEN RETURN -1; --alas! the user has too many records to proceed!
END IF
--make sure that the sequencing order begins intact IF (cnt = 1 AND seq = 1) THEN
seq := 2; ELSE IF (cnt = 0 AND seq != 1) THEN
seq:= 1 END IF; END IF;
INSERT tblemailadd (emuserfk, emseqnum, emailaddr, emprettyname) VALUES (ufk, seq, em,
emp);
RETURN 1; --huzahh! the record has been added!END IF;
END;
'LANGUAGE 'plpgsql';
errors:
psql:edit_procs.sql:121: ERROR: parser: parse error at or near "t"
psql:edit_procs.sql:122: ERROR: parser: parse error at or near "return"
psql:edit_procs.sql:125: ERROR: parser: parse error at or near "ELSE"
psql:edit_procs.sql:126: ERROR: parser: parse error at or near "return"
psql:edit_procs.sql:127: ERROR: parser: parse error at or near "if"
psql:edit_procs.sql:131: ERROR: parser: parse error at or near "ELSE"
psql:edit_procs.sql:136: ERROR: parser: parse error at or near "if"
psql:edit_procs.sql:140: ERROR: parser: parse error at or near "ELSE"
psql:edit_procs.sql:141: ERROR: parser: parse error at or near "if"
psql:edit_procs.sql:144: ERROR: parser: parse error at or near
"tblemailadd"
psql:edit_procs.sql:146: ERROR: parser: parse error at or near "return"
psql:edit_procs.sql:147: ERROR: parser: parse error at or near "if"
psql:edit_procs.sql:148: NOTICE: COMMIT: no transaction in progress
COMMIT
this is part of a larger script, but the function declaration before this
works perfectly, so I assume the problem lies here.
sorry for the length...
TIA
leo
В списке pgsql-sql по дате отправления: