Thread: Stumped on PlPgSql
Ok, I give up :-(
I have recently upgraded my development system to 7.0.2, my production server is still
6.5.1
I have a Plpsgsql function that I have been using in 6.5.1 and it works fine.
The same function on 7.0.2 gives me an error. I have looked in the doc and haven't
seen any change in syntax but maybe Im blind.
The function is
-- ------------------------------------------------------------------------
-- PostCharge
-- ------------------------------------------------------------------------
CREATE FUNCTION "postcharge" ( int4, int4, int4, int4, int4, text )
RETURNS bool AS '
DECLARE
clnt ALIAS FOR $1;
invnum ALIAS FOR $2;
rescode ALIAS FOR $3;
zone ALIAS FOR $4;
q ALIAS FOR $5;
cmmnt ALIAS FOR $6;
res RECORD;
ctrl RECORD;
amt float8;
qf float8;
BEGIN
SELECT * INTO res FROM resource WHERE code = rescode;
IF res.status <> ''A'' THEN
RETURN ''f'';
END IF;
SELECT * INTO ctrl FROM control;
qf := float8( q );
IF zone = 0 THEN
amt := res.rate_internal * qf;
END IF;
IF zone = 1 THEN
amt := res.rate_local * qf;
END IF;
IF zone = 2 THEN
amt := res.rate_national * qf;
END IF;
IF zone = 3 THEN
amt := res.rate_international * qf;
END IF;
INSERT INTO client_trans (
client, resource, invnum, period, tstamp, comment, qty, amount
) VALUES (
clnt, rescode, invnum, ctrl.open_period, ''now'', cmmnt, q, amt );
RETURN ''t'';
END;
' LANGUAGE 'plpgsql';
The error is
ERROR: parser: parse error at or near "$1"
Any help would be appreciated.
AGRE Enterprises <agree@godzone.net.nz> writes:
> I have a Plpsgsql function that I have been using in 6.5.1 and it works fine.
> The same function on 7.0.2 gives me an error. I have looked in the doc and haven't
> seen any change in syntax but maybe Im blind.
> The error is
> ERROR: parser: parse error at or near "$1"
Not much help is it :-(. Apparently there's something wrong with the
way the plpgsql function executor is generating plain-SQL queries from
your function, but we need more context to tell just what.
Here's what I'd do to get more info: make sure that the postmaster is
creating a log file (you should have started it without -S, and
redirected its stdout and stderr to some convenient log file, not
/dev/null). Next, run psql with PGOPTIONS set for debugging output
level 2 or more, say
export PGOPTIONS="-d2"
psql yourdb
(syntax of setting environment variables varies depending on what
shell you use, but hopefully you know what to do for yours). Then,
try to execute the function, so that you get the error report. Now
you can look in the postmaster's logfile and you will see the exact
sequence of queries that the plpgsql function executor tried to submit
to the main SQL parser. This will at least narrow down the problem
to one line of the plpgsql function. If it's still not clear what's
wrong, send in the logged queries and we'll take a look...
regards, tom lane
This was a classic case of seeing what I thought was there not what was actually there :-( > export PGOPTIONS="-d2" > psql yourdb This gave the show away, thanks Tom. I had added an extra parameter, but gave it the same ALIAS as a column in the insert, hence the syntax error. The column was rplace by $1.
Tom Lane wrote:
> AGRE Enterprises <agree@godzone.net.nz> writes:
> > I have a Plpsgsql function that I have been using in 6.5.1 and it works fine.
> > The same function on 7.0.2 gives me an error. I have looked in the doc and haven't
> > seen any change in syntax but maybe Im blind.
>
> > The error is
> > ERROR: parser: parse error at or near "$1"
>
> Not much help is it :-(. Apparently there's something wrong with the
> way the plpgsql function executor is generating plain-SQL queries from
> your function, but we need more context to tell just what.
Yeah - I know.
The function handler put's out some DEBUG messages in the
postmaster log (cannot do it as NOTICE since the client
already got the ERROR during the SPI call). Please tell us
what these DEBUG messages say. They usually contain a line
number where it happened inside the function.
Jan
>
> Here's what I'd do to get more info: make sure that the postmaster is
> creating a log file (you should have started it without -S, and
> redirected its stdout and stderr to some convenient log file, not
> /dev/null). Next, run psql with PGOPTIONS set for debugging output
> level 2 or more, say
> export PGOPTIONS="-d2"
> psql yourdb
> (syntax of setting environment variables varies depending on what
> shell you use, but hopefully you know what to do for yours). Then,
> try to execute the function, so that you get the error report. Now
> you can look in the postmaster's logfile and you will see the exact
> sequence of queries that the plpgsql function executor tried to submit
> to the main SQL parser. This will at least narrow down the problem
> to one line of the plpgsql function. If it's still not clear what's
> wrong, send in the logged queries and we'll take a look...
>
> regards, tom lane
>
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
Usually what causes this error for me is following type of code: table x with column a, and local variable called a. Then, if you are not careful, it'll refer to the local variable in a where clause, when you intended to refer to table column. On Mon, 23 Oct 2000, Jan Wieck wrote: > Tom Lane wrote: > > AGRE Enterprises <agree@godzone.net.nz> writes: > > > I have a Plpsgsql function that I have been using in 6.5.1 and it works fine. > > > The same function on 7.0.2 gives me an error. I have looked in the doc and haven't > > > seen any change in syntax but maybe Im blind. > > > > > The error is > > > ERROR: parser: parse error at or near "$1" > > > > Not much help is it :-(. Apparently there's something wrong with the > > way the plpgsql function executor is generating plain-SQL queries from > > your function, but we need more context to tell just what. > > Yeah - I know. > > The function handler put's out some DEBUG messages in the > postmaster log (cannot do it as NOTICE since the client > already got the ERROR during the SPI call). Please tell us > what these DEBUG messages say. They usually contain a line > number where it happened inside the function. > > > Jan > > > > > Here's what I'd do to get more info: make sure that the postmaster is > > creating a log file (you should have started it without -S, and > > redirected its stdout and stderr to some convenient log file, not > > /dev/null). Next, run psql with PGOPTIONS set for debugging output > > level 2 or more, say > > export PGOPTIONS="-d2" > > psql yourdb > > (syntax of setting environment variables varies depending on what > > shell you use, but hopefully you know what to do for yours). Then, > > try to execute the function, so that you get the error report. Now > > you can look in the postmaster's logfile and you will see the exact > > sequence of queries that the plpgsql function executor tried to submit > > to the main SQL parser. This will at least narrow down the problem > > to one line of the plpgsql function. If it's still not clear what's > > wrong, send in the logged queries and we'll take a look... > > > > regards, tom lane > > > > > -- > > #======================================================================# > # It's easier to get forgiveness for being wrong than for being right. # > # Let's break this rule - forgive me. # > #================================================== JanWieck@Yahoo.com # > > >
> Usually what causes this error for me is following type of code: table x > with column a, and local variable called a. Then, if you are not careful, > it'll refer to the local variable in a where clause, when you intended to > refer to table column. I have already replied to the list and Tom Lane, but it seems to have gotten lost somewhere. As stated above, this is exactly what I had done. I looked at the code over and over and kept seeing what I thought was there and not what was actually there :-( The give away way setting the -d2 option and getting postgres to spill the beans in the log file. A very useful switch to know about :-)