Re: Composite type

antono124 <g(dot)antonopoulos000(at)gmail(dot)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?

First, it's PostgreSQL or Postgres for short; not Postgre.

It's pretty hard to see what you want here.  You might be looking
for something like this:

CREATE TYPE type1 AS (
  k1  bigint,
  v1  text
  );
CREATE TYPE type2 AS (
  k2  bigint,
  v2  text,
  k1  bigint
  );
CREATE TABLE table1 (
  LIKE type1,
  PRIMARY KEY (k1)
  );
CREATE TABLE table2 (
  LIKE type2,
  PRIMARY KEY (k2),
  FOREIGN KEY (k1) REFERENCES table1
  );

To reference data from both together you might want a view:

CREATE VIEW view1 AS
  SELECT * FROM table1 JOIN table2 USING (k1);

If that's not quite what you're after you might want to look at the
INHERITS clause of CREATE TABLE.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company