reference to a view

[Joolz]

In my db I have a table type_of_action, fields code varchar, name
varchar, medical boolean. Two other tables refer to this table, one of
them to the medical rows, the other one to the none-medical rows. I
would like to make the reference with a view, like this:

create table type of action_type (
code varchar,
name varchar,
medical boolean
);

create view action_type_medical as select * from action_type
where medical=true;
create view action_type_non_medical as select * from action_type
where medical=false;

create table general_actions (
field1, field2, field_etc,
action_type varchar references action_type_non_medical(code)
);

create table medical_actions (
field1, field2, field_etc,
action_type varchar references action_type_medical(code)
);

But pg refuses this, can't create a reference to a view. I can now
create a trigger plus function that will do the check, but is there a
more elegant way? Thanks!

--
10:35-10:41
Fedora Core release 2 (Tettnang) Linux 2.6.6-1.435

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

[Stephan Szabo]

On Wed, 16 Jun 2004, Joolz wrote:

In my db I have a table type_of_action, fields code varchar, name
varchar, medical boolean. Two other tables refer to this table, one of
them to the medical rows, the other one to the none-medical rows. I
would like to make the reference with a view, like this:

create table type of action_type (
code varchar,
name varchar,
medical boolean
);

create view action_type_medical as select * from action_type
where medical=true;
create view action_type_non_medical as select * from action_type
where medical=false;

create table general_actions (
field1, field2, field_etc,
action_type varchar references action_type_non_medical(code)
);

create table medical_actions (
field1, field2, field_etc,
action_type varchar references action_type_medical(code)
);

But pg refuses this, can't create a reference to a view. I can now
create a trigger plus function that will do the check, but is there a
more elegant way? Thanks!

Not really right now. Note, that to do foreign keys properly you need
triggers on action_type as well and those triggers need to take into
account the view conditions to determine what checks to do.

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

http://www.postgresql.org/docs/faqs/FAQ.html

[Stephan Szabo]

On Wed, 16 Jun 2004, Joolz wrote:

In my db I have a table type_of_action, fields code varchar, name
varchar, medical boolean. Two other tables refer to this table, one of
them to the medical rows, the other one to the none-medical rows. I
would like to make the reference with a view, like this:

create table type of action_type (
code varchar,
name varchar,
medical boolean
);

create view action_type_medical as select * from action_type
where medical=true;
create view action_type_non_medical as select * from action_type
where medical=false;

create table general_actions (
field1, field2, field_etc,
action_type varchar references action_type_non_medical(code)
);

create table medical_actions (
field1, field2, field_etc,
action_type varchar references action_type_medical(code)
);

But pg refuses this, can't create a reference to a view. I can now
create a trigger plus function that will do the check, but is there a
more elegant way? Thanks!

Not really right now. Note, that to do foreign keys properly you need
triggers on action_type as well and those triggers need to take into
account the view conditions to determine what checks to do.

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

http://www.postgresql.org/docs/faqs/FAQ.html

[Joolz]

> [Stephan Szabo schreef op 16-06-2004 07:57 -0700]

On Wed, 16 Jun 2004, Joolz wrote:

In my db I have a table type_of_action, fields code varchar, name
varchar, medical boolean. Two other tables refer to this table,
one of them to the medical rows, the other one to the none-medical
rows. I would like to make the reference with a view, like this:

create table type of action_type (
code varchar,
name varchar,
medical boolean
);

create view action_type_medical as select * from action_type
where medical=true;
create view action_type_non_medical as select * from action_type
where medical=false;

create table general_actions (
field1, field2, field_etc,
action_type varchar references action_type_non_medical(code)
);

create table medical_actions (
field1, field2, field_etc,
action_type varchar references action_type_medical(code)
);

But pg refuses this, can't create a reference to a view. I can now
create a trigger plus function that will do the check, but is
there a more elegant way? Thanks!

Not really right now. Note, that to do foreign keys properly you
need triggers on action_type as well and those triggers need to take
into account the view conditions to determine what checks to do.

Thanks for pointing that out. Considering this, it may be a better
idea after all to make two (physically) seperate tables after all.
After all, these are "only" domain tables, and I don't want the
reverse-update trigger to change any of my "real" data.

--
22:40-22:43
Fedora Core release 2 (Tettnang) Linux 2.6.6-1.435

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

http://www.postgresql.org/docs/faqs/FAQ.html

[Joolz]

> [Stephan Szabo schreef op 16-06-2004 07:57 -0700]

On Wed, 16 Jun 2004, Joolz wrote:

In my db I have a table type_of_action, fields code varchar, name
varchar, medical boolean. Two other tables refer to this table,
one of them to the medical rows, the other one to the none-medical
rows. I would like to make the reference with a view, like this:

create table type of action_type (
code varchar,
name varchar,
medical boolean
);

create view action_type_medical as select * from action_type
where medical=true;
create view action_type_non_medical as select * from action_type
where medical=false;

create table general_actions (
field1, field2, field_etc,
action_type varchar references action_type_non_medical(code)
);

create table medical_actions (
field1, field2, field_etc,
action_type varchar references action_type_medical(code)
);

But pg refuses this, can't create a reference to a view. I can now
create a trigger plus function that will do the check, but is
there a more elegant way? Thanks!

Not really right now. Note, that to do foreign keys properly you
need triggers on action_type as well and those triggers need to take
into account the view conditions to determine what checks to do.

Thanks for pointing that out. Considering this, it may be a better
idea after all to make two (physically) seperate tables after all.
After all, these are "only" domain tables, and I don't want the
reverse-update trigger to change any of my "real" data.

--
22:40-22:43
Fedora Core release 2 (Tettnang) Linux 2.6.6-1.435

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

http://www.postgresql.org/docs/faqs/FAQ.html