Thread: finding indexed functions from pg_index

Hi,

I am trying to modify SQL::Translator, a perl module for translating
various dialects of SQL ddl (table, index creates, constraints, etc) (and 
it does other cool things too, check it out!), to talk directly to a
PostgreSQL database to determine what tables it has, what columns they
have, indexes, constraints, etc.  I have mostly figured out what I need
from pg_namespace, pg_class, pg_index and pg_attribute.

What I haven't figure out is this: how does one determine what the
declarative statement is for a functional index that is obtained from
pg_index.indexprs?  What I would like to be able to do is be able to
recreate the SQL that defined that index.  Is that represented somewhere
in another system table?

Thanks,
Scott


----------------------------------------------------------------------
Scott Cain, Ph. D.                      cain@cshl.org
GMOD Coordinator, http://www.gmod.org/             (216)392-3087
----------------------------------------------------------------------

Scott Cain <cain@cshl.edu> writes:
> What I haven't figure out is this: how does one determine what the
> declarative statement is for a functional index that is obtained from
> pg_index.indexprs?  What I would like to be able to do is be able to
> recreate the SQL that defined that index.  Is that represented somewhere
> in another system table?

Use pg_get_indexdef().
        regards, tom lane

On Tue, Sep 20, 2005 at 02:15:36PM -0400, Scott Cain wrote:
> What I haven't figure out is this: how does one determine what the
> declarative statement is for a functional index that is obtained from
> pg_index.indexprs?  What I would like to be able to do is be able to
> recreate the SQL that defined that index.

See the pg_get_indexdef() function.

http://www.postgresql.org/docs/8.0/interactive/functions-info.html

When trying to figure out how to query the system catalogs, it can
be useful to study the queries that psql runs for \d commands.  Run
"psql -E" or execute "\set ECHO_HIDDEN" to see those queries.

-- 
Michael Fuhr