Postgres Pro
Downloads
Home   >   mailing lists

Thread: How do I trigger the re-creation of a crosstab-type view

How do I trigger the re-creation of a crosstab-type view

From
George Weaver
Date:
Hi Everyone,
 
I have created a function that creates a view.  The function is designed to use the distinct values of one column of an existing view to create a new view with the distinct values forming the columns in the new view for aggregation purposes (type of crosstab).  When the function is invoked, it creates the new view.
 
The existing view the function works from is a summary view that draws from numerous tables in the database.  It is for viewing information only, i.e. no edit, delete, or update functionality.
 
What I would like help with is the mechanism I should use to determine when there is a new distinct value in the column that is being pivoted.  When this happens the function needs to be invoked to recreate the view incorporating the new column.
 
I am a novice when it comes to triggers and rules.
 
Should I be using a trigger or rule to do this?  If so, should the trigger or rule be based on the summary view, or the table from which the pivot column is originally obtained from?  If I base the rule or trigger on the summary view, does the user need to "view" the summary view in order for the trigger or rule to be invoked? 
 
I'm not sure if I've explained this very well but I hope you get the idea and can point me in the right direction.
 
Thanks in advance for your help!
 
George

Re: How do I trigger the re-creation of a crosstab-type

From
Joe Conway
Date:
George Weaver wrote:
> I have created a function that creates a view.  The function is
> designed to use the distinct values of one column of an existing view
> to create a new view with the distinct values forming the columns in
> the new view for aggregation purposes (type of crosstab).  When the
> function is invoked, it creates the new view.
>
> The existing view the function works from is a summary view that
> draws from numerous tables in the database.  It is for viewing
> information only, i.e. no edit, delete, or update functionality.
>
> What I would like help with is the mechanism I should use to
> determine when there is a new distinct value in the column that is
> being pivoted.  When this happens the function needs to be invoked to
> recreate the view incorporating the new column.

Possibly you could use crosstab() (from contrib/tablefunc) which would
do this for you dynamically?

Joe