Does postgreSQL have the ability to index a view? Or index a computed
column such as an aggregate?

Thanks

Search Discussions

  • Sean Davis at Feb 15, 2006 at 3:48 pm
    You simply index the underlying table.

    Sean


    On 2/15/06 10:35 AM, "Ketema Harris" wrote:

    Does postgreSQL have the ability to index a view? Or index a computed column
    such as an aggregate?
    Thanks
  • Srinivas Iyyer at Feb 15, 2006 at 5:41 pm
    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
  • Sean Davis at Feb 15, 2006 at 5:47 pm

    On 2/15/06 12:41 PM, "Srinivas Iyyer" 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
  • Srinivas Iyyer at Feb 15, 2006 at 6:15 pm
    Thank you very much Sean !
    I learned a great deal today :-)

    Thanks again.
    Srini

    --- Sean Davis wrote:


    On 2/15/06 12:41 PM, "Srinivas Iyyer"
    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedFeb 15, '06 at 3:35p
activeFeb 15, '06 at 6:15p
posts5
users3
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase