Re: Making a query from 2 tables at same time

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