Обсуждение: Transactions, How to?

Поиск
Список
Период
Сортировка

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,

Re: Transactions, How to?

От
Andreas Kretschmer
Дата:
Andre Lopes <lopes80andre@gmail.com> wrote:

>
> 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?

Right, you are wrong ;-)

A function is atomic, you don't need a begin/commit inside.


Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

Re: Transactions, How to?

От
Andre Lopes
Дата:
Tkanks for the reply.
 
Best Regards,


 
On Sat, Feb 20, 2010 at 5:34 PM, Andreas Kretschmer <akretschmer@spamfence.net> wrote:
Andre Lopes <lopes80andre@gmail.com> wrote:

>
> 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?

Right, you are wrong ;-)

A function is atomic, you don't need a begin/commit inside.


Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general