Thread: executing a procedure withing a procedure?

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

--- ben sewell <mosherben@gmail.com> wrote:

> Hi Keith,
> thanks for your reply.
>
> What about inserting and deleting data? what do I need on the database
> server?
>

With pass-throught queries you can send the inserts or deletes.

or you can bind your form directly to an "updateable/insertable/deleteable" view using the rule
system.  Rules on the view will handle all of the processing for you.  There is a little bit of
set-up required however:

http://www.postgresql.org/docs/8.1/interactive/rules-update.html

Regards,

Richard Broersma Jr.

> What do I add on the front end in order to bid the form to a view when
> updating data?

Don't forget to copy the list when you have a question so that other can reply also.

I am not sure I understand your first question.  But if you want to bind a view to a form in
ms-access, look at:
http://www.postgresql.org/download/
under application interfaces -- odbc


> Also, the procedure I need to write are for a report in access. How would I
> link the procedure to the report? I do have several parameters for the
> procedure, so any advice is welcome.

hmm.. I never tried binding a report to a pl-pgsql function before.  I imagine that you could do
it through a pass-through query where you:
select * from <your_function>;

Alternitivly, you could create a view in postgresql that is a "select * from <your_function>" and
then bind your report to that view.

Regards,

Richard Broersma Jr.

Good Monday,

Is there a way to convert data type `datetime` into an integer
(unix_timestamp) while doing ALTER COLUMN `column_name` TYPE int4
USING CAST(`column_name` as integer) ...... something like this?

Thanks,
Mike E.

On Mon, Aug 14, 2006 at 08:00:59AM -0400, Mike Ellsworth wrote:

hi,

> Is there a way to convert data type `datetime` into an integer
> (unix_timestamp) while doing ALTER COLUMN `column_name` TYPE int4
> USING CAST(`column_name` as integer) ...... something like this?

i dont know, if it solves your problem - for reading i usually cast like
this:

 EXTRACT(EPOCH FROM ts) as ts

and for writing:

 ts::int4::abstime::timestamp

--
cu

On Mon, Aug 14, 2006 at 08:00:59AM -0400, Mike Ellsworth wrote:
> Is there a way to convert data type `datetime` into an integer

There is no datetime type.  Do you mean timestamp or timestamp with
time zone?

> (unix_timestamp) while doing ALTER COLUMN `column_name` TYPE int4
> USING CAST(`column_name` as integer) ...... something like this?

Is this what you're looking for?

ALTER TABLE foo ALTER COLUMN column_name TYPE integer
  USING extract(epoch FROM column_name);

--
Michael Fuhr

Mike Ellsworth <nhrcommu@rochester.rr.com> writes:
> Is there a way to convert data type `datetime` into an integer
> (unix_timestamp) while doing ALTER COLUMN `column_name` TYPE int4
> USING CAST(`column_name` as integer) ...... something like this?

Something involving extract(epoch) would do that ... but why do you
want to?  If the column is really timestamps then you are almost always
best off to declare it as timestamps.  When you have a client that wants
a numeric version, they can do the extract(epoch) bit when they select
the data (or you can make a view that does so, if the client code is
too brain-dead to manage it for itself).  The normal rule of good
database design is that the database should have as much knowledge
as possible about what it's storing, not as little as possible.

            regards, tom lane

Mike Ellsworth <nhrcommu@rochester.rr.com> writes: 

Is there a way to convert data type `datetime` into an integer
(unix_timestamp) while doing ALTER COLUMN `column_name` TYPE int4
USING CAST(`column_name` as integer) ...... something like this?   

Something involving extract(epoch) would do that ... but why do you
want to?  If the column is really timestamps then you are almost always
best off to declare it as timestamps.  When you have a client that wants
a numeric version, they can do the extract(epoch) bit when they select
the data (or you can make a view that does so, if the client code is
too brain-dead to manage it for itself).  The normal rule of good
database design is that the database should have as much knowledge
as possible about what it's storing, not as little as possible.
		regards, tom lane