Pau Marc Munoz Torres wrote:
Ok,
I have two tables, first one, that i call precalc has the following
structure
...
and a numer of index that is a real number resulting of a function (function
is called idr and i talk about it bellow)
...
another table is local than has the following fields
id values for both tables are the same.
idr function is a function that I wrote
create function IDR(char,char,char,char,char,varchar(20)) returns real AS'
...
select into P1 score from PSSMS where AA=$1 and POS=1 and
MOLEC=$6;
...
' LANGUAGE plpgsql IMMUTABLE;
This function is not IMMUTABLE, it queries a table. Labelling it
IMMUTABLE doesn't make it so.
Oh and it takes "char" parameters but you seem to pass it "varchar".
where PSSMS was a temporay table (it don't exist right now)
Then how can the function be meaningful?
now i would like to perform a select to get p1,p2,p6,p7,p9, sp and pos from
those register that her value in the index is bigger than x
OK
mhc2db=> select t1.sp, t1.pos,t2.p1, t2.p4, t2.p6, t2.p7, t2.p9 from local
as t1, precalc as t2 where t1.ce='ACIAD' and idr(t2.p1, t2.p4, t2.p6, t2.p7,
t2.p9, 'HLA-DRB5*0101')>2;
if i perfom a select like
select * from precalc where dr(p1,p4, p6, p7, p9, 'HLA-DRB5*0101')>2;
I assume you mean idr(...)
If you want this to work properly, then your function needs to genuinely
be immutable. That means it only depends on the values you pass in to
it, not on any other tables.
If it works in the simple case above, then that is purely chance. So -
can idr() calculate its score based on its inputs?
or
select * from local where ce='ACIAD';
I don't see what this query has to do with your problem.
--
Richard Huxton
Archonet Ltd
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
