Re: Was: Triggers, Stored Procedures, PHP

Chris Travers wrote:
> "Shane D" <shane.dawalt@wright.edu> Wrote:
>>   Could someone explain to me the usefulness of views?  I understand
>>how they are created.  I understand a single query can be created as a
>>view returning all records in a single column of a single table, or
>>maybe even multiple columns across many tables using a complex join.

> 3)  Providing application-specific presentations of the data, thus
> insulating them from the actual structure, or allowing a denormalized view
> of a highly normalized database.

In several cases, we've taken long functions from various perl and PHP
code bases, combined the "select" queries from them into views, and
converted the rest of the logic into stored procedures (in plpgsql, no
less).

>>   That sounds find if all you want to do is to populate your drop-down
>>list box with selection choices or use the same search criteria each
>>time.  But if I want to access certain information for a particular
>>customer that requires joins and the like, then a view would be great.
>>But as far as I know, I am unable to place search parameters into a
>>view.  Is this false or am I totally missing the point of views?

> For that join you are mentioning, one would have to know how you were
> looking at the information, etc. to know whether a simple join would be the
> best way to go or whether a view would be better.

But as a sampler, you can use the view to create a virtual table (that's
a tautology, isn't it) which contains the the data set that the function
uses as for output (IIRC, this is called the "domain"). The specifics of
your function can be coded into a stored procedure, which can accept
(for example) a customer ID, and return all the values from the view
that relate to that customer.

In that case, you'd probably start the definition of your plpgsql stored
procedure as:

create or replace function get_transactions (INTEGER) returns set of
record as '
DECLARE
    cust_id ALIAS FOR $1;
BEGIN
for r in select ... from ... loop
     return next r;
end loop;
return;
END
' language 'plpgsql';

But I would certainly love to have parameterised views :)

Alex