Re: [GENERAL] PostgreSQL Inheritance and column mapping
On 03/10/2014 05:54, Jim Nasby wrote: On 10/2/14, 9:00 AM, Tom Lane wrote: Achilleas Mantzios ach...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 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 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL Inheritance and column mapping
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL Inheritance and column mapping
Achilleas Mantzios ach...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL Inheritance and column mapping
On 10/2/14, 9:00 AM, Tom Lane wrote: Achilleas Mantzios ach...@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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
