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(at)mail(dot)nih(dot)gov> wrote:

>
>
>
> On 2/15/06 12:41 PM, "Srinivas Iyyer"
> <srini_iyyer_bio(at)yahoo(dot)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