Re: Composite type

On Mon, Jan 27, 2014 at 2:02 AM, antono124 <g.antonopoulos000@gmail.com> wrote:
> Lets say that we have 2 tables.
> Create Table "table1" Of "type1"
> Create Table "table2" Of "type2"
>
> I want to refer the first table in the second. I want to reference the whole
> table not only one field, so something like that:
>
> CREATE TYPE type2 AS OBJECT (
>   var1   NUMBER,
>   var2    REF type1
>   )
>
> CREATE TABLE table2 OF type2 (
>    PRIMARY KEY (Pk),
>    FOREIGN KEY (fk) REFERENCES table1)
>
> Can i do something like this in Postgre?
It is possible to use multiple column names with defining a foreign key:
=# create table t1 (a int, b text, primary key (a, b));
CREATE TABLE
=# create table t2 (a int, b text, c text, foreign key (a, b)
references t1 (a, b));
CREATE TABLE
=# \d t2
      Table "public.t2"
 Column |  Type   | Modifiers
--------+---------+-----------
 a      | integer |
 b      | text    |
 c      | text    |
Foreign-key constraints:
    "t2_a_fkey" FOREIGN KEY (a, b) REFERENCES t1(a, b)

You could as well use some custom types if you do not want to
reference all the columns...
=# create type ty1 as (a int, b int);
CREATE TYPE
=# create table t1 (c ty1 primary key);
CREATE TABLE
=# create table t2 (d int primary key, e ty1 references t1 (c));
CREATE TABLE
=# \d t2
      Table "public.t2"
 Column |  Type   | Modifiers
--------+---------+-----------
 d      | integer | not null
 e      | ty1     |
Indexes:
    "t2_pkey" PRIMARY KEY, btree (d)
Foreign-key constraints:
    "t2_e_fkey" FOREIGN KEY (e) REFERENCES t1(c)

Regards,
--
Michael