Re: A function to count number of terms - something wrong please help

Thank you very much Sean !
I learned a great deal today :-)

Thanks again.
Srini

--- Sean Davis <sdavis2@mail.nih.gov> wrote:

>
>
>
> On 2/15/06 12:41 PM, "Srinivas Iyyer"
> <srini_iyyer_bio@yahoo.com> wrote:
>
> > Dear group,
> >  I wrote a function to return the number of
> records
> > that has this query term associated with.
> >
> > Something is going wrong. Looking forward some
> help to
> > correct this function.
> > Could some one help if this function is correct
> way,
> > or please suggest other way please.
> >
> > Thanks
> > srini
> >
> > SQL query and result:
> > test=# select distinct seq_name from sequence,
> go_seq,
> > gos where gos.go_term = 'protein binding' AND
> > gos.gos_id = go_seq.gos_id AND go_seq.seq_id =
> > sequence.seq_id ;
> >    seq_name
> > ---------------
> >  A2M
> >  AATF
> >  ABCD1
> >  ABCD2
> >  ABCD3
> > .......
> > .......
> >  ZP3
> >  ZRANB1
> >  ZW10
> >  ZYX
> > (3862 rows)
> >
> >
> ===================================================
> > Function:
> > CREATE FUNCTION fetch_count_fterm(term) RETURNS
> char
>
> ^^^^
>
> And you are returning mcounts, which is an integer.
> So, this should be an
> integer.
>
>
> > AS '
> > DECLARE
> >         referrer_keys1 RECORD;
> >         fterm ALIAS FOR $1;
> >         mcounts integer;
> > BEGIN
> >         referrer_keys1 IN SELECT distinct seq_name
> > from sequence,go_seq,gos where gos.go_term = fterm
> AND
> > gos.gos_id = go_seq.gos_id AND go_seq.seq_id =
> > sequence.seq_id
> >        mcounts := select count(*) from
> referrer_keys1
> >         return mcounts;
>
> And I don't think your function is going to work as
> is.
>
> How about this:
>
> PREPARE fetch_count_fterm_sql(TEXT) as
>   SELECT count(distinct seq_name) from
>     sequence, go_seq,gos
>     where gos.go_term = $1 AND
>      gos.gos_id = go_seq.gos_id
>      AND go_seq.seq_id = sequence.seq_id group by
> gos.go_term;
>
> Then you can do:
>
> EXECUTE fetch_count_fterm_sql('protein binding');
>
>
> UNTESTED, but I think something like this should
> work.  See here:
>
>
http://www.postgresql.org/docs/8.1/interactive/sql-prepare.html
>
>
>
>


__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com