Re: how to call sql code without function

Perhaps you could try to express your idea in SQL language (as much as
possible, call it pseudo SQL) and then ask for optimization suggestion

Cheers
Medi

On 8/20/07, giuseppe(dot)derossi(at)email(dot)it <giuseppe(dot)derossi(at)email(dot)it> wrote:
>
> Hi all,
> I agree, but now I actually need a technical and syntactical support in
> order to grab all my queries to keep the php code simple and fast. This is
> the scenario:
>
> I send as input three parameters: x,y,z (one or two could be null)
>
> 1th query on human anagrafic table(S) regarding x,y,z -> 1th temp table
> 2th query returns from the 1th temp table all actual data -> 2th tamp
> table
> 3th and 4th queries retrieve some historical data -> 3th temp table
>
> 5th query selects data regarding the 2th temp table from the 1th building
> tables ->4 th temp table
> 6th query selects data about th 2th table from th 2th building tables ->
> into 4th tamp table
>
> in output I've 1th temp table, th 2th one and th 4th table
>
> By using PHP, afaik, I have to send a query and get the results, now I can
> send the whole package of query and read the results, but so the sql is in
> php: dangerous and slow.
> The other way consist on using the functions but, afaik, a function can
> returns only a set of data a time,
> so I should write three functions but the syntax for the funcion has a lot
> of overhead code lines.
> Is there another way in order to store the whole set of queries and to
> call
> it once from php, sending the parameter and reading the three tables ? I'd
> like to reduce my php calls only to send the parameters (first call) and
> then reading the three table of result ( three calls).
> Now at my present learning I perform 9 calls.
>
> Giu
>
> --------- Original Message --------
> Da: Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>
> To: Medi Montaseri <montaseri(at)gmail(dot)com>
> Cc: pgsql-admin(at)postgresql(dot)org
> Oggetto: Re: [ADMIN] how to call sql code without function
> Data: 20/08/07 20:05
>
> >
> >
> >
> > On 8/20/07, Medi Montaseri <montaseri(at)gmail(dot)com> wrote:
> > > You can think of a database as a filesystem as well. That is do some
> > > processing, store the result in temp table, do some more, etc,etc then
> merge
> > > and process temp tables to arrive at some result.
> > >
> > > Just as in the case of filesystem, if you are operating in a
> concurrent
> > > evironment, you need to fence against that. That is it is possible
> that
> at a
> > > given time two sessions will arrive at the same processing point where
> they
> > > need to create such temp tables.
> >
> > Each session will get it's own temp table, even if they have the same
> name.
> >
> > The real issue is what they do with the data in that temp table to
> > make sure that they're committing changes that make sense given the
> > current state of data in the database.
> >
> > > The other solution which I prefer is to write a stored procedure to
> solve
> > > this. Or get creative with nested and complex SQL queries.
> >
> > Note that nested queries still have some race conditions (such as with
> > aggregate functions) in postgresql.
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: explain analyze is your friend
> >
> >
> >
> --
> Email.it, the professional e-mail, gratis per te: http://www.email.it/f
>
> Sponsor:
> Viaggi, voli, soggiorni...cattura l'offerta e parti con Mondolastminute
> Clicca qui: http://adv.email.it/cgi-bin/foclick.cgi?mid=6850&d=20070821
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>