Обсуждение: 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