Re: [GENERAL] constraints on composite types

2005-09-09 Thread Richard Huxton 

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

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] constraints on composite types

2005-09-09 Thread Tom Lane 
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

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [GENERAL] constraints on composite types

2005-09-09 Thread Richard Huxton 

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

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] constraints on composite types

2005-09-09 Thread Michael Fuhr 
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

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] constraints on composite types

2005-09-09 Thread Roman Neuhauser 
# [EMAIL PROTECTED] / 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

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] constraints on composite types

2005-09-09 Thread Tom Lane 
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

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] constraints on composite types

2005-09-09 Thread Michael Fuhr 
On Fri, Sep 09, 2005 at 05:20:58PM +0200, Roman Neuhauser wrote:
 # [EMAIL PROTECTED] / 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

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org