pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
HELP: what's wrong with my PL/PSQL function??
5+ messages / 4 participants
[nested] [flat]

* HELP: what's wrong with my PL/PSQL function??
@ 2001-05-18 09:21 Bruno Boettcher <bboett@erm1.u-strasbg.fr>
  2001-05-19 12:07 ` Re: HELP: what's wrong with my PL/PSQL function?? Richard Huxton <dev@archonet.com>
  0 siblings, 1 reply; 5+ messages in thread

From: bboett@erm1.u-strasbg.fr @ 2001-05-18 09:21 UTC (permalink / raw)
  To: pgsql-sql

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;
  arow RECORD;
  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....



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: HELP: what's wrong with my PL/PSQL function??
  2001-05-18 09:21 HELP: what's wrong with my PL/PSQL function?? Bruno Boettcher <bboett@erm1.u-strasbg.fr>
@ 2001-05-19 12:07 ` Richard Huxton <dev@archonet.com>
  2001-05-19 14:58   ` Re: HELP: what's wrong with my PL/PSQL function?? Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 5+ messages in thread

From: Richard Huxton @ 2001-05-19 12:07 UTC (permalink / raw)
  To: Bruno Boettcher <bboett@erm1.u-strasbg.fr>; +Cc: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: HELP: what's wrong with my PL/PSQL function??
  2001-05-18 09:21 HELP: what's wrong with my PL/PSQL function?? Bruno Boettcher <bboett@erm1.u-strasbg.fr>
  2001-05-19 12:07 ` Re: HELP: what's wrong with my PL/PSQL function?? Richard Huxton <dev@archonet.com>
@ 2001-05-19 14:58   ` Tom Lane <tgl@sss.pgh.pa.us>
  2001-05-21 12:48     ` Re: HELP: what's wrong with my PL/PSQL function?? Jan Wieck <JanWieck@Yahoo.com>
  0 siblings, 1 reply; 5+ messages in thread

From: Tom Lane @ 2001-05-19 14:58 UTC (permalink / raw)
  To: Richard Huxton <dev@archonet.com>; +Cc: Bruno Boettcher <bboett@erm1.u-strasbg.fr>; pgsql-sql@postgresql.org, Jan Wieck <JanWieck@Yahoo.com>

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



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: HELP: what's wrong with my PL/PSQL function??
  2001-05-18 09:21 HELP: what's wrong with my PL/PSQL function?? Bruno Boettcher <bboett@erm1.u-strasbg.fr>
  2001-05-19 12:07 ` Re: HELP: what's wrong with my PL/PSQL function?? Richard Huxton <dev@archonet.com>
  2001-05-19 14:58   ` Re: HELP: what's wrong with my PL/PSQL function?? Tom Lane <tgl@sss.pgh.pa.us>
@ 2001-05-21 12:48     ` Jan Wieck <JanWieck@Yahoo.com>
  2001-05-21 14:16       ` Re: HELP: what's wrong with my PL/PSQL function?? Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 5+ messages in thread

From: Jan Wieck @ 2001-05-21 12:48 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Richard Huxton <dev@archonet.com>; Bruno Boettcher <bboett@erm1.u-strasbg.fr>; pgsql-sql@postgresql.org, Jan Wieck <JanWieck@Yahoo.com>

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




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: HELP: what's wrong with my PL/PSQL function??
  2001-05-18 09:21 HELP: what's wrong with my PL/PSQL function?? Bruno Boettcher <bboett@erm1.u-strasbg.fr>
  2001-05-19 12:07 ` Re: HELP: what's wrong with my PL/PSQL function?? Richard Huxton <dev@archonet.com>
  2001-05-19 14:58   ` Re: HELP: what's wrong with my PL/PSQL function?? Tom Lane <tgl@sss.pgh.pa.us>
  2001-05-21 12:48     ` Re: HELP: what's wrong with my PL/PSQL function?? Jan Wieck <JanWieck@Yahoo.com>
@ 2001-05-21 14:16       ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 5+ messages in thread

From: Tom Lane @ 2001-05-21 14:16 UTC (permalink / raw)
  To: Jan Wieck <JanWieck@yahoo.com>; +Cc: Richard Huxton <dev@archonet.com>; Bruno Boettcher <bboett@erm1.u-strasbg.fr>; pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~2001-05-21 14:16 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-05-18 09:21 HELP: what's wrong with my PL/PSQL function?? Bruno Boettcher <bboett@erm1.u-strasbg.fr>
2001-05-19 12:07 ` Richard Huxton <dev@archonet.com>
2001-05-19 14:58   ` Tom Lane <tgl@sss.pgh.pa.us>
2001-05-21 12:48     ` Jan Wieck <JanWieck@Yahoo.com>
2001-05-21 14:16       ` Tom Lane <tgl@sss.pgh.pa.us>

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