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

2012/8/29 Merlin Moncure <mmoncure(at)gmail(dot)com>

> On Tue, Aug 28, 2012 at 3:25 PM, Dmitriy Igrishin <dmitigr(at)gmail(dot)com>
> wrote:
> > 2012/8/20 Merlin Moncure <mmoncure(at)gmail(dot)com>
> >>
> >> On Sun, Aug 19, 2012 at 8:14 AM, Dmitriy Igrishin <dmitigr(at)gmail(dot)com>
> >> wrote:
> >> >> For various reasons, this often goes the wrong way. Views are often
> >> >> the right way to go. +1 on your comment above -- the right way to do
> >> >> views (and SQL in general) is to organize scripts and to try and
> avoid
> >> >> managing everything through GUI tools. It works.
> >> >
> >> > The drawback of this approach is that in some cases we need a
> >> > factory function(s) (in terms of the OOP) which returns one or a
> >> > set of objects (i.e. the function returns the view type). But since
> >> > the views are not in the dump we are forced to abandon this solution
> >> > and go with workarounds (such as creating extra composite types
> >> > to use as returning values or use the tables).
> >>
> >> Could you elaborate on this?
> >
> > Suppose we've designed a simple class hierarchy (I'll use C++ notation):
> > class User { ... };
> > class Real_user : public User { ... };
> > class Pseudo_user : public User { ... };
> >
> > Suppose we've decided that objects of these classes will be stored
> > in one database table:
> > CREATE TYPE user_type AS ENUM ('real', 'pseudo');
> > CREATE TABLE user (id serial NOT NULL,
> > tp user_type NOT NULL,
> > user_property1 text NOT NULL,
> > user_property2 text NOT NULL,
> > real_user_property1 text NULL,
> > real_user_property2 text NULL,
> > pseudo_user_property1 text NULL);
> >
> > For simple mapping we've creating the (updatable, with rules) views:
> > CREATE VIEW real_user_view
> > AS SELECT * FROM user WHERE tp = 'real';
> >
> > CREATE VIEW pseudo_user_view
> > AS SELECT * FROM user WHERE tp = 'pseudo';
> >
> > CREATE VIEW user_view
> > AS SELECT * FROM real_user_view
> > UNION ALL SELECT * FROM pseudo_user_view;
> >
> > The C++ classes above will operate on these views.
> > Finally, suppose we need a function which gets a Real_user's
> > instance by known identifier (or a key):
> > The C++ function may be defined as:
> > Real_user* real_user(int id);
> >
> > At the same time this function can call PL/pgSQL's function:
> > CREATE FUNCTION real_user(id integer)
> > RETURNS real_user_view ...
> >
> > So, the factory function real_user() is depends on the view. And
> > when the views are not in the dump (stored in the separate place)
> > this is an annoying limitation and we must use some of the
> > workarounds. (Use the table "user" as a return value or create
> > an extra composite type with the same structure as for the
> real_user_view).
>
> 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
>
I always do emphasis on the code style and on the easiness of
maintenance. And I looks at the views as on the classes (aka abstractions).
In many cases I don't want to care how (and where) the data is actually
stored -- in the one table, or in the many tables, or whatever.
AFAIK, the main goal of the views to provide such abstraction.

> automatically track added columns to the table.
>
Agreed, this is a nice feature.

>
> *) 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
> postgres=# create view baz as select * from bar;
> CREATE VIEW
> postgres=# alter type foo add attribute c int cascade;
> ALTER TYPE
> postgres=# \d bar
> Table "public.bar"
> Column | Type | Modifiers
> --------+---------+-----------
> a | integer |
> b | integer |
> c | integer |
> Typed table of type: foo
>
Thanks for the solution! But it seems like a workaround here.

>
> *) do you really need a factory function to create 'user' -- why not
> allow regular inserts?
>
By "factory function" I mean the function which creates an instance
for the client -- i.e. selecting object from the data source :-)

>
>
> *) I usually do some variant of this:
>
> create table fruit
> (
> fruit_id int primary key,
> type text,
> freshness numeric
> );
>
> create table apple
> (
> fruit_id int primary key references fruit on delete cascade
> deferrable initially deferred,
> cyanide_content numeric
> );
>
> create table orange
> (
> fruit_id int primary key references fruit on delete cascade
> deferrable initially deferred,
> vitamin_c_content numeric
> );
>
> create or replace function hs(r anyelement) returns hstore as
> $$
> select hstore($1);
> $$ language sql immutable strict;
>
> create or replace view fruit_ext as
> select f.*,
> coalesce(hs(a), hs(o)) as properties
> from fruit f
> left join apple a using(fruit_id)
> left join orange o using(fruit_id);
>
> insert into fruit values(1, 'apple', 2.0);
> insert into fruit values(2, 'orange', 3.5);
>
> insert into apple values(1, 0.00003);
> insert into orange values(2, 0.012);
>

> 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.
>
Yes, it seems to work in very simple cases. But I would not have to deal
with it
because it's seems to hard to maintaince. And for the project with hundred
classes
it seems to be a nightmare! :-)

>
> 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.
>
Agree, hstore is useful for this cases. But again, it's just an
implementation
detail *how* to store object properties.

>
> Aside: a better way of doing this is the problem that table
> inheritance was trying to solve (and didn't).
>
Btw, is there are some ideas to implement virtual functions in Postgres? :-)
Without these functions iheritance are useless from the point of the OOP.

--
// Dmitriy.