Re: executing a procedure withing a procedure?

[Keith Worthington <KeithW(at)NarrowPathInc(dot)com>]

ben sewell wrote:
> Hi guys,
> I'm re-writing a MS Access frontend in readiness for a postgresql 
> migration but I am abit stuck since postgres is completely new to me. 
> Anyways, is it possible to execute a procedure within a procedure?
>  
> What I'm trying to do is that in the access frontend, there is a form 
> where you can generate reports with all the options 
> (adviser,provider,introducer,plangroup,plantype, and datelist- which 
> sets two text fields date_start and date_end. Additionally, there are 
> checkboxes for all the options which are checked when an option has been 
> selected eg when you choose an adviser. To make things even more 
> complicated, you can also select an adviser and a datelist so you can 
> records for an adviser for the specified time period for example.
>  
> The idea I have in mind is that I will create pass through queries in 
> VBA code builder that takes the parameters and does all the processing 
> on the server and returns the query that is then used to make the report.
>  
> What the problem is that there is alot of repetition because of the 
> filters (subqueries are used in the access queries) so I was hoping 
> there was a way I could reduce the amount of code i would have to write 
> for the procedure that returns the qeury results.
>  
> I'm also open to any other suggustions on how to tackle this problem.
>  
> Cheers,
> Ben

Ben,

We wrote our front end using VB.  Although we started out by embedding 
the SQL in the front end we have found that it is easier to maintain by 
moving as much of that code as possible into the database.  To that end 
all new SQL that is embedded in the front end is one of the following 
two forms.

SELECT *
  FROM interface.func_myfunc(parm1, parm2, ...);

SELECT *
  FROM interface.view_myview
 WHERE my_condition;

This has enabled the front end developer to focus on the user interface 
rather than the SQL and it pushes the SQL development over to the 
database engineer.

HTH
--

Kind Regards,
Keith