Re: how to call sql code without function

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(at)gmail(dot)com>
To:
Cc: pgsql-admin(at)postgresql(dot)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(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 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&#39;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&#39;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(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
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&#39;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&#39;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&#39;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
>
>
>
--
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