Re: how to call sql code without function

Поиск
Список
Период
Сортировка
От giuseppe.derossi@email.it
Тема Re: how to call sql code without function
Дата
Msg-id cebd74b67f3abf8a082a9ae345297b28@83.225.111.96
обсуждение исходный текст
Ответ на how to call sql code without function  (giuseppe.derossi@email.it)
Ответы Re: how to call sql code without function  ("Scott Marlowe" <scott.marlowe@gmail.com>)
Список pgsql-admin
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



В списке pgsql-admin по дате отправления:

Предыдущее
От: Adam Radlowski
Дата:
Сообщение: Re: SOS. Database Lost
Следующее
От: Peter Elmers
Дата:
Сообщение: Re: adding a user with the permission to create trimmed users