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

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

> > PS. I'm tried to found a SQL formatter for format views definitions
> > stored in the database, but unsuccessful.
>
> Even if you could find one, I wouldn't use it: the database changes
> the structure of you query. Suppose you had:
> CREATE VIEW v AS SELECT *, a*b AS product FROM foo;
>
> The database converts that to:
> CREATE VIEW v AS SELECT a,b, a*b AS product FROM foo;
>
> That means that if you add columns after the fact, the view definition
> in the database will diverge from what the source would create.
> Besides that, there are a number of other things that the database
> does like add unnecessary casts, column aliases and parentheses that
> make a purely textual solution impossible.
>
> merlin
>

--
// Dmitriy.