SEO

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....850&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....893&d=20070821



---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

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.

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match