Re: Views versus user-defined functions: formatting, comments, performance, etc. - Mailing list pgsql-general

On Tue, Aug 28, 2012 at 3:25 PM, Dmitriy Igrishin <dmitigr@gmail.com> wrote:

> 2012/8/20 Merlin Moncure <mmoncure@gmail.com>
>>
>> On Sun, Aug 19, 2012 at 8:14 AM, Dmitriy Igrishin <dmitigr@gmail.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

automatically track added columns to the table.

*) 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

*) do you really need a factory function to create 'user'  -- why not
allow regular inserts?

*) 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.

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).