pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedpgpl-problem, what's wrong with my loop?
5+ messages / 3 participants
[nested] [flat]
* pgpl-problem, what's wrong with my loop?
@ 2000-11-20 16:21 Bruno Boettcher <bboett@erm1.u-strasbg.fr>
0 siblings, 2 replies; 5+ messages in thread
From: Bruno Boettcher @ 2000-11-20 16:21 UTC (permalink / raw)
To: pgsql-sql
Hello,
once more, i ran into a problem....
i got no syntax error, i don't know how to debug, except for raising
exceptions.... the loop never executes.... making the select call by hand
with fixed values, returns a result... but the second raise is never passed
with the function...so something seems wrong....
CREATE FUNCTION accSum(text,text) RETURNS int4 AS '
DECLARE
col ALIAS FOR $1;
sumup ALIAS FOR $2;
actsum journal.amount%TYPE;
arow journal%ROWTYPE;
conversion float4;
temp float4;
sum int4;
BEGIN
sum := 0;
RAISE NOTICE ''stats %=% '', col,sumup;
FOR arow IN SELECT currency,amount FROM journal WHERE col=sumup LOOP
RAISE NOTICE ''% current line: %'', arow.id,arow.amount;
SELECT conv FROM currencies WHERE tag=arow.currency INTO conversion;
temp := conversion*arow.amount+0.5;
sum := sum + temp;
END LOOP;
return sum;
END;
' LANGUAGE 'plpgsql';
as sayd :
fibu=> select accSum('plus','102');
NOTICE: stats plus=102
accsum
--------
0
(1 row)
fibu=> SELECT currency,amount FROM journal WHERE plus=102;
currency | amount
----------+--------
EUR | 100000
EUR | 100000
EUR | 100000
EUR | 100000
EUR | 100000
(5 rows)
surely some stupid error somewhere.....
--
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: pgpl-problem, what's wrong with my loop?
@ 2000-11-20 16:37 Tom Lane <tgl@sss.pgh.pa.us>
parent: Bruno Boettcher <bboett@erm1.u-strasbg.fr>
1 sibling, 0 replies; 5+ messages in thread
From: Tom Lane @ 2000-11-20 16:37 UTC (permalink / raw)
To: Bruno Boettcher <bboett@erm1.u-strasbg.fr>; +Cc: pgsql-sql
Bruno Boettcher <bboett@erm1.u-strasbg.fr> writes:
> CREATE FUNCTION accSum(text,text) RETURNS int4 AS '
> DECLARE
> col ALIAS FOR $1;
> sumup ALIAS FOR $2;
> ...
> FOR arow IN SELECT currency,amount FROM journal WHERE col=sumup LOOP
Looks like the WHERE condition is testing for equality between the two
parameters of the function. Since evidently that wasn't what you meant
to do, perhaps you'd be well advised to choose local-variable names that
don't conflict with column names of your tables...
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: pgpl-problem, what's wrong with my loop?
@ 2000-11-20 17:06 Kovacs Zoltan Sandor <tip@pc10.radnoti-szeged.sulinet.hu>
parent: Bruno Boettcher <bboett@erm1.u-strasbg.fr>
1 sibling, 1 reply; 5+ messages in thread
From: Kovacs Zoltan Sandor @ 2000-11-20 17:06 UTC (permalink / raw)
To: Bruno Boettcher <bboett@erm1.u-strasbg.fr>; +Cc: pgsql-sql
> FOR arow IN SELECT currency,amount FROM journal WHERE col=sumup LOOP
My opinion is the problem that you cannot give a column name as a
parameter. But I'm not sure. This never worked for me. The thing here
happens that you get all rows if and only if $1=$2 (as strings) and if
they are not equal, the WHERE clause will stand for constant false. This
second case may be the fact for you.
You should write different codes for the different col parameters in my
opinion. Or you might write a C function which can send arbitrary SQL
queries to the backend.
Regards, Zoltan
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: pgpl-problem, what's wrong with my loop?
@ 2000-11-20 17:40 Kovacs Zoltan Sandor <tip@pc10.radnoti-szeged.sulinet.hu>
parent: Bruno Boettcher <bboett@erm1.u-strasbg.fr>
0 siblings, 0 replies; 5+ messages in thread
From: Kovacs Zoltan Sandor @ 2000-11-20 17:40 UTC (permalink / raw)
To: Bruno Boettcher <bboett@erm1.u-strasbg.fr>; +Cc: pgsql-sql; JanWieck@t-online.de
On Mon, 20 Nov 2000, Bruno Boettcher wrote:
> On Mon, Nov 20, 2000 at 06:06:52PM +0100, Kovacs Zoltan Sandor wrote:
> > > FOR arow IN SELECT currency,amount FROM journal WHERE col=sumup LOOP
> > My opinion is the problem that you cannot give a column name as a
> > parameter. But I'm not sure. This never worked for me. The thing here
> :( can somebody confirm this? in this case i have to check only 2
> cols....
> put if i had more to check, this would be a serious limitation.....
I sent this question to the list about 4 months ago without receiving any
answers. Jan, could you please help?
> > You should write different codes for the different col parameters in my
> > opinion. Or you might write a C function which can send arbitrary SQL
> > queries to the backend.
> :D wanted to stay as SQL'is as possible....
Me too. PLPGSQL is a good piece of ware. :-)
Zoltan
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: pgpl-problem, what's wrong with my loop?
@ 2000-11-20 17:47 Bruno Boettcher <bboett@erm1.u-strasbg.fr>
parent: Kovacs Zoltan Sandor <tip@pc10.radnoti-szeged.sulinet.hu>
0 siblings, 1 reply; 5+ messages in thread
From: Bruno Boettcher @ 2000-11-20 17:47 UTC (permalink / raw)
To: Kovacs Zoltan Sandor <tip@pc10.radnoti-szeged.sulinet.hu>; +Cc: pgsql-sql
On Mon, Nov 20, 2000 at 06:06:52PM +0100, Kovacs Zoltan Sandor wrote:
> > FOR arow IN SELECT currency,amount FROM journal WHERE col=sumup LOOP
> My opinion is the problem that you cannot give a column name as a
> parameter. But I'm not sure. This never worked for me. The thing here
:( can somebody confirm this? in this case i have to check only 2
cols....
put if i had more to check, this would be a serious limitation.....
> You should write different codes for the different col parameters in my
> opinion. Or you might write a C function which can send arbitrary SQL
> queries to the backend.
:D wanted to stay as SQL'is as possible....
anyway thanks for the answer...
--
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
end of thread, other threads:[~2000-11-20 17:47 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-11-20 16:21 pgpl-problem, what's wrong with my loop? Bruno Boettcher <bboett@erm1.u-strasbg.fr>
2000-11-20 16:37 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-11-20 17:06 ` Kovacs Zoltan Sandor <tip@pc10.radnoti-szeged.sulinet.hu>
2000-11-20 17:47 ` Bruno Boettcher <bboett@erm1.u-strasbg.fr>
2000-11-20 17:40 ` Kovacs Zoltan Sandor <tip@pc10.radnoti-szeged.sulinet.hu>
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