From: Tom Lane <tgl@sss.pgh.pa.us>
To: Nikhil Ingale <niks.bgm@gmail.com>
cc: pgsql-admin@lists.postgresql.org
Subject: Re: Find out the indexed columns
In-reply-to: <334991.1683552353@sss.pgh.pa.us>
References: 
 <CALXkTpw1Sf_TPLjJxWc-bg--21A8wTb126G=zeGqRGh9GgT3Ww@mail.gmail.com>
 <334991.1683552353@sss.pgh.pa.us>
Comments: In-reply-to Tom Lane <tgl@sss.pgh.pa.us>
	message dated "Mon, 08 May 2023 09:25:53 -0400"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <337745.1683553135.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Mon, 08 May 2023 09:38:55 -0400
Message-ID: <337746.1683553135@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/337746.1683553135%40sss.pgh.pa.us>
Precedence: bulk

I wrote:
> You could get back the column name(s) by joining to pg_attribute,

Oh, another possible approach is to use pg_describe_object,
which'd be interesting if you also want to handle non-column
dependencies.  For example,

regression=3D# drop table t;
DROP TABLE
regression=3D# create table t(f1 text);
CREATE TABLE
regression=3D# create index ti on t (fipshash(f1));
CREATE INDEX
regression=3D# select * from pg_depend where classid =3D 'pg_class'::regcl=
ass and objid =3D 'ti'::regclass;
 classid | objid | objsubid | refclassid | refobjid | refobjsubid | deptyp=
e =

---------+-------+----------+------------+----------+-------------+-------=
--
    1259 | 48632 |        0 |       1259 |    48627 |           0 | a
    1259 | 48632 |        0 |       1259 |    48627 |           1 | a
    1259 | 48632 |        0 |       1255 |    16501 |           0 | n
(3 rows)

regression=3D# select pg_describe_object(refclassid, refobjid, refobjsubid=
) from pg_depend where classid =3D 'pg_class'::regclass and objid =3D 'ti'=
::regclass;
   pg_describe_object    =

-------------------------
 table t
 column f1 of table t
 function fipshash(text)
(3 rows)

			regards, tom lane