[SQL] postgresql 8.01, plpgsql
i have the following function in plpgsql giving stynax errors all over the place. i have doen createlang on the db, as far as i can see i'm right. is there anything obviously wrong? one thing to note is i followed this example http://www.zigo.dhs.org/postgresql/#insert_or_update and it gives the same errors. "ERROR: unterminated dollar-quoted string at or near "$$ BEGIN LOOP" CREATE OR REPLACE FUNCTION insert_update_daily_takings (ID BIGINT, TillName VARCHAR, Tape NUMERIC(10,2), Cash NUMERIC(10,2), GM NUMERIC(10,2), VenueManager NUMERIC(10,2), AsstManager NUMERIC(10,2), BarManager NUMERIC(10,2), PRCards NUMERIC(10,2), otherPromo NUMERIC(10,2), Functions NUMERIC(10,2), Accounts NUMERIC(10,2), Spill NUMERIC(10,2), Orings NUMERIC(10,2), Variance NUMERIC(10,2) ) RETURNS VOID AS $$ 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; IF found THEN RETURN; END IF; BEGIN INSERT INTO daily_takings (id, till_name, tape, cash, promo_manager, venue_manager, asst_manager, bar_manager, pr_cards, other_promo, functions, accounts, spill, o_rings, variance) VALUES (ID, TillName, Tape, Cash, GM, VenueManager, AsstManager, BarManager, PRCards, otherPromo, Functions, Accounts, Spill, Orings, Variance); RETURN; EXCEPTION WHEN unique_violation THEN NULL END; END LOOP; END; $$ LANGUAGE plpgsql; ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] postgresql 8.01, plpgsql
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
[SQL] re postgresql 8.01 plpgsql
problem sovled. i was still in psql from 7.4, i only upgraded this morning and left it on there. it was the source of all my grief. ---(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
[SQL] unsubscribe
Timothy Smith wrote: i have the following function in plpgsql giving stynax errors all over the place. i have doen createlang on the db, as far as i can see i'm right. is there anything obviously wrong? one thing to note is i followed this example http://www.zigo.dhs.org/postgresql/#insert_or_update and it gives the same errors. "ERROR: unterminated dollar-quoted string at or near "$$ BEGIN LOOP" CREATE OR REPLACE FUNCTION insert_update_daily_takings (ID BIGINT, TillName VARCHAR, Tape NUMERIC(10,2), Cash NUMERIC(10,2), GM NUMERIC(10,2), VenueManager NUMERIC(10,2), AsstManager NUMERIC(10,2), BarManager NUMERIC(10,2), PRCards NUMERIC(10,2), otherPromo NUMERIC(10,2), Functions NUMERIC(10,2), Accounts NUMERIC(10,2), Spill NUMERIC(10,2), Orings NUMERIC(10,2), Variance NUMERIC(10,2) ) RETURNS VOID AS $$ 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; IF found THEN RETURN; END IF; BEGIN INSERT INTO daily_takings (id, till_name, tape, cash, promo_manager, venue_manager, asst_manager, bar_manager, pr_cards, other_promo, functions, accounts, spill, o_rings, variance) VALUES (ID, TillName, Tape, Cash, GM, VenueManager, AsstManager, BarManager, PRCards, otherPromo, Functions, Accounts, Spill, Orings, Variance); RETURN; EXCEPTION WHEN unique_violation THEN NULL END; END LOOP; END; $$ LANGUAGE plpgsql; ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
