Обсуждение: PostgreSQL Inheritance and column mapping

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"
withinvoice_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

On 10/2/2014 4:51 AM, Achilleas Mantzios wrote:
>
>
> 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

Achilleas Mantzios <achill@matrix.gatewaynet.com> writes:
> 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

On 10/2/14, 9:00 AM, Tom Lane wrote:
> Achilleas Mantzios <achill@matrix.gatewaynet.com> writes:
>> 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.

On 03/10/2014 05:54, Jim Nasby wrote:
> On 10/2/14, 9:00 AM, Tom Lane wrote:
>> Achilleas Mantzios <achill@matrix.gatewaynet.com> writes:
>>> 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.

That is a brilliant idea, thank you!
One problem is that the tables are a part of a 100-node replication system base on a heavily hacked
version of DBMirror, over a non-TCPIP Satellite network. That would require rewriting rules
and deploying this across the remote nodes.
I would be afraid to run the ALTER TABLE ... RENAME TO command in this system.
So, we could just bite the bullet and get our team rewrite all programs.

--
Achilleas Mantzios
Head of IT DEV
IT DEPT
Dynacom Tankers Mgmt