pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Inherited tables and new fields 2+ messages / 2 participants [nested] [flat]
* Inherited tables and new fields @ 2004-07-20 14:36 Jeff Boes <jboes@qtm.net> 2004-07-21 07:11 ` Re: Inherited tables and new fields Oliver Elphick <olly@lfix.co.uk> 0 siblings, 1 reply; 2+ messages in thread From: Jeff Boes @ 2004-07-20 14:36 UTC (permalink / raw) To: pgsql-sql This feels like a flaw in the way inherited tables work. I have a "template" table used to create other tables (but not by inheritance; instead the "daughter" tables are created via create table draft_00123 as select * from draft_template where false; This is done for somewhat historical reasons, because we weren't sure at the time if we were going to stay with Pg and so we didn't use every Pg-specific feature in the books. Of course, we regret that ... Now we have a function that spans all the daughter tables. That is, you can do select * from fn_all_drafts() ... and get rows from each table. Of course, had we used table inheritance, we'd do something like ... select * from draft_template ... but it wouldn't do exactly what we are doing now: that is, fn_all_drafts() returns not only the contents of every row in the tables draft_XXXXX, but also an extra column indicating which table that row came from. create table all_drafts (editor_id integer) inherits draft_template; What frustrates me from time to time is that if "draft_template" is altered to add a new column, then the function breaks because the new column appears in "all_drafts" as *following* editor_id. The column order messes up the code in the function, because it's expecting all_drafts to look like draft_template, with editor_id added at the end. Is this a mis-feature? -- (Posted from an account used as a SPAM dump. If you really want to get in touch with me, dump the 'jboes' and substitute 'mur'.) ________ Jeffery Boes <>< jboes@qtm.net ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Inherited tables and new fields 2004-07-20 14:36 Inherited tables and new fields Jeff Boes <jboes@qtm.net> @ 2004-07-21 07:11 ` Oliver Elphick <olly@lfix.co.uk> 0 siblings, 0 replies; 2+ messages in thread From: Oliver Elphick @ 2004-07-21 07:11 UTC (permalink / raw) To: Jeff Boes <jboes@qtm.net>; +Cc: pgsql-sql On Tue, 2004-07-20 at 15:36, Jeff Boes wrote: ... > Of course, had we used table inheritance, we'd do something like ... > > select * from draft_template ... > > but it wouldn't do exactly what we are doing now: that is, > fn_all_drafts() returns not only the contents of every row in the tables > draft_XXXXX, but also an extra column indicating which table that row > came from. You can do that with an inheritance hierarchy like this: select tableoid::regclass as tablename, * from my_table; > create table all_drafts (editor_id integer) inherits draft_template; > > What frustrates me from time to time is that if "draft_template" is > altered to add a new column, then the function breaks because the new > column appears in "all_drafts" as *following* editor_id. The column > order messes up the code in the function, because it's expecting > all_drafts to look like draft_template, with editor_id added at the end. > > Is this a mis-feature? New columns get added at the end of each table; that is standard. -- Oliver Elphick olly@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA ======================================== "Greater love hath no man than this, that a man lay down his life for his friends." John 15:13 ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2004-07-21 07:11 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2004-07-20 14:36 Inherited tables and new fields Jeff Boes <jboes@qtm.net> 2004-07-21 07:11 ` Oliver Elphick <olly@lfix.co.uk>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox