Обсуждение: Accessing composite type columns in indexes
Michael Fuhr's example of using composite types for date intervals/
ranges/periods prompted me to explore this a little further. While
doing so, it appears that one can't directly access the columns of a
composite type when creating an index, i.e., neither UNIQUE (foo.bar)
nor UNIQUE ((foo).bar) work. I was able to create indexes including
composite columns by creating functions that returned values from a
composite type parameter.
Is this expected?
Here are the details:
begin;
BEGIN
-- closed-open date interval [from_date, to_date)
create type date_co_interval as
(
from_date date
, to_date date
);
CREATE TYPE
create function co_begin(date_co_interval) returns date
strict
immutable
security definer
language plpgsql as '
declare
i alias for $1;
begin
return i.from_date;
end;
';
CREATE FUNCTION
-- convenience function
create function prior(date) returns date
strict
immutable
security definer
language plpgsql as '
declare
p alias for $1;
begin
return p - 1;
end;
';
CREATE FUNCTION
create function co_end(date_co_interval) returns date
strict
immutable
security definer
language plpgsql as '
declare
i alias for $1;
begin
return prior(i.to_date);
end;
';
CREATE FUNCTION
savepoint composite_dot;
SAVEPOINT
create table employment_history
(
company text not null
, during date_co_interval not null
, unique (company, during.from_date, during.to_date)
);
ERROR: syntax error at or near "." at character 129
LINE 5: , unique (company, during.from_date, during.to_date)
^
rollback to savepoint composite_dot;
ROLLBACK
savepoint composite_parens;
SAVEPOINT
create table employment_history
(
company text not null
, during date_co_interval
, unique (company, (during).from_date, (during).to_date)
);
ERROR: syntax error at or near "(" at character 114
LINE 5: , unique (company, (during).from_date, (during).to_date)
^
rollback to savepoint composite_parens;
ROLLBACK
savepoint function_on_composite;
SAVEPOINT
create table employment_history
(
company text not null
, during date_co_interval not null
, unique (company, co_begin(during), co_end(during))
);
ERROR: syntax error at or near "(" at character 131
LINE 5: , unique (company, co_begin(during), co_end(during))
^
rollback to savepoint function_on_composite;
ROLLBACK
savepoint parens_function_on_composite;
SAVEPOINT
create table employment_history
(
company text not null
, during date_co_interval not null
, unique (company, (co_begin(during)), (co_end(during)))
);
ERROR: syntax error at or near "(" at character 123
LINE 5: , unique (company, (co_begin(during)), (co_end(during)))
^
rollback to savepoint parens_function_on_composite;
ROLLBACK
create table employment_history
(
company text not null
, during date_co_interval not null
);
CREATE TABLE
savepoint composite_idx;
SAVEPOINT
create unique index employment_history_pkey_idx
on employment_history (company, during.from_date, during.to_date);
ERROR: syntax error at or near "," at character 98
LINE 2: on employment_history (company, during.from_date, during.to_...
^
rollback to savepoint composite_idx;
ROLLBACK
savepoint composite_parens_idx;
SAVEPOINT
create unique index employment_history_pkey_idx
on employment_history (company, (during).from_date, (during).to_date);
ERROR: syntax error at or near "." at character 89
LINE 2: on employment_history (company, (during).from_date, (during)...
^
rollback to savepoint composite_parens_idx;
ROLLBACK
create unique index employment_history_pkey_idx
on employment_history (company, co_begin(during), co_end(during));
CREATE INDEX
rollback;
ROLLBACK
select version();
version
------------------------------------------------------------------------
----------------------------------------------------------------------
PostgreSQL 8.1.3 on powerpc-apple-darwin8.4.0, compiled by GCC
powerpc-apple-darwin8-gcc-4.0.1 (GCC) 4.0.1 (Apple Computer, Inc.
build 5247)
(1 row)
Michael Glaesemann
grzm myrealbox com
Michael Glaesemann <grzm@myrealbox.com> writes:
> ... it appears that one can't directly access the columns of a
> composite type when creating an index, i.e., neither UNIQUE (foo.bar)
> nor UNIQUE ((foo).bar) work.
You need both, ie something like
create table foo (bar date_co_interval);
create unique index fooi on foo (((bar).from_date));
The outer set of parens is required for any index expression. Basically
that's to fix a grammar conflict against the possible presence of an
index opclass, that is given
create index fooi on foo (x ! y)
is that an infix operator expression "x ! y", or a postfix operator
expression "x !" followed by an opclass name?
The inner set of parens is because "a.b" is always interpreted as a
table and column name. To refer to a column, and then qualify it with
a composite-type field, we require you to write "(b).c" or "(a.b).c".
It'd be legal to write the same index as
create unique index fooi on foo (((foo.bar).from_date));
Make sense now?
regards, tom lane
On Sat, Mar 04, 2006 at 12:21:38PM +0900, Michael Glaesemann wrote: > create unique index employment_history_pkey_idx > on employment_history (company, (during).from_date, (during).to_date); > ERROR: syntax error at or near "." at character 89 > LINE 2: on employment_history (company, (during).from_date, (during)... > ^ This works: create unique index employment_history_pkey_idx on employment_history (company, ((during).from_date), ((during).to_date)); -- Michael Fuhr
On Mar 4, 2006, at 13:34 , Michael Fuhr wrote: > On Sat, Mar 04, 2006 at 12:21:38PM +0900, Michael Glaesemann wrote: >> create unique index employment_history_pkey_idx >> on employment_history (company, (during).from_date, >> (during).to_date); >> ERROR: syntax error at or near "." at character 89 >> LINE 2: on employment_history (company, (during).from_date, >> (during)... >> ^ > > This works: > > create unique index employment_history_pkey_idx > on employment_history (company, ((during).from_date), > ((during).to_date)); Thanks, Tom and Michael. I thought I had been aggressive enough in applying parentheses. On Mar 4, 2006, at 13:31 , Tom Lane wrote: > Make sense now? Yep! Michael Glaesemann grzm myrealbox com
On Mar 4, 2006, at 13:44 , Michael Glaesemann wrote:
> On Mar 4, 2006, at 13:31 , Tom Lane wrote:
>> Make sense now?
>
> Yep!
Except, why doesn't it work in the CREATE TABLE statement? One needs
to add the UNIQUE index as a separate command. For example,
-- doesn't work
create table foo
(
foo date_co_interval
, unique (((foo).from_date), ((foo).to_date))
);
-- fails (as expected)
create table foo
(
foo date_co_interval
, unique (((foo.foo).from_date), ((foo.foo).to_date))
);
-- works
create table foo (foo date_co_interval);
create unique index foo_idx on foo (((foo).from_date), ((foo).to_date));
Is this also excluded because of some parser ambiguity?
Michael Glaesemann
grzm myrealbox com
Michael Glaesemann <grzm@myrealbox.com> writes: > Except, why doesn't it work in the CREATE TABLE statement? The UNIQUE/PRIMARY KEY syntax only allows bare column names, per the SQL standard. While there's not any technical reason why we couldn't extend that syntax, there's a pretty large fear factor involved: we could find ourselves behind the spec-compliance eight-ball if the SQL committee exercises their prerogative to extend the syntax in some other direction. CREATE INDEX is entirely outside the spec (as the whole concept of indexes is outside the spec) and so we can pretty much do what we please within that statement. This is exactly the same reason why we don't support opclass names in UNIQUE/PRIMARY KEY, as has been suggested several times recently, eg http://archives.postgresql.org/pgsql-hackers/2006-03/msg00163.php regards, tom lane
On Mar 4, 2006, at 14:17 , Tom Lane wrote: > The UNIQUE/PRIMARY KEY syntax only allows bare column names, per > the SQL > standard. While there's not any technical reason why we couldn't > extend > that syntax, there's a pretty large fear factor involved: we could > find > ourselves behind the spec-compliance eight-ball if the SQL committee > exercises their prerogative to extend the syntax in some other > direction. Cool. Thanks for the explanation. Michael Glaesemann grzm myrealbox com