ERROR: cannot execute UPDATE in a read-only transaction

[asphator]

 Hello,

 

Problem is in the title.

As it is not possible to execute two queries within a report, I made a stored procedure as following :

CREATE TYPE type_seuils AS (

ratio_critical numeric(4,2), 

ratio_survey numeric(4,2), 

freq_critical numeric(4,2), 

freq_survey numeric(4,2)

); 

CREATE OR REPLACE FUNCTION func_seuils(rc numeric, rs numeric, fc numeric, fs numeric) RETURNS SETOF type_seuils AS

$BODY$

DECLARE

record type_seuils;

BEGIN

UPDATE seuils SET 

ratio_critical = coalesce(rc::numeric(4,2), ratio_critical), 

ratio_survey = coalesce(rs::numeric(4,2), ratio_survey), 

freq_critical = coalesce(fc::numeric(4,2), freq_critical), 

freq_survey = coalesce(fs::numeric(4,2), freq_survey);

FOR record IN SELECT ratio_critical, ratio_survey, freq_critical, freq_survey FROM seuils

LOOP

RETURN NEXT record;

END LOOP;

RETURN;

END;

$BODY$

LANGUAGE 'plpgsql';

 

Without UPDATE query, all works well. But with it, I have this error message : cannot execute UPDATE in a read/only query.

May someone help me to figure out how to bypass this? I've been searching for 2 days without any success :(

 

Regards,

Post Edited by asphator at 02/20/2012 08:40

[asphator]

 Up :)

 

I precise that following solution did not worked with me :

- http://jasperforge.org/plugins/espforum/view.php?group_id=112&forumid=102&topicid=49238 (I have already the latest pgsql driver)

and I just found out that someone else already exposed the same problem here :

- http://jasperforge.org/plugins/espforum/view.php?group_id=112&forumid=102&topicid=95462

 

[asphator]

One solution has been found on this forum :  http://www.developpez.net/forums/d1188243/logiciels/solutions-dentreprise/business-intelligence/jasper/error-cannot-execute-update-in-read-only-transaction/

 

Instead of using a stored procedure, it is possible to do it through a scriptlet ;)