Re: pg_get_INDEXdef - opclass
Bah, I mean pg_get_indexdef of course :-)
Show quoted text
-----Original Message-----
From: pgsql-hackers-owner@postgresql.org
[mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of Dave Page
Sent: 12 June 2006 12:12
To: pgsql-hackers@postgresql.org
Subject: [HACKERS] pg_get_viewdef - opclassFollowing a pgAdmin bug report, I noticed that pg_get_viewdef doesn't
return the opclass when called for a specific column (in 8.1
at least) -
for example, for the index:CREATE UNIQUE INDEX bar_pattern_idx2 ON foofoo USING btree
(lower((bar)::text) bpchar_pattern_ops, bar2)A query on column 1 returns:
lower((bar)::text)
It seems to me that the opclass should be included as well.
Regards, Dave.
---------------------------(end of
broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org
so that your
message can get through to the mailing list cleanly
"Dave Page" <dpage@vale-housing.co.uk> writes:
Following a pgAdmin bug report, I noticed that pg_get_viewdef doesn't
return the opclass when called for a specific column (in 8.1
at least) -
Bah, I mean pg_get_indexdef of course :-)
This is intentional --- whoever asked for the per-column variant of
the indexdef function wanted it that way. It seems reasonable to me:
you can extract the opclass name with a simple join against
pg_index.indclass[N], when you need it, whereas if the function
sometimes included an opclass name that would tend to break apps that
weren't expecting it. OTOH, getting the expression for an expression
column would be seriously painful if there were no function to do it.
regards, tom lane
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: 12 June 2006 18:32
To: Dave Page
Cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] pg_get_INDEXdef - opclass"Dave Page" <dpage@vale-housing.co.uk> writes:
Following a pgAdmin bug report, I noticed that
pg_get_viewdef doesn't
return the opclass when called for a specific column (in 8.1
at least) -Bah, I mean pg_get_indexdef of course :-)
This is intentional --- whoever asked for the per-column variant of
the indexdef function wanted it that way. It seems reasonable to me:
you can extract the opclass name with a simple join against
pg_index.indclass[N], when you need it, whereas if the function
sometimes included an opclass name that would tend to break apps that
weren't expecting it. OTOH, getting the expression for an expression
column would be seriously painful if there were no function to do it.
Right, but how can I conditionally join with pg_opclass based on whether
or not the opclass specified for the column is the default for that
type? For a base type index column I can probably do that with some SQL,
but what about cases where the index column is an expression?
Regards, Dave.
Import Notes
Resolved by subject fallback
"Dave Page" <dpage@vale-housing.co.uk> writes:
Right, but how can I conditionally join with pg_opclass based on whether
or not the opclass specified for the column is the default for that
type?
Check pg_opclass.opcdefault and compare pg_opclass.opcintype to the
index column's datatype (which you'd get from its pg_attribute row
... whether it's an expression is irrelevant).
regards, tom lane
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: 13 June 2006 14:42
To: Dave Page
Cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] pg_get_INDEXdef - opclass"Dave Page" <dpage@vale-housing.co.uk> writes:
Right, but how can I conditionally join with pg_opclass
based on whether
or not the opclass specified for the column is the default for that
type?Check pg_opclass.opcdefault and compare pg_opclass.opcintype to the
index column's datatype (which you'd get from its pg_attribute row
... whether it's an expression is irrelevant).
Ahh right - thanks. I got it into my head that there was no pg_attribute
row for an expression. Must be the sun...
Regards, Dave.
Import Notes
Resolved by subject fallback