On Wed, Aug 29, 2012 at 6:15 AM, Merlin Moncure <mmoncure@xxxxxxxxx> wrote:
Hm, couple points (and yes, this is a common problem):
*) how come you don't have your function depend on the table instead
of the view? this has the neat property of having the function
automatically track added columns to the table.
Plus there are lots of really cool things about function/table dependencies.
*) if that's still a headache from dependency point of view, maybe you
can use composite-type implemented table:
postgres=# create type foo as (a int, b int);
CREATE TYPE
postgres=# create table bar of foo;
CREATE TABLE
That's a useful syntax there.
*) do you really need a factory function to create 'user' -- why not
allow regular inserts?
I don't know about his case but with LedgerSMB our new code all maps inserts into stored procedures. These have a useful property of discoverability although I suppose an insert would too. However if you are going the insert route, it may be good to separate physical from logical storage, which may be where the views and functions come in.
Also showing before where table inheritance can make this a bit better and how to solve your key issue (which again inheritance provides a solution for, properly used).
*) I usually do some variant of this:
create table fruit
(
fruit_id int primary key,
type text,
freshness numeric
);
I would suggest adding fruit.type to the primary key. From here there are all sorts of things you can do and depending on number of types, inheritance can be a net win (see my upcoming post tomorrow on this).
Also create another table:
CREATE TABLE fruit_ref (
fruit_id int,
fruit_type text
);
This is then useful for creating inherited interfaces. More on this below.
create table apple
(
fruit_id int primary key references fruit on delete cascade
deferrable initially deferred,
cyanide_content numeric
);
Change this to:
CREATE TABLE apple (
cyanide_content numeric,
primary key (fruit_id, fruit_type)
check (fruit_type = 'apple'),
foreign key (fruit_id, fruit_type) references fruit (fruit_id, type)
deferrable initially immediate,
) inherits (fruit_ref);
Make similar changes to other tables below....
create table orange
(
fruit_id int primary key references fruit on delete cascade
deferrable initially deferred,
vitamin_c_content numeric
);
Now, you can also do as follows:
CREATE OR REPLACE FUNCTION fruit(fruit_ref) returns fruit
language sql
as $$
select * from fruit where fruit_id = $1.fruit_id; $$;
This way you technically can do something like:
select (a.fruit).* from apple a;
although that will essentially force a nested loop join, and probably even worse than that so keep those to the minimum.
But now we can decide on how to enforce the type constraint. The simplest way if you don't have too many subtypes is probably to add the following columns to fruit:
ALTER TABLE fruit ADD apple_id int;
ALTER TABLE fruit ADD FOREIGN KEY (apple_id, type)
REFERENCES apple (fruit_id, type)
DEFERRABLE INITIALLY DEFERRED;
And then do the same for orange etc. you can then:
ALTER TABLE fruit ADD CHECK ((type = 'apple' and apple_id IS NOT NULL) OR (type = 'orange' AND orange_id IS NOT NULL)
etc....
;
This way you only get a fairly complicated set of type constraints, and the interface to fruit is in fact guaranteed to be unique and enforced.
The problem though is that if you have a very large number of subtypes, this becomes sufficiently complex that constraint triggers against the fruit_ref inheritance tree may become a net win over individual foreign keys. Inheritance also simplifies adding sub-types because you inherit the subtype interface and then work from there for joins and doesn't have to be used to enforce keys to get there however.
This seems to work well especially if you have a lot of
specializations of the 'base type' and you can season deletions to
taste with appropriate RI triggers if you want. An alternate way to
do it is to include fruit.type in the primary key, forcing the
dependent fruit back to the proper record though. My main gripe about
it is that it there's no way to make sure that a 'fruit' points at the
proper dependent table based on type with a pure constraint.
deferred foreign key constraints can do that if you add one key to fruit for each base table.
Yet another way of doing this is to simple hstore the extended
properties into the base table so that everything is stuffed in one
table -- that discards all type safety though. I'm curious about what
others have come up with in terms of solving this problem.
Aside: a better way of doing this is the problem that table
inheritance was trying to solve (and didn't).
Inheritance certainly hasn't become hassle-free in terms of this sort of modelling and in fact is usually on the balance not a net gain. It may be helpful in defining interfaces however, and as we get more and better tools (excited about NO INHERIT constraints in 9.2) it may yet develop into a more generally useful tool of this sort. What it can do here however is define an interface for a join. You could probably inherit fruit from the join class to but if you do that, if you ever have to do a constraint trigger, against the tree, you will run into problems. It's better to have the two sides of the foreign key not in the same inheritance subtree.
Best Wishes,
Chris Travers
