me respondo yo mismo esta dentro de las exotic features
pero se puede simular asi
I really needed this functionality in PostgreSQL. A common use for
autonomous transactions is error logging. I want to log sqlerrm in a
function and raise an exception so the calling application knows there is an
error and I have it logged to a table.
I figured out a way to "hack" an autonomous transaction by using a dblink in
a function and here is a simple example:
create or replace function fn_log_error(p_function varchar, p_location int,
p_error varchar) returns void as
$$
declare
v_sql varchar;
v_return varchar;
v_error varchar;
begin
perform dblink_connect('connection_name', 'dbname=...');
v_sql := 'insert into error_log (function_name, location, error_message,
error_time) values (''' || p_function_name || ''', ' ||
p_location || ', ''' || p_error || ''', clock_timestamp())';
select * from dblink_exec('connection_name', v_sql, false) into v_return;
--get the error message
select * from dblink_error_message('connection_name') into v_error;
if position('ERROR' in v_error) > 0 or position('WARNING' in v_error) > 0
then
raise exception '%', v_error;
end if;
perform dblink_disconnect('connection_name');
exception
when others then
perform dblink_disconnect('connection_name');
raise exception '(%)', sqlerrm;
end;
$$
language 'plpgsql' security definer;
I thought I would share and it works rather well. Maybe someone could
enhance this concept to include it with the core database to provide
autonomous transactions.
Jon
2014/1/7 Juan J Rosales Rodriguez <jjrosales...@gmail.com>
> Ummmmm postgres ya tiene transacciones autonomas ?????
>
>
> 2014/1/7 Juan J Rosales Rodriguez <jjrosales...@gmail.com>
>
>> Buenas tardes las funciones generan una transaccion automaticamente pero
>> si quisiera ejecutar una funcion pero que me este aplicando los cambios a
>> medida que esta se ejecuta por ejemplo por que quiero que los cambios se
>> aplicen instantaneamente ya que pueden ser muchos productos.
>>
>> DECLARE
>> v_cursor record;
>> v_result boolean;
>> BEGIN
>> FOR v_cursor IN
>> SELECT
>> prod.idproducto,
>> prod.existencia,
>> prod.precio
>> FROM productos
>> where prod.existencia * prod.precio <> prod.importe
>> LOOP
>>
>> BEGIN TRANSACTION;
>>
>> UPDATE productos SET importe = v_cursor.cantidad * v_cursor.precio
>> WHERE idproducto= v_cursor.idproducto;
>>
>> COMMIT;
>>
>>
>> END LOOP;
>> RETURN 1;
>> END;
>>
>>
>
