X-Original-To: pgsql-interfaces-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 5C3DBD80D3
	for <pgsql-interfaces-postgresql.org@localhost.postgresql.org>;
	Tue, 20 Sep 2005 19:03:45 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 67776-05
	for <pgsql-interfaces-postgresql.org@localhost.postgresql.org>;
	Tue, 20 Sep 2005 22:03:37 +0000 (GMT)
Received: from tigger.fuhr.org (tigger.fuhr.org [63.214.45.158])
	by svr1.postgresql.org (Postfix) with ESMTP id E8E6ED9442
	for <pgsql-interfaces@postgresql.org>;
	Tue, 20 Sep 2005 19:03:35 -0300 (ADT)
Received: from winnie.fuhr.org (winnie.fuhr.org [10.1.0.1])
	by tigger.fuhr.org (8.13.3/8.13.3) with ESMTP id j8KM3O6c054601
	(version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=OK);
	Tue, 20 Sep 2005 16:03:28 -0600 (MDT)
	(envelope-from mfuhr@winnie.fuhr.org)
Received: from winnie.fuhr.org (localhost [127.0.0.1])
	by winnie.fuhr.org (8.13.3/8.13.3) with ESMTP id j8KM3NQc066924;
	Tue, 20 Sep 2005 16:03:23 -0600 (MDT)
	(envelope-from mfuhr@winnie.fuhr.org)
Received: (from mfuhr@localhost)
	by winnie.fuhr.org (8.13.3/8.13.3/Submit) id j8KM3NWB066923;
	Tue, 20 Sep 2005 16:03:23 -0600 (MDT) (envelope-from mfuhr)
Date: Tue, 20 Sep 2005 16:03:23 -0600
From: Michael Fuhr <mike@fuhr.org>
To: Scott Cain <cain@cshl.edu>
Cc: pgsql-interfaces@postgresql.org
Subject: Re: finding indexed functions from pg_index
Message-ID: <20050920220323.GA66861@winnie.fuhr.org>
References: <Pine.GSO.4.05.10509201409020.23347-100000@phage.cshl.edu>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <Pine.GSO.4.05.10509201409020.23347-100000@phage.cshl.edu>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.005 required=5 tests=[AWL=0.005]
X-Spam-Level: 
X-Archive-Number: 200509/30
X-Sequence-Number: 5753

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