PostgreSQL Inheritance and column mapping

[Achilleas Mantzios]

Hi,

Was there ever any discussion.thought about being able to follow a non-strict by name
column mapping between inherited tables and father tables?

If for instance someone wants to build an hierarchy on a schema of tables being defined
in an established production system, designed with no inheritance in mind, is there any workaround
or a way to map the non-common column names, but semantically similar?

E.g. Imagine we have the following tables in a legacy non-OO system :
create table receipt_document(id serial primary key,doc_no text, date_entered date);
create table invoice_document(id serial primary key,invoice_no text, date_entered date, date_due date);
.....
.....
Then at a (much) later date we decide we want to have an overview of all the documents having to do
with purchasing, or even add generic purchase documents for which no special application or structure exists
(as of yet)
We create the new generic table :
create table purchase_document(id serial primary key,doc_no text, date_entered date);
And then make this the father table to the two tables with the detailed data :

test=# alter table receipt_document INHERIT purchase_document ;
-- that works

test=# alter table invoice_document INHERIT purchase_document ;
ERROR: child table is missing column "doc_no"

Here the problem is that invoice_document lacks col doc_no, which semantically has the same meaning as invoice_no.

One work around would be to rename the col and massively replace all occurrences of this in the applications.
However i am just wondering if it would be a good idea to extend the way PgSQL inheritance works and
have a mapping between columns as well. e.g. somehow denote that purchase_document.doc_no should be merged and "mapped" with invoice_document.invoice_no.

After all, generally speaking invoices have invoice_no's while general docs have doc_no's , right?
So I think, the above scenario could be indeed be found a lot of times in systems designed with no OO in mind.
--
Achilleas Mantzios
Head of IT DEV
IT DEPT
Dynacom Tankers Mgmt

[John R Pierce]

Post by Achilleas Mantzios
After all, generally speaking invoices have invoice_no's while general
docs have doc_no's , right?
So I think, the above scenario could be indeed be found a lot of times
in systems designed with no OO in mind.

database relations are not 'objects'.
--
john r pierce 37N 122W
somewhere on the middle of the left coast

[Tom Lane]

Post by Achilleas Mantzios
Was there ever any discussion.thought about being able to follow a non-strict by name
column mapping between inherited tables and father tables?

No. You could use a view with UNION ALL perhaps.

(The subtext here is that past discussion of inheritance has generally
focused on *eliminating* differences between parent and child tables, so
as to make query planning and execution faster/simpler. That is, when the
discussion doesn't consist of somebody wanting to get rid of inheritance
altogether. I think the enthusiasm for supporting even-less-compatible
child tables will be not distinguishable from zero.)

regards, tom lane

[Jim Nasby]

Post by Tom Lane

Post by Achilleas Mantzios
Was there ever any discussion.thought about being able to follow a non-strict by name
column mapping between inherited tables and father tables?

No. You could use a view with UNION ALL perhaps.

FWIW, I've had some less than stellar results with that (admittedly, back on 8.4).

The other thing you could do is something like:

ALTER TABLE invoice_document RENAME TO invoice_document_raw;
ALTER TABLE invoice_document_raw RENAME invoice_no TO doc_no;
CREATE VIEW invoice_document AS
SELECT ...
, doc_no AS invoice_no
, ...
FROM invoice_document_raw
;

If you make that view writable then no one needs to know that you renamed the column in the underlying table.

[Achilleas Mantzios]

This post might be inappropriate. Click to display it.