Re: Views versus user-defined functions: formatting, comments, performance, etc.

On Wed, Aug 29, 2012 at 6:15 AM, Merlin Moncure <mmoncure(at)gmail(dot)com> 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