Re: [SQL] postgresql 8.01, plpgsql

[Timothy Smith]

Michael Fuhr wrote:
On Tue, Mar 15, 2005 at 11:35:13AM +1000, Timothy Smith wrote:
 

i have the following function in plpgsql giving stynax errors all over 
the place.
   

When I load the function you posted I get this:
test=> \i foo.sql
psql:foo.sql:87: ERROR:  syntax error at or near "END" at character 2851
psql:foo.sql:87: LINE 83:         END;
psql:foo.sql:87:                  ^
If I add a semicolon to the NULL statement in the exception handler
then it loads successfully.
 

one thing to note is i followed this example 
http://www.zigo.dhs.org/postgresql/#insert_or_update and it gives the 
same errors.
   

I get no errors loading the example in the "Insert or Update" section
of that page.
 

"ERROR:  unterminated dollar-quoted string at or near "$$
BEGIN
LOOP"
   

This looks like the parser is reaching EOF before the end of the
function body.  How are you loading the function?  I usually store code
in a file and use "psql -f filename" from the shell or "\i filename"
from the psql prompt.  If you're not already doing that, what happens
when you try it?
 

ok i tried loading it from a file like you suggested and this is what i 
got for MY function

\i /home/timothy/function
psql:/home/timothy/function:35: ERROR:  unterminated dollar-quoted 
string at or near "$$
BEGIN
       LOOP
               UPDATE daily_takings SET till_name = TillName,
                               tape = Tape,
                               cash = Cash,
                               promo_manager = GM,
                               venue_manager = VenueManager,
                               asst_manager = AsstManager,
                               bar_manager = BarManager,
                               pr_cards = PRCards,
                               other_promo = otherPromo,
                               functions = Functions,
                               accounts = Accounts,
                               spill = Spill,
                               o_rings = Orings,
                               variance = Variance
                               WHERE id = ID
                                AND till_name = TillName;" at character 604
psql:/home/timothy/function:37: ERROR:  syntax error at or near "IF" at 
character 3
psql:/home/timothy/function:38: ERROR:  syntax error at or near "IF" at 
character 7
psql:/home/timothy/function:70: ERROR:  syntax error at or near "INSERT" 
at character 13
psql:/home/timothy/function:71: ERROR:  syntax error at or near "RETURN" 
at character 4
psql:/home/timothy/function:74: ERROR:  syntax error at or near 
"EXCEPTION" at character 3
psql:/home/timothy/function:75: ERROR:  syntax error at or near "LOOP" 
at character 6
psql:/home/timothy/function:76: WARNING:  there is no transaction in 
progress
COMMIT
psql:/home/timothy/function:78: ERROR:  unterminated dollar-quoted 
string at or near "$$
LANGUAGE plpgsql;" at character 1

and similarly for the example i got
\i /home/timothy/function
psql:/home/timothy/function:1: ERROR:  unterminated dollar-quoted string 
at or near "$$ BEGIN LOOP UPDATE db SET b = data WHERE a = key;" at 
character 63
psql:/home/timothy/function:1: ERROR:  syntax error at or near "IF" at 
character 2
psql:/home/timothy/function:1: ERROR:  syntax error at or near "IF" at 
character 6
psql:/home/timothy/function:2: ERROR:  syntax error at or near "INSERT" 
at character 8
psql:/home/timothy/function:2: ERROR:  syntax error at or near "RETURN" 
at character 2
psql:/home/timothy/function:2: ERROR:  syntax error at or near 
"EXCEPTION" at character 2

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
     subscribe-nomail command to [EMAIL PROTECTED] so that your
     message can get through to the mailing list cleanly