problem w/plpgsql proc

From: leo <lmendoza(at)garbersoft(dot)net>
To: pgsql-sql(at)postgresql(dot)org
Subject: problem w/plpgsql proc
Date: 2001-10-12 16:05:45
Message-ID: 9q74ak$5v5$1@news.tht.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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 add it.
returns:
-1 - the user already has 8 entries
0 - the record was updated, and the table 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 records
rec RECORD;
cnt INTEGER;
found SMALLINT := 0;
BEGIN
-- see if the email address exists, then see if renumbering is needed
CREATE 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 8 entries 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 like we found the email addr.
END IF;
END LOOP;

-- if the emailaddr was found, then update the record.
-- if it wasn't, then insert 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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Tom Lane 2001-10-12 17:32:15 Re: indexing and LIKE
Previous Message Patrik Kudo 2001-10-12 08:16:33 Re: indexing and LIKE