0

I need to iterate over a result set and update records from the data. I am using an anonymous block but the information does not persist.

Like this...

do $$ 
declare dados Record; 
begin 
   for dados in 
        select 
           vd."Id"
        from "Vendas" vd 
           inner join "ItensVendas" iv on iv."VendaId" = vd."Id" 
        where vd."Especie" = 'NFE' and coalesce(vd."ClienteId", 0) = 0 and iv."CodCFOP" in (5949, 5927) 
        group by vd."ChaveNFE", vd."Id", vd."DataCadastro" 
   loop 
        execute format('update "Vendas" set "EhConsumoProprio" = true where "Id" = %L', dados."Id"); 
   end loop; 
end $$; 

And again...

do $$
declare dados Record;
declare cmd varchar(300);
begin
    for dados in 
        select
            vd."ChaveNFE",
            vd."Id",
            vd."DataCadastro"
        from
            "Vendas" vd
        inner join "ItensVendas" iv on
            iv."VendaId" = vd."Id"
        where
            vd."Especie" = 'NFE'
            and coalesce(vd."ClienteId",0)= 0
            and iv."CodCFOP" in (5949, 5927)
        group by vd."ChaveNFE",
                 vd."Id",
                 vd."DataCadastro" 
    loop
        raise notice '%',dados."Id";
        update "Vendas" set "EhConsumoProprio"=true where "Id"=dados."Id"; -- It don't persists
        cmd := format('update "Vendas" set "EhConsumoProprio"=true where "Id"=%L', dados."Id");
        raise notice '%', cmd;
            execute cmd; -- It don't persists
    end loop;
end $$;
  • Why PL/pgSQL and a loop? That can be done much more efficiently using a single UPDATE statement? But if the update doesn't "persist", maybe you turned off autocommit and simply forgot to commit the changes? – a_horse_with_no_name Mar 15 at 14:00
  • why do I need to put this in a migration of the entity framework to run across multiple databases. When I use commit manually I have... ERROR: cannot begin/end transactions in PL/pgSQL – Jones Romão Mar 15 at 14:12
  • You need a commit after the do block - but again: you don't need PL/pgSQL for this at all. A simple UPDATE will work just fine. – a_horse_with_no_name Mar 15 at 14:14
  • It is true. It would be best to just use a simple update. – Jones Romão Mar 15 at 17:10
0

IMHO just a single UPDATE can solve your issue. Something like this:

UPDATE "Vendas"
   SET "EhConsumoProprio" = TRUE
  FROM "ItensVendas"
 WHERE "ItensVendas"."VendaId" = "Vendas"."Id"
   AND "Vendas"."Especie" = 'NFE'
   AND "Vendas"."ClienteId" IS NOT DISTINCT FROM 0
   AND "ItensVendas"."CodCFOP" IN (5949, 5927);
  • "Vendas"."ClienteId" IS NOT DISTINCT FROM 0 in a WHERE clause (where only true matters) is an expensive way of saying "Vendas"."ClienteId" = 0 – Erwin Brandstetter Mar 15 at 15:16
  • @ErwinBrandstetter why is more expensive? The idea is deal with NULLs correctly. The original SQL did it using COALESCE function: coalesce(vd."ClienteId",0)= 0 – fabriziomello Mar 20 at 21:12
  • ... which is not the same. The original translates to (vd."ClienteId" = 0 OR vd."ClienteId" IS NULL) or (vd."ClienteId" <> 0) IS NOT TRUE. Your translation is incorrect and inefficient, no offence. – Erwin Brandstetter Mar 20 at 21:21
  • oops... you're correct... I'm completely mislead... the Op want to return all records where vd."ClienteId" = 0 OR vd."ClienteId" IS NULL using the COALESCE. Sorry about the noise. – fabriziomello Mar 22 at 0:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.