Re: creating variable views

Dado,
> Formatting functions? As in formatted output? Could you give an
> example?

Yes.  For example, I have a function called:

qf_format_contact_name (VARCHAR, VARCHAR, VARCHAR, VARCHAR) 

That produces (depending on data) formatted output like:

Julie Snodgrass
Ms. Keller, Law Clerk
Human Resources Director

... and I call in in views like:

CREATE VIEW lv_billing_contacts AS
SELECT client_usq, client_name, qf_format_contact_name(prefix,
last_name, first_name, contact_title) AS contact_name
FROM clients JOIN client_contacts ...

All this function does is format output, rather than perform any fancy
manipulation.  I find that the Postgres view optimizer has no trouble
with such functions.

More complex functions, like qf_calc_next_invoice_date(VARCHAR) which
calculates a client's next invoice date based on their invoice interval
plus certain system variables pretty much kills the view optimizer if I
do a WHERE on that column, since the optimizer doesn't know what to
expect from the function.



> > This works quite well for me.  It's a *lot* easier to adjust than
> DB
> > level security ("all of the accountants need access to the Void
> > function" can be fixed with a single UPDATE)
> 
> I'm sorry but I have no idea what you're talking about here.
> What is this problem? What is the Void function?

That was an example of the sort of sweeping user access change one might
be asked to implement.  For example, you might set up the system at the
start so that only the Sysadmin can "void" (cancel) financial
transactions for security purposes.  However, changes in your company's
business policies in 3 months may dictate that the whole accounting
dept.  needs to be able to void.  Using SQL DB security, this can be a
serious headache, as opposed to a single update with an interface-based
system.

-Josh


______AGLIO DATABASE SOLUTIONS___________________________                                      Josh Berkus Complete
informationtechnology      josh@agliodbs.com  and data management solutions       (415) 565-7293 for law firms, small
businesses       fax 621-2533   and non-profit organizations.      San Francisco