Обсуждение: constraints on composite types

This fails on 8.0.3 (syntax error at or near "." at character):

CREATE TYPE ct AS (
  foo INTEGER,
  bar INTEGER
);

CREATE TABLE t1 (
  attr ct,
  CONSTRAINT uq UNIQUE (attr.foo)
);

Should it be possible? From reading
http://www.postgresql.org/docs/current/static/rowtypes.html it looks
like almost everything else works.

--
How many Vietnam vets does it take to screw in a light bulb?
You don't know, man.  You don't KNOW.
Cause you weren't THERE.             http://bash.org/?255991

Roman Neuhauser wrote:
> This fails on 8.0.3 (syntax error at or near "." at character):
>
> CREATE TYPE ct AS (
>   foo INTEGER,
>   bar INTEGER
> );
>
> CREATE TABLE t1 (
>   attr ct,
>   CONSTRAINT uq UNIQUE (attr.foo)
> );
>
> Should it be possible? From reading
> http://www.postgresql.org/docs/current/static/rowtypes.html it looks
> like almost everything else works.

You might get somewhere with:

CREATE OR REPLACE FUNCTION testfunc(ct) RETURNS int AS
'SELECT $1.foo;'
LANGUAGE SQL IMMUTABLE;

CREATE UNIQUE INDEX t1_b_uniq ON t1 (testfunc(b));

Seems to work on 8.1beta - haven't tried on version 8, but if the syntax
is accepted I don't see why not.
--
   Richard Huxton
   Archonet Ltd

Richard Huxton <dev@archonet.com> writes:
> You might get somewhere with:

> CREATE OR REPLACE FUNCTION testfunc(ct) RETURNS int AS
> 'SELECT $1.foo;'
> LANGUAGE SQL IMMUTABLE;

> CREATE UNIQUE INDEX t1_b_uniq ON t1 (testfunc(b));

The point is that "attr.foo" is an expression, not a column name, and
the SQL spec allows UNIQUE and PRIMARY KEY only on bare column names.
I don't believe you need the function -- this should be enough:

CREATE UNIQUE INDEX t1_b_uniq ON t1 ((attr.foo));

            regards, tom lane

Tom Lane wrote:
> Richard Huxton <dev@archonet.com> writes:
>
>>You might get somewhere with:
>
>
>>CREATE OR REPLACE FUNCTION testfunc(ct) RETURNS int AS
>>'SELECT $1.foo;'
>>LANGUAGE SQL IMMUTABLE;
>
>
>>CREATE UNIQUE INDEX t1_b_uniq ON t1 (testfunc(b));
>
>
> The point is that "attr.foo" is an expression, not a column name, and
> the SQL spec allows UNIQUE and PRIMARY KEY only on bare column names.
> I don't believe you need the function -- this should be enough:
>
> CREATE UNIQUE INDEX t1_b_uniq ON t1 ((attr.foo));

I got: Relation "attr" does not exist (on 8.1 beta)


--
   Richard Huxton
   Archonet Ltd

On Fri, Sep 09, 2005 at 10:39:58AM -0400, Tom Lane wrote:
> I don't believe you need the function -- this should be enough:
>
> CREATE UNIQUE INDEX t1_b_uniq ON t1 ((attr.foo));

I was expecting that to work too, but it doesn't:

ERROR:  relation "attr" does not exist

--
Michael Fuhr

# mike@fuhr.org / 2005-09-09 09:10:30 -0600:
> On Fri, Sep 09, 2005 at 10:39:58AM -0400, Tom Lane wrote:
> > I don't believe you need the function -- this should be enough:
> >
> > CREATE UNIQUE INDEX t1_b_uniq ON t1 ((attr.foo));
>
> I was expecting that to work too, but it doesn't:
>
> ERROR:  relation "attr" does not exist

    The manual says something to the effect of (table.col).subcol,
    I'll need that schema-qualified as well, IOW (schema.table.col).subcol

--
How many Vietnam vets does it take to screw in a light bulb?
You don't know, man.  You don't KNOW.
Cause you weren't THERE.             http://bash.org/?255991

Richard Huxton <dev@archonet.com> writes:
> Tom Lane wrote:
>> I don't believe you need the function -- this should be enough:
>>
>> CREATE UNIQUE INDEX t1_b_uniq ON t1 ((attr.foo));

> I got: Relation "attr" does not exist (on 8.1 beta)

Sorry, make that

CREATE UNIQUE INDEX t1_b_uniq ON t1 (((attr).foo));

            regards, tom lane

On Fri, Sep 09, 2005 at 05:20:58PM +0200, Roman Neuhauser wrote:
> # mike@fuhr.org / 2005-09-09 09:10:30 -0600:
> > On Fri, Sep 09, 2005 at 10:39:58AM -0400, Tom Lane wrote:
> > > I don't believe you need the function -- this should be enough:
> > >
> > > CREATE UNIQUE INDEX t1_b_uniq ON t1 ((attr.foo));
> >
> > I was expecting that to work too, but it doesn't:
> >
> > ERROR:  relation "attr" does not exist
>
>     The manual says something to the effect of (table.col).subcol,
>     I'll need that schema-qualified as well, IOW (schema.table.col).subcol

This works:

CREATE UNIQUE INDEX t1_b_uniq ON t1 (((attr).foo));

--
Michael Fuhr