plpgsql function with update and seeing changed data from outside during run

From: Clemens Schwaighofer <clemens(dot)schwaighofer(at)e-graphics(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: plpgsql function with update and seeing changed data from outside during run
Date: 2011-06-09 06:41:16
Message-ID: BANLkTim5YWwwCxdpL4rEQrFuqE5FJvQSYQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,

I have a plpgsql function where I read data from a table in a loop and
update data in a different table.

Is it possible to see the updated data from a different access during
the run of this function? Or is this impossible because the function
is a "transaction" and no data change is visible outside until the
function is finished? I can see the changed data inside the function.

the function looks something like this

create or replace function insert_log(i_log_id INT, i_queue_id INT)
returns "varchar"
as $$
declare
[... here are record, etc declarations]
begin
for myrec in select * from queue where queue_id = i_queue_id;
loop
insert into log_sub () values ();
update log set rows = [internal row count] where log_id = i_log_id;
end loop
end; $$ language plpgsql;
--
★ Clemens 呉 Schwaighofer
★ IT Engineer/Web Producer/Planning
★ E-Graphics Communications SP Digital
★ 6-17-2 Ginza Chuo-ku, Tokyo 104-8167, JAPAN
★ Tel: +81-(0)3-3545-7706
★ Fax: +81-(0)3-3545-7343
http://www.e-gra.co.jp

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Radosław Smogura 2011-06-09 07:32:38 Re: Best Practices - Securing an Enterprise application using JBOSS & Postgres
Previous Message John R Pierce 2011-06-09 06:39:42 Re: Adding "quota user limit" using triggers