Thread: numeric conversions?
Hello, me again :( i just ran into another problem.... didn't found it neither in the doc nor in the FAQ..... the currencies conversion factors i use are stored as float4 in a table, the values to apply on are stores as int4 .... is the type casting done automaticly? how? can i have an influence of the cast order? is there an easy way to doing the rounding? the operation i want to do is something along this line: int4 = ((int4) float4 * (float4)int4 +.5); -- 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....
> is the type casting done automaticly? how? can i have an influence of > the cast order? is there an easy way to doing the rounding? bf2=# select 1 / 2 from dual;?column? ---------- 0 (1 row) bf2=# select 1::float / 2 from dual;?column? ---------- 0.5 (1 row) bf2=# select (1::float / 2)::int from dual;?column? ---------- 0 (1 row) Also you have the functions round, floor, and ceil, which do what you would expect. -Jonathan
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 ''%
currentline: %'', 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 | 100000EUR | 100000EUR | 100000EUR | 100000EUR | 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....
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
> 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
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....
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