pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
problem 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