Re: Triggers, Stored Procedures, PHP. was: Re: PostgreSQL Advocacy, Thoughts and Comments

Jason Tesser wrote:

[snip]
A programmer that doesn't document stuff needs to find a new job :-)
This is more of an issue with management. Anyone who does database apps
for on any kind of a large scale will tell you that views, triggers, etc..
are essential. I am currently in teh process of writing a complete solution
for the college I develop for. Finance, accounting, pos, registration,
student tracking etc...

I'm going to hop on this thread and ask a question rather than rant
(although ranting is fine by me ... rant away).

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.

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?

Shane D

Shane D <shane.dawalt@wright.edu> writes:

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.

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?

It's false. You can treat a view just like a table and add clauses to
your query that restrict it beyond what the view gives you. I think
that's what you're asking about...

Views are useful for things like:

1) Insulating apps from details of the schema which may change
2) Giving different users different, well, views of the data, perhaps
on a column basis. Create a view that only shows a subset of
columns, and only allow unprivileged users access to the view, not
the underlying table(s).

-Doug

Doug McNaught wrote:

It's false. You can treat a view just like a table and add clauses to
your query that restrict it beyond what the view gives you. I think
that's what you're asking about...

Thanks for your reply.

I found an example in the postgresql reference manual in the "CREATE
VIEW" section that shows exactly what you said (reproduced below).

CREATE VIEW kinds AS
SELECT *
FROM films
WHERE kind = �Comedy�;

The manual uses the view thusly:

SELECT * FROM kinds;

But what if the films table also had a field for the production
company. This implies based on the view definition that it too, has the
field (call it prod_co). Could I use the following query to select all
Comedy films distributed by the 'Small Company' production company?

SELECT * FROM kinds WHERE prod_co = 'Small Company';

Yes this is contribed, but humor me please.

Shane

Shane D <shane.dawalt@wright.edu> writes:

But what if the films table also had a field for the production
company. This implies based on the view definition that it too, has
the field (call it prod_co). Could I use the following query to
select all Comedy films distributed by the 'Small Company' production
company?

SELECT * FROM kinds WHERE prod_co = 'Small Company';

Sure, as long as the column is part of the view, you can use it to
constrain the SELECT.

-Doug

After a long battle with technology, shane.dawalt@wright.edu (Shane D), an earthling, wrote:

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?

A VIEW is essentially "macroexpanded" into being the query requested,
in more-or-less the manner LISP handles macro expansion.

Suppose I define a view...

create view january_transactions as
select * from transaction_table where trans_on between
'2003-01-01' and '2003-02-01';

I can then narrow things down when I use the view...

select * from january_transactions -- So I'm looking only at Jan
where txn_type in (1, 2, 4);

If there's a "parameter" that you're expecting to use, then that means
that's a field you want to make sure you are selecting so that, when
you use the view, you can throw in a WHERE clause to specify the
"parameter." That's what the "where txn_type in (1,2,4)" part
expresses.

One of the guys I work with is building "data warehouse" application
code; I keep commending that he use VIEWs as much as possible, and
building summary tables only when performance dictates it. And the
way to define the views most usefully is to make them fairly generic.

In most cases, that means that the VIEW should JOIN tables together to
extract useful information. And anything that could be a parameter
should be selected. That way, filtering can be done on the view, and
so the view can be used for multiple reports.
--
output = reverse("moc.enworbbc" "@" "enworbbc")
http://www3.sympatico.ca/cbbrowne/linuxxian.html
"Heuristics (from the French heure, "hour") limit the amount of time
spent executing something. [When using heuristics] it shouldn't take
longer than an hour to do something."

"Shane D" <shane.dawalt@wright.edu> Wrote:
<snip>

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.

Before I go into the usefulness of views, it is important to understand that
views behave sort of like "logical tables" which can be used in SELECT
queries (or in PostgreSQL, if you add the proper RULEs, you can also use
them in INSERT, UPDATE, or DELETE queries as well).

A view is defined by a select query (often but not always a join). This can
be useful for:
1) Aggregating tables with different permission levels into a single
logical table, hence giving the effect of per-column permissions.
2) Subdividing the table into several logical tables with different
permissions based on which view the row appears in.
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.
4) Data mining and reporting: Views can aggregate tables in ways that make
it easier to make sense of data. Views can be aggregated into other views,
allowing very abstract approaches to reporting.

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?

Think of it this way: PostgreSQL can do the following thigns with your
data:
1) Store it
2) Retrieve it.
3) Ensure that the data is meaningful (via Referential Integrity
enforcement, etc.)
4) Present it in various ways (i.e. complex select statements, views, etc.)

Views represent a tool for changing the presentation of the data in the
database. Neither more nor less.

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.

Best Wishes,
Chris Travers

On 29/11/2003 16:24 Jason Tesser wrote:

[snip]
A programmer that doesn't document stuff needs to find a new job :-)

Agreed. So you're replaced him and inherited a documentation-free
application. How many favours has he done you by squirrelling away section
of business logic in the database?

This is more of an issue with management. Anyone who does database apps
for on any kind of a large scale will tell you that views, triggers,
etc..
are essential. I am currently in teh process of writing a complete
solution
for the college I develop for. Finance, accounting, pos, registration,
student tracking etc...

I've worked on stuff for some of the largest companies in the world if
that counts. Mind you, I've been in the business 24 years (18 of those as
an independent consultant) so maybe I'm just a newbie :)

For your accounting, take a look at SQL-Ledger (www.sql-ledger.org). It
might save you months of effort.

You might not have understood me or I am not understanding you.

It feels like we're 2 people divided by a common language...

-- 
Paul Thomas
+------------------------------+---------------------------------------------+
| Thomas Micro Systems Limited | Software Solutions for the Smaller 
Business |
| Computer Consultants         | 
http://www.thomas-micro-systems-ltd.co.uk   |
+------------------------------+---------------------------------------------+

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

On Mon, Dec 01, 2003 at 09:38:06AM +1100, Alex Satrapa wrote:

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

Me too. I've created many functions to extract data that are joined to
other functions. All in all the result is not as optimal as it could
be, because the optimizer can not poke into the functions, and the
estimates about functions are only guesses. If one could use
parametrized views instead of functions the whole mess would probably be
more optimal.

Maybe there's a TODO here?

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Ninguna manada de bestias tiene una voz tan horrible como la humana" (Orual)

Alvaro Herrera wrote:

Me too. I've created many functions to extract data that are joined to
other functions. All in all the result is not as optimal as it could
be, because the optimizer can not poke into the functions, and the
estimates about functions are only guesses. If one could use
parametrized views instead of functions the whole mess would probably be
more optimal.

How is a "parameterized view" any different than a set returning SQL
function? In either case, you've got the same work to do to teach the
optimizer how to understand it, no? Seems like the todo is just that,
teach the optimizer how to do better with set-returning SQL functions.

Joe

Joe Conway <mail@joeconway.com> writes:

How is a "parameterized view" any different than a set returning SQL function?
In either case, you've got the same work to do to teach the optimizer how to
understand it, no? Seems like the todo is just that, teach the optimizer how to
do better with set-returning SQL functions.

I find almost always that when I wish I had "parameterized views" the view can
be rewritten into more sophisticated views that push the parameterized
constraint outside the view. The problem is that databases usually can't push
the clause back inside. So "parameterized views" usually are a crutch for
working around optimizer limitations but a different limitation than you're
thinking.

For example:

"parameterized view":

create view view_1 as select count(*) from foo where x = $1

rewritten view and query using it:

create view view_2 as select x, count(*) from foo group by x;

select * from view_2 where x = ?

Actually in this case Postgres does fairly well. It does manage to use the
index though it still uses a GroupAggregate instead of a simple Aggregate
node. The run-time is almost as fast as the straightforward query.

--
greg

Given a table like the following:

CREATE TABLE mytable (
progr integer PRIMARY KEY,
record_creation_date date DEFAULT current_date,
...
other columns
...
);

is there a way to deny any modifications to 'record_creation_date'
without using a view?

Thanks,
Claudio

--
Claudio Succa
PERTEL - Torino - Italy
+39-011-437.4141
http://www.pertel.it
http://www.uniassist.it

Claudio Succa <claudio.succa.ml@pertel.it> writes:

Given a table like the following:

CREATE TABLE mytable (
progr integer PRIMARY KEY,
record_creation_date date DEFAULT current_date,
...
other columns
...
);

is there a way to deny any modifications to 'record_creation_date'
without using a view?

Sure, you can use a trigger.

-Doug

Or, depending if you just want to ignore updates to that field (not
always best, but possible, similar to a view).

CREATE OR REPLACE FUNCTION block_col()
RETURNS TRIGGER AS '
BEGIN
NEW.ts_field := OLD.ts_field;
RETURN NEW;
END;
' LANGUAGE PLPGSQL;

In place of the assignment, you could also test for inequality and raise
an error as Doug suggested:
IF NEW.ts_field != OLD.ts_field THEN
RAISE EXCEPTION ''Update to % Not Permitted'',
ts_field
END IF;
Best Wishes,
Chris Travers

Il ven, 2003-12-12 alle 20:11, Doug McNaught ha scritto:

Claudio Succa <claudio.succa.ml@pertel.it> writes:

Given a table like the following:

CREATE TABLE mytable (
progr integer PRIMARY KEY,
record_creation_date date DEFAULT current_date,
...
other columns
...
);

is there a way to deny any modifications to 'record_creation_date'
without using a view?

Sure, you can use a trigger.

-Doug

Thanks a lot Doug.

(Not to reinvent the wheel, do you know where I could find a suitable
function to use in the trigger?)

Claudio

--
Claudio Succa
PERTEL - Torino - Italy
+39-011-437.4141
http://www.pertel.it
http://www.uniassist.it

Claudio Succa <claudio.succa.ml@pertel.it> writes:

(Not to reinvent the wheel, do you know where I could find a suitable
function to use in the trigger?)

No, but it should be pretty trivial to write. Just set up a BEFORE
UPDATE trigger that compares OLD.ts_field against NEW.ts_field and
does a RAISE ERROR if they're different. The PL/pgSQL docs have a few
decent examples of how to write a trigger function.

-Doug

Il sab, 2003-12-13 alle 15:48, Chris Travers ha scritto:

Or, depending if you just want to ignore updates to that field (not
always best, but possible, similar to a view).

CREATE OR REPLACE FUNCTION block_col()
RETURNS TRIGGER AS '
BEGIN
NEW.ts_field := OLD.ts_field;
RETURN NEW;
END;
' LANGUAGE PLPGSQL;

Great. It works exactly as I need.

(notice: I had to replace RETURNS TRIGGER with RETURNS OPAQUE)

Thanks everybody,
Claudio

--
Claudio Succa
PERTEL - Torino - Italy
+39-011-437.4141
http://www.pertel.it
http://www.uniassist.it