Received: from postgresql.org.org (webmail.postgresql.org [216.126.85.28]) by postgresql.org (8.11.3/8.11.1) with SMTP id f4IEalA98216 for ; Fri, 18 May 2001 10:36:47 -0400 (EDT) (envelope-from pgsql-sql-owner@postgresql.org) Received: from erm1.u-strasbg.fr (erm1.u-strasbg.fr [130.79.74.61]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f4I9EkA50769 for ; Fri, 18 May 2001 05:14:46 -0400 (EDT) (envelope-from bboett@erm1.u-strasbg.fr) Received: by erm1.u-strasbg.fr (Postfix, from userid 501) id D5C415E38; Fri, 18 May 2001 11:21:36 +0200 (CEST) Date: Fri, 18 May 2001 11:21:36 +0200 To: pgsql-sql@postgresql.org Subject: HELP: what's wrong with my PL/PSQL function?? Message-ID: <20010518112136.A468@erm1.u-strasbg.fr> Mime-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Disposition: inline User-Agent: Mutt/1.3.15i From: bboett@erm1.u-strasbg.fr (Bruno Boettcher) X-Archive-Number: 200105/196 X-Sequence-Number: 3013 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....