Обсуждение: how to call sql code without function

Hi all,
I use Postgresql 8.2 in win env. I wrote a set of complex queries which
return three temporary tables of results. I want to use then in php, but the
unique way I know consists on sending one query a time via php, the logic I
need has now been cablated into the sql code.
I could use the functions but it seems a bit long becouse I set some
temporary tables and I shoud use dynamic queries and I've more then a
recordset.
Is there a way to store this piece of sql code into the database and to
recall it with the right input by php and to read only the two tables of
results via PHP?

thanks in advantage

giu

 --
 Email.it, the professional e-mail, gratis per te: http://www.email.it/f

 Sponsor:
 In REGALO un GIOCO! Scegli GPBikes 3D,Bubble Boom, Rock City Empire
 Clicca qui: http://adv.email.it/cgi-bin/foclick.cgi?mid=6732&d=20070820

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.

The other solution which I prefer is to write a stored procedure to solve this. Or get creative with nested and complex SQL queries.

Cheers
Medi

On 8/20/07, Medi Montaseri <montaseri@gmail.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.

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.marlowe@gmail.com>
    To: Medi Montaseri <montaseri@gmail.com>
            Cc: pgsql-admin@postgresql.org
    Oggetto: Re: [ADMIN] how to call sql code without function
    Data: 20/08/07 20:05

    >
>
>
> On 8/20/07, Medi Montaseri <montaseri@gmail.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

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

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Kuriakose, Cinu Cheriyamoozhiyil wrote:
> Hi,
>
>
>
> I was in  the process of downloading the source code of PostgreSQL-7.1
> through CVS, and I was able to do that with the command :
>
>
>
> cvs -d :pserver:anoncvs@anoncvs.postgresql.org:/projects/cvsroot co
> -rREL7_1 -P pgsql
>
>
>
> Now this source code is downloaded is in the pgsql folder, can anyone
> please tell me the command to build PostgreSQL-7.1 binary from this
> source code.
>
> Awaiting response.

Not to be unkind but, "Good god, WHY!??"

If you really must know I suggest this link:

http://www.commandprompt.com/ppbook/c360

Sincerely,

Joshua D. Drake


>
>
>
> Thanks and Regards
>
> Cinu
>
>


- --

      === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564   24x7/Emergency: +1.800.492.2240
PostgreSQL solutions since 1997  http://www.commandprompt.com/
            UNIQUE NOT NULL
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFGylgAATb/zqfZUUQRAgpkAKCbdRhVeAolsCjLR7SxfLtxTTBU8QCcCp+e
XdoAC9GV8Zo7D7ZjPEvl754=
=nfUV
-----END PGP SIGNATURE-----

In other words, are you sure you want to use 7.1 which is about 5 years old ?
Also note that there are binary versions for various platforms, ready to be used.

Cheers
Medi

Hi Medi,
Last night, I wrote a function which perfoms a subset of queries and returns
the results of a temporary table. It's more fast than I believe and that's
ok to me.
There is a prolem : It works too fine. I'm afraid of Murphy's law. I red
into the Internet that there are problem when there is a temporary table in
a function. My queries are in direct way, that is, without Execute
statement.

What kind of problem can arise (session,caching, and so on) ? which should
be the solution ?

After evaluating that I'll try toimprove the code.

This is an excerpt of it:

CREATE OR REPLACE FUNCTION human2build(varchar,varchar,varchar) RETURNS
SETOF out_build1_build2
AS
$BODY$
DECLARE
-- constants
 ...  alias ...

-- variables
...

BEGIN

--drop all temporary table (1)
EXECUTE 'drop table tab_1';
...
EXECUTE 'drop table tab_N';

select alias into tab_temp_1 from tables;
...
lot of other select which store prtial results in temprry table
...

insert all partial results in last_temporary_table

query:='select all from last_temporary_table'

for rec_tab_temp in execute query
LOOP
      ret.foglio = rec_tab_temp.field1;
          ....
          ret.numero = rec_tab_temp.fieldN;
          RETURN NEXT ret;
END LOOP;

end;

$BODY$
LANGUAGE 'plpgsql' VOLATILE;

Thansk for yuor kind attention

Giu

    --------- Original Message --------
    Da: Medi Montaseri <montaseri@gmail.com>
    To:
            Cc: pgsql-admin@postgresql.org
    Oggetto: Re: [ADMIN] how to call sql code without function
    Data: 21/08/07 02:59

    >
> Perhaps you could try to express your idea in SQL language (as much as
possible, call it pseudo SQL) and then ask for optimization
suggestionCheersMediOn 8/20/07,
> giuseppe.derossi@email.it <giuseppe.derossi@email.it> wrote:
> Hi all,I agree, but now I actually need a technical and syntactical
support inorder to grab all my queries to keep the php code simple and fast.
This isthe  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
table2th query returns from the 1th temp table all actual data -> 2th tamp
table3th and 4th queries retrieve some historical data -> 3th temp table
> 5th query  selects data regarding the 2th temp table from the 1th
buildingtables ->4 th temp table6th 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 tableBy using
PHP, afaik, I have to send a query and get the results, now I cansend 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 canreturns 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 callit once from php, sending the parameter and
reading the three tables ? I'dlike 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.marlowe@gmail.com>        To: Medi Montaseri
<montaseri@gmail.com>                Cc: pgsql-admin@postgresql.org
>         Oggetto: Re: [ADMIN] how to call sql code without
function        Data: 20/08/07 20:05        >>>> On 8/20/07, Medi Montaseri
<montaseri@gmail.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
thenmerge> > 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 thatat 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 samename.>> 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 tosolve
> > > 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@postgresql.org so that your       message can get through to the
mailing list cleanly
>
>
>
 --
 Email.it, the professional e-mail, gratis per te: http://www.email.it/f

 Sponsor:
 Cerchi un’auto usata, vuoi vendere il camper o il cellulare? Prova Email.it
Annunci, pochi click per pubblicare e trovare ciò che vuoi!
 Clicca qui: http://adv.email.it/cgi-bin/foclick.cgi?mid=6893&d=20070821

On 8/21/07, giuseppe.derossi@email.it <giuseppe.derossi@email.it> wrote:
>
> Hi Medi,
> Last night, I wrote a function which perfoms a subset of queries and returns
> the results of a temporary table. It's more fast than I believe and that's
> ok to me.
> There is a prolem : It works too fine. I'm afraid of Murphy's law. I red
> into the Internet that there are problem when there is a temporary table in
> a function. My queries are in direct way, that is, without Execute
> statement.
>
> What kind of problem can arise (session,caching, and so on) ? which should
> be the solution ?

The only real danger of using temp tables is that if you don't vacuum
often enough the system catalogs can get bloated.  Also, if you create
a new connection for each access, then you might wind up with a lot of
pg_temp_xxx schemas laying about, but they don't really hurt anything.
 Generally speaking temp tables in postgresql are pretty well behaved.