From: Andrea Suisani <suisani@demetra.com>
To: pgsql-sql@postgresql.org
Subject: PL/pgsql question
Date: Tue, 12 Jun 2001 15:59:03 +0200
Message-ID: <01c801c0f347$d7863ba0$7501a8c0@andrea> (raw)
hi,
i define a function using pgacces with pl/pgsql.
it seems that the queries define in the function wasn't executed.
if I try to excute the same queries from the psql everything, really, works
fine.
the body of the function is:
-----------------------------------
Declare
rec_struttura record;
estra_capo float8;
estra_non float8;
rand_doub float8;
row float8;
capo int;
i int;
rand_max float8;
Begin
i:=0;
For rec_struttura in Select * From struttura_campione Loop
i:=i+1;
prov := rec_struttura.cod_prov;
estra_capo := rec_struttura.num_capo;
estra_non := rec_struttura.num_non_capo;
capo := 1;
row:=prova_rand(prov,capo,1)::float8;
rand_doub:=(estra_capo/row);
if i = 1 then
create table tmp as select * from veneto where cod_prov=prov and
capoluo=capo and rand < rand_doub and occupato = false limit estra_capo;
end if;
if i > 1 then
insert into tmp select * from veneto where cod_prov=prov and capoluo=capo
and rand < rand_doub and occupato = false limit estra_capo ;
end if;
insert into campione_out select note, prefisso, telefono1 as telefono,
cap, cod_com, cod_prov, citta, nome_cogno as nome_cognome, capoluo,
estrazione as ultimo_acesso, rand from tmp;
insert into tab_estrazioni (prefisso,telefono) select prefisso as
presisso, telefono1 as telefono from tmp;
delete from tmp;
rand_max:=prova_rand(prov,capo,2);
update veneto set occupato = true, estrazione = now() where (capoluo =
capo and cod_prov = prov) and (rand <= rand_max) and occupato=false ;
end loop;
return rand_doub;
end;
--------------------------------------------------------------
no errors are report in log file during the excution of this function.
What should i change in the function?
Thanks in advance for any advice
Andrea
P.S. i'm sorry for my bad english
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: suisani@demetra.com
Subject: Re: PL/pgsql question
In-Reply-To: <01c801c0f347$d7863ba0$7501a8c0@andrea>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox