pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedproblem w/plpgsql proc
5+ messages / 5 participants
[nested] [flat]
* problem w/plpgsql proc
@ 2001-10-12 16:05 leo <lmendoza@garbersoft.net>
0 siblings, 3 replies; 5+ messages in thread
From: leo @ 2001-10-12 16:05 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: problem w/plpgsql proc
@ 2001-10-15 15:20 Josh Berkus <josh@agliodbs.com>
parent: leo <lmendoza@garbersoft.net>
2 siblings, 0 replies; 5+ messages in thread
From: Josh Berkus @ 2001-10-15 15:20 UTC (permalink / raw)
To: leo <lmendoza@garbersoft.net>; pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: problem w/plpgsql proc
@ 2001-10-15 15:20 Tom Lane <tgl@sss.pgh.pa.us>
parent: leo <lmendoza@garbersoft.net>
2 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2001-10-15 15:20 UTC (permalink / raw)
To: leo <lmendoza@garbersoft.net>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: problem w/plpgsql proc
@ 2001-10-15 16:06 Aasmund Midttun Godal <postgresql@envisity.com>
parent: leo <lmendoza@garbersoft.net>
2 siblings, 0 replies; 5+ messages in thread
From: Aasmund Midttun Godal @ 2001-10-15 16:06 UTC (permalink / raw)
To: lmendoza@garbersoft.net; +Cc: pgsql-sql
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 EXISTS clause should be different: you are probably wanting FOUND which is a special variable so you will have to do the select first and then check the FOUND variable afterwards. You are also trying to use FOUND as a normal variable, which I 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: problem w/plpgsql proc
@ 2001-10-16 10:57 Henshall, Stuart - WCP <SHenshall@westcountrypublications.co.uk>
0 siblings, 0 replies; 5+ messages in thread
From: Henshall, Stuart - WCP @ 2001-10-16 10:57 UTC (permalink / raw)
To: 'lmendoza@garbersoft.net' <lmendoza@garbersoft.net>; pgsql-sql
IF (em = rec.emailaddr) THEN
found = 1; -- looks like we found
the email addr.
END IF;
found=1; should be found:=1;
- Stuart
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2001-10-16 10:57 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-10-12 16:05 problem w/plpgsql proc leo <lmendoza@garbersoft.net>
2001-10-15 15:20 ` Josh Berkus <josh@agliodbs.com>
2001-10-15 15:20 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-10-15 16:06 ` Aasmund Midttun Godal <postgresql@envisity.com>
2001-10-16 10:57 Re: problem w/plpgsql proc Henshall, Stuart - WCP <SHenshall@westcountrypublications.co.uk>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox