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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 1/26/2014 9:02 AM, antono124 wrote:

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:

does this table1 have a primary key? if so, referencing the PK allows
you to fetch the whole row via a join. if this table doesn't have a
primary key, um, why not??

--
john r pierce 37N 122W
somewhere on the middle of the left coast

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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?

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

As a note, the following also works:

CREATE TYPE foo AS (bar text, baz int);
CREATE TABLE table_of_foo OF foo (primary key(bar));

The one thing that doesn't work is the REF syntax and the operators that go
along with that. However, you could come up with dereferencing functions
and operators oneself.

Personally in this case I would probably use inheritance instead for the
simple reason that tables can inherit but complex types cannot. You can
use CHECK(false) NOINHERIT to effectively relegate a table to a type if you
have a sufficiently recent version of PostgreSQL.

Best Wishes,
Chris Travers

Guys thank you for your replies, you really helped me a lot!!!

I haven't use Postgres before and its the first time I am "playing" with
composite types, so sorry if I hurted your eyes with my question!

Kevin I followed your suggestion and seems to work fine.I think it is what
I was looking for.

I am actually trying to transform a RELATIONAL database to
OBJECT-RELATIONAL. Can anybody provide me with any helpful link or suggest
me some "must do" steps? (maybe I need to start a new post for this).

Thank you again,
George Ant

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Composite-type-tp5789103p5789584.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Wed, Jan 29, 2014 at 1:44 PM, George Ant <g.antonopoulos000@gmail.com>wrote:

Guys thank you for your replies, you really helped me a lot!!!

I haven't use Postgres before and its the first time I am "playing" with
composite types, so sorry if I hurted your eyes with my question!

Kevin I followed your suggestion and seems to work fine.I think it is what
I was looking for.

I am actually trying to transform a RELATIONAL database to
OBJECT-RELATIONAL. Can anybody provide me with any helpful link or suggest
me some "must do" steps? (maybe I need to start a new post for this).

Huge question. You might find my blog (http://ledgersmbdev.blogspot.com)
to be of interest.

Here's the very brief overview.

1. Every good object-relational design is also good relational design to
at least a certain extent. With a few exceptions (which, if they don't
strike you as necessary, they probably aren't exceptions), you don't want
to sacrifice relational design in the process. Usually if you are going to
sacrifice relational design (up to and including 1st Normal Form) you
should have a very good reason to do so. Sacrificing relational design
always has costs. Don't do it willy nilly.

2. Table inheritance in PostgreSQL works better when you think less in
terms of inheritance and more in terms of mix-ins. This is because certain
things, like keys, don't inherit the way you'd expect in an inheritance
approach. Table inheritance in PostgreSQL is a wonderful tool for
maintaining consistent interfaces across necessarily different tables (for
example because foreign keys are different). However it doesn't work
flawlessly for things like managing subtypes. Use joins (and maybe views)
for that.

3. You can treat tables as classes, and build methods on those. You can
also treat views as classes and build methods on those. This allows you to
encapsulate your data in other ways. Complex types can be classes too.

I usually think about object-relational design not in terms of the table
layout but in terms of certain styles of writing functions and ways of
interacting with the underlying data. In other words, for me it is about
ways of encapsulating logic both above and below SQL so that various kinds
of complex operations are gracefully handled with looser coupling between
the application and the database.

Going very far on this will require writing client libraries. I am working
on some in Perl. I would be happy to collaborate with those in other
languages. However it's best to start small, and experiment with adding a
little bit at a time to a good relational design, and see what it buys you.
In this area, I think, less is quite often more.

Best Wishes,
Chris Travers

Chris, thank you for your time. Your reply was really helpful!

I followed your advice. I didn't sacrifice relational design (I didn't have
this intention) and I used joins/views.

Kind Regards,
George Antonopoulos

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Composite-type-tp5789103p5790242.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general