I am trying to make postgres tables work like an object hierarchy. As an example I have done this.
drop table if exists os.linux cascade;
create table os.linux
(
script_name character varying(255) NOT NULL,
script_body text,
CONSTRAINT os_linux_pkey PRIMARY KEY (script_name)
);
drop table if exists os.red_hat;
CREATE TABLE os.red_hat
(
CONSTRAINT os_red_hat_pkey PRIMARY KEY (script_name)
)INHERITS (os.linux);
drop table if exists os.debian;
CREATE TABLE os.debian
(
CONSTRAINT os_debian_pkey PRIMARY KEY (script_name)
)INHERITS (os.linux);
insert into os.linux(script_name, script_body) VALUES ('package', 'tgz' );
insert into os.linux(script_name, script_body) VALUES ('awk', 'awk' );
insert into os.debian(script_name, script_body) values( 'package', 'apt');
insert into os.red_hat(script_name, script_body) values( 'package', 'yum');
When I do SELECT * from os.debian I would like to get two records one where the package is 'apt' and one where the awk is 'awk'.
So the package row is overridden in the child but it inherits the parent row.
Is there a way to do this?
Ideally I would like to have a deeper hierarchy like nix -> linux -> debian -> ubuntu -> ubuntu_16_04
so that when I select from ubuntu_16_04 I get all the rows from all the parent tables but properly overridden so they don't union.
Thanks.