Inherited tables and new fields

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

Re: Inherited tables and new fields

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


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings