Re: A function to count number of terms - something

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
^^^^

Should be a data type (like "TEXT").
> 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;
> END;
> ' LANGUAGE plpgsql;
>
> test=# \i fetch_Fterm.sql
> psql:fetch_Fterm.sql:11: ERROR: type term does not
> exist
> test=#
>
>
>
> Could some one help if this function is correct way,
> or please suggest other way please.
>
> Thanks
>
>
>
> __________________________________________________
> Do You Yahoo!?
> Tired of spam? Yahoo! Mail has the best spam protection around
> http://mail.yahoo.com
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match