pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedpostgresql 8.01, plpgsql
4+ messages / 2 participants
[nested] [flat]
* postgresql 8.01, plpgsql
@ 2005-03-15 01:35 Timothy Smith <timothy@open-networks.net>
2005-03-15 02:41 ` Re: postgresql 8.01, plpgsql Michael Fuhr <mike@fuhr.org>
0 siblings, 1 reply; 4+ messages in thread
From: Timothy Smith @ 2005-03-15 01:35 UTC (permalink / raw)
To: pgsql-sql
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;
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: postgresql 8.01, plpgsql
2005-03-15 01:35 postgresql 8.01, plpgsql Timothy Smith <timothy@open-networks.net>
@ 2005-03-15 02:41 ` Michael Fuhr <mike@fuhr.org>
2005-03-15 03:38 ` Re: postgresql 8.01, plpgsql Timothy Smith <timothy@open-networks.net>
0 siblings, 1 reply; 4+ messages in thread
From: Michael Fuhr @ 2005-03-15 02:41 UTC (permalink / raw)
To: Timothy Smith <timothy@open-networks.net>; +Cc: pgsql-sql
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?
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: postgresql 8.01, plpgsql
2005-03-15 01:35 postgresql 8.01, plpgsql Timothy Smith <timothy@open-networks.net>
2005-03-15 02:41 ` Re: postgresql 8.01, plpgsql Michael Fuhr <mike@fuhr.org>
@ 2005-03-15 03:38 ` Timothy Smith <timothy@open-networks.net>
2005-03-15 04:25 ` Re: postgresql 8.01, plpgsql Michael Fuhr <mike@fuhr.org>
0 siblings, 1 reply; 4+ messages in thread
From: Timothy Smith @ 2005-03-15 03:38 UTC (permalink / raw)
To: Michael Fuhr <mike@fuhr.org>; 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: postgresql 8.01, plpgsql
2005-03-15 01:35 postgresql 8.01, plpgsql Timothy Smith <timothy@open-networks.net>
2005-03-15 02:41 ` Re: postgresql 8.01, plpgsql Michael Fuhr <mike@fuhr.org>
2005-03-15 03:38 ` Re: postgresql 8.01, plpgsql Timothy Smith <timothy@open-networks.net>
@ 2005-03-15 04:25 ` Michael Fuhr <mike@fuhr.org>
0 siblings, 0 replies; 4+ messages in thread
From: Michael Fuhr @ 2005-03-15 04:25 UTC (permalink / raw)
To: Timothy Smith <timothy@open-networks.net>; +Cc: pgsql-sql
On Tue, Mar 15, 2005 at 01:38:00PM +1000, Timothy Smith wrote:
> 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 "$$
Could you attach the files you're reading? It might be helpful if
we could see everything you're trying to execute.
What version of PostgreSQL are you using? Presumably 8.X since
you're using dollar quoting.
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2005-03-15 04:25 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-03-15 01:35 postgresql 8.01, plpgsql Timothy Smith <timothy@open-networks.net>
2005-03-15 02:41 ` Michael Fuhr <mike@fuhr.org>
2005-03-15 03:38 ` Timothy Smith <timothy@open-networks.net>
2005-03-15 04:25 ` Michael Fuhr <mike@fuhr.org>
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