Обсуждение: HELP: what's wrong with my PL/PSQL function??

hello!

i have the following function:
CREATE FUNCTION plusSum(text,int4) RETURNS int4 AS ' DECLARE jrlname ALIAS FOR $1; sumup ALIAS FOR $2; actsum int4;
arowRECORD; conversion float8; temp float8; sum float8; BEGIN sum := 0; RAISE NOTICE ''stats % '', sumup; FOR arow IN
SELECT* FROM "jrlname" WHERE plus=sumup LOOP    SELECT conv FROM currencies WHERE tag=arow.currency INTO conversion;
sum:= sum + (arow.amount/conversion);    END LOOP;    return  round(sum); END;         ' LANGUAGE 'plpgsql';select
plusSum('journal','102');

and i get an error:
ERROR:  parser: parse error at or near "$1"
(BTW would be helpful if the thing could spit out also the line
number....)

isolated the prob to this line:
FOR arow IN SELECT * FROM "jrlname" WHERE plus=sumup LOOP
in fact it doesn't seem to like the fact that i use a dynamic table
name??

i remind vaguely that pl/psql had indeed this sort of problem... but
this was years ago.... is there any workaround? even using perl? 

The whole prog is a bookkeeping program... and i want it now really able
to work with several sets of tables.... without duplicating also the
functions i wrote associated to the tables....

BTW is there a possibility to make further select calls out of plperl?
(an example making this would come in handy...)

i am using a postgres7.03 DB on a debian system...

-- 
ciao bboett
==============================================================
bboett@earthling.net
http://inforezo.u-strasbg.fr/~bboett http://erm1.u-strasbg.fr/~bboett
===============================================================
the total amount of intelligence on earth is constant.
human population is growing....

Bruno Boettcher wrote:
> 
> and i get an error:
> ERROR:  parser: parse error at or near "$1"
> (BTW would be helpful if the thing could spit out also the line
> number....)

You can get syntax error line-numbers by editing the function in a
text-file and \i file to import it. Otherwise, plpgsql's error reporting
is a bit weak.

> isolated the prob to this line:
> FOR arow IN SELECT * FROM "jrlname" WHERE plus=sumup LOOP
> in fact it doesn't seem to like the fact that i use a dynamic table
> name??
> i remind vaguely that pl/psql had indeed this sort of problem... but
> this was years ago.... is there any workaround? even using perl?

Still there - plpgsql script is pre-compiled so there's no easy way
around it. There is a new EXECUTE command available which takes a string
and runs it as a query, but I don't think it'll help you here (could be
wrong, check the archives there was a discussion about a similar problem
some time ago IIRC).

> The whole prog is a bookkeeping program... and i want it now really able
> to work with several sets of tables.... without duplicating also the
> functions i wrote associated to the tables....

You could use the EXECUTE system to generate one function for each
table. See the auto-timestamp generator in Roberto Mello's plpgsql
cookbook at http://techdocs.postgresql.org/ for an example.

Otherwise tcl/perl scripting should help you since these aren't
pre-compiled. Can't comment further since I don't use them.

HTH

- Richard Huxton

Richard Huxton <dev@archonet.com> writes:
> Bruno Boettcher wrote:
>> and i get an error:
>> ERROR:  parser: parse error at or near "$1"
>> (BTW would be helpful if the thing could spit out also the line
>> number....)

> You can get syntax error line-numbers by editing the function in a
> text-file and \i file to import it. Otherwise, plpgsql's error reporting
> is a bit weak.

Actually, plpgsql DOES report the line number.  Into the postmaster log.
For example:

regression=# create function zz() returns int as '
regression'# begin
regression'#   x := x + 1;
regression'#   return x;
regression'# end;' language 'plpgsql';
CREATE
regression=# select zz();
ERROR:  parser: parse error at or near "x"
regression=#

tail postmaster.log shows

ERROR:  parser: parse error at or near "x"
DEBUG:  Last error occured while executing PL/pgSQL function zz
DEBUG:  line 2 at SQL statement

I am not sure why this precious info is so deeply buried.  I know why
it's not part of the ERROR itself: our elog mechanism doesn't support
that.  But seems like it could at least be made a NOTICE rather than a
DEBUG message.
        regards, tom lane

Tom Lane wrote:
> Richard Huxton <dev@archonet.com> writes:
> > Bruno Boettcher wrote:
> >> and i get an error:
> >> ERROR:  parser: parse error at or near "$1"
> >> (BTW would be helpful if the thing could spit out also the line
> >> number....)
>
> > You can get syntax error line-numbers by editing the function in a
> > text-file and \i file to import it. Otherwise, plpgsql's error reporting
> > is a bit weak.
>
> Actually, plpgsql DOES report the line number.  Into the postmaster log.
> For example:
>
> regression=# create function zz() returns int as '
> regression'# begin
> regression'#   x := x + 1;
> regression'#   return x;
> regression'# end;' language 'plpgsql';
> CREATE
> regression=# select zz();
> ERROR:  parser: parse error at or near "x"
> regression=#
>
> tail postmaster.log shows
>
> ERROR:  parser: parse error at or near "x"
> DEBUG:  Last error occured while executing PL/pgSQL function zz
> DEBUG:  line 2 at SQL statement
>
> I am not sure why this precious info is so deeply buried.  I know why
> it's not part of the ERROR itself: our elog mechanism doesn't support
> that.  But seems like it could at least be made a NOTICE rather than a
> DEBUG message.
   Because  at  the  time  the  NOTICE  will be sent, the client   already  received  the  ERROR  and  doesn't  read
from  the   connection until the next command.  So at least you'd have to   send an empty query to get it.
 


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #



_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com

Jan Wieck <JanWieck@yahoo.com> writes:
> Tom Lane wrote:
>> I am not sure why this precious info is so deeply buried.  I know why
>> it's not part of the ERROR itself: our elog mechanism doesn't support
>> that.  But seems like it could at least be made a NOTICE rather than a
>> DEBUG message.

>     Because  at  the  time  the  NOTICE  will be sent, the client
>     already  received  the  ERROR  and  doesn't  read  from   the
>     connection until the next command.  So at least you'd have to
>     send an empty query to get it.

Au contraire.  To test this, I changed elog(DEBUG)s to elog(NOTICE)s
in pl_exec.c, and now I get:

regression=# select zz();
NOTICE:  Last error occured while executing PL/pgSQL function zz
NOTICE:  line 2 at SQL statement
ERROR:  parser: parse error at or near "x"
regression=#

The reason is that libpq doesn't report query done until it gets the
trailing 'Z' (ReadyForQuery) message, and the notices will come out
before that.

You might need to reword the notices a bit because they will appear
before the error itself, but this still seems to me a lot better than
having to go digging in the postmaster log (if there is one).
        regards, tom lane