Обсуждение: Composite type
Hello guys! I have found a example in Oracle and I am trying to do it in Postgre. 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? Thank you in advance! George Ant -- View this message in context: http://postgresql.1045698.n5.nabble.com/Composite-type-tp5788860.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
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
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
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
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.
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.