Thread: problem w/plpgsql proc
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
leo <lmendoza@garbersoft.net> writes: > CREATE FUNCTION editEmail(integer, smallint, varchar, varchar) RETURNS > integer AS ' > ... > -- if it wasn't, then insert the new record. An undoubled quote mark in a function body is bad news... regards, tom lane
Leo, You're gonna feel like a dork when I point it out ... > -- if the emailaddr was found, then update the record. > -- if it wasn't, then insert the new record. ^^^ SINGLE QUOTE HERE!! BAD! You can't have single quotes inside a function, especially in comments. They will be treated as the end of the function definition string. If you write the T-SQL Procedure ---> PostgreSQL function porting guide, I volunteer to edit (certifiable MS SQL DBA). You might wanna wait for 7.2, where Jan says that cursor support in PL/pgSQL functions will be added. -Josh ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete information technology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
Attachment
I may be wrong, but I believe you should not have a semicolon after beginning a loop (in the for clause) and the IF NOT EXISTSclause should be different: you are probably wanting FOUND which is a special variable so you will have to do theselect first and then check the FOUND variable afterwards. You are also trying to use FOUND as a normal variable, whichI suspect will fail because it is a reserved word. Regards, Aasmund. On Fri, 12 Oct 2001 11:05:45 -0500, leo <lmendoza@garbersoft.net> wrote: > 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 > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html Aasmund Midttun Godal aasmund@godal.com - http://www.godal.com/ +47 40 45 20 46
IF (em = rec.emailaddr) THEN found = 1; -- looks likewe found the email addr. END IF; found=1; should be found:=1; - Stuart