From: | Andre Lopes <lopes80andre(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Transactions, How to? |
Date: | 2010-02-20 17:22:25 |
Message-ID: | 18f98e681002200922y3c98614an1261abcdbc627ad6@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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,
From | Date | Subject | |
---|---|---|---|
Next Message | Andreas Kretschmer | 2010-02-20 17:34:34 | Re: Transactions, How to? |
Previous Message | Heddon's Gate Hotel | 2010-02-20 16:13:45 | Re: PostgreSQL fails to start |