Обсуждение: Indexed Views
Does postgreSQL have the ability to index a view? Or index a computed column such as an aggregate?
Thanks
Thanks
You simply index the underlying table. Sean On 2/15/06 10:35 AM, "Ketema Harris" <ketema@gmail.com> wrote: > Does postgreSQL have the ability to index a view? Or index a computed column > such as an aggregate? Thanks
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 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
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 ^^^^ 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