Обсуждение: How to use int2vector

Hello, all.

In trying to write a utility to drop/restore table indexes (as an aid to
a fast reload of the associated tables), I ran into the definition of
the field pg_index.indkey as an int2vector. I can't see any place in the
doc that says what to do with this type, you can't cast it to any array
type, and the only reference to it in the searchable archives (Thanks,
pgsql.ru!) points one to contrib/intarray -- which doesn't mention
int2vector at all.

How can I deal with this type in plpgsql?

                     Thanks,
                     Rich Cullingford
                     System Detection, Inc.
                     rculling@sysd.com

Rich Cullingford <rculling@sysd.com> writes:
> In trying to write a utility to drop/restore table indexes (as an aid to
> a fast reload of the associated tables), I ran into the definition of
> the field pg_index.indkey as an int2vector.

> How can I deal with this type in plpgsql?

You should be able to index into it (indkey[0], etc), but I cannot think
of any good reason to be looking at the details of the pg_index row for
your purposes.  It is certainly not reasonable to think of writing
plpgsql code to reconstruct 7.4 expressional index definitions from
what's in pg_index :-(.  I would suggest using the pg_get_indexdef
function instead.  Perhaps something like

regression=# select pg_get_indexdef(indexrelid) from pg_index where indrelid = 'tenk1'::regclass;
                      pg_get_indexdef
-----------------------------------------------------------
 CREATE INDEX tenk1_hundred ON tenk1 USING btree (hundred)
 CREATE INDEX tenk1_unique2 ON tenk1 USING btree (unique2)
 CREATE INDEX tenk1_unique1 ON tenk1 USING btree (unique1)
(3 rows)

regression=#

pg_get_indexdef() exists at least as far back as 7.0, and probably
further (pg_dump has relied on it for a long time).

            regards, tom lane