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

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