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@xxxxxxxxx>
To:
Cc: pgsql-admin@xxxxxxxxxxxxxx
Oggetto: Re: 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@xxxxxxxx <giuseppe.derossi@xxxxxxxx> 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@xxxxxxxxx> To: Medi Montaseri
<montaseri@xxxxxxxxx> Cc: pgsql-admin@xxxxxxxxxxxxxx
> Oggetto: Re: how to call sql code without
function Data: 20/08/07 20:05 >>>> On 8/20/07, Medi Montaseri
<montaseri@xxxxxxxxx
> > 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@xxxxxxxxxxxxxx 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
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
