[GENERAL] Transactions, How to?

[Andre Lopes]

Hi,

I need to do a procedure that does an INSERT and an UPDATE.

The procedure works in this way:

[quote]
CREATE OR REPLACE FUNCTION "public"."apr_insert_newsletter_distritos"
("pSTRING_ARRAY" varchar, "pEMAIL" varchar, "pITEMS" integer,
"pID_WEBSITE_RECOLHA" varchar) RETURNS void AS
$body$
DECLARE
    pSTRING_ARRAY    alias for $1;
    pEMAIL      alias for $2;
    pITEMS     alias for $3;
    pID_WEBSITE_RECOLHA  alias for $4;
    vARRAY_DIST    varchar[];
BEGIN
 -- Passar o pSTRING_ARRAY para ARRAY
 SELECT string_to_array(pSTRING_ARRAY, ',') into vARRAY_DIST;
    --RAISE NOTICE 'BILHETE_IDENTIFICACAO: %' , vARRAY_DIST;

    -- Transacção

     FOR i IN 1 .. pITEMS LOOP
     INSERT INTO am_newsletter_distritos (email, id_website_recolha,
id_distrito)
     values (pEMAIL, pID_WEBSITE_RECOLHA, vARRAY_DIST[i]);
     END LOOP;

     update am_newsletter_emails set subscri_completa = '1'
     where email = pEMAIL and id_website_recolha = pID_WEBSITE_RECOLHA;

END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
[/quote]

Compile OK

But I can't complite if I add to the procedure a BEGIN and a COMMIT. I think
this BEGIN and COMMIT will make a real transaction or I'am wrong?

[quote]
CREATE OR REPLACE FUNCTION "public"."apr_insert_newsletter_distritos"
("pSTRING_ARRAY" varchar, "pEMAIL" varchar, "pITEMS" integer,
"pID_WEBSITE_RECOLHA" varchar) RETURNS void AS
$body$
DECLARE
    pSTRING_ARRAY    alias for $1;
    pEMAIL      alias for $2;
    pITEMS     alias for $3;
    pID_WEBSITE_RECOLHA  alias for $4;
    vARRAY_DIST    varchar[];
BEGIN
 -- Passar o pSTRING_ARRAY para ARRAY
 SELECT string_to_array(pSTRING_ARRAY, ',') into vARRAY_DIST;
    --RAISE NOTICE 'BILHETE_IDENTIFICACAO: %' , vARRAY_DIST;

    -- Transacção
    BEGIN
     FOR i IN 1 .. pITEMS LOOP
     INSERT INTO am_newsletter_distritos (email, id_website_recolha,
id_distrito)
     values (pEMAIL, pID_WEBSITE_RECOLHA, vARRAY_DIST[i]);
     END LOOP;

     update am_newsletter_emails set subscri_completa = '1'
     where email = pEMAIL and id_website_recolha = pID_WEBSITE_RECOLHA;
    COMMIT;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
[/quote]

Compile error: ERROR:  syntax error at end of input at character 917


What I need to do to have a real atomic transaction?


Best Regards,