Re: postgresql 8.01, plpgsql - Mailing list pgsql-sql

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