Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select now(), version(), setseed(.42);


now
version
setseed


2024-05-04 16:23:59.484701+01
PostgreSQL 14.10 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-20), 64-bit




SELECT 1


create table totals(valid,amount)as values
(0,1::int),
(0,1::int),
(0,1::int),
  
(1,1::int),
(1,1::int)
;

SELECT 5


--OP's example
do $f$
  DECLARE
    total1 float;
    total2 float;
  SELECT
      SUM(CASE totals.valid = 0 WHEN TRUE THEN amount ELSE 0 END)::float,
      SUM(CASE totals.valid <> 0 WHEN TRUE THEN amount ELSE 0 END)::float
  FROM
    totals
  INTO total1, total2;
end $f$;

ERROR:  syntax error at end of input
LINE 7:       SUM(CASE totals.valid = 0 WHEN TRUE THEN amount ELSE 0...
                                    ^
CONTEXT:  invalid type name "SUM(CASE totals.valid "


--more examples
do $f$
  DECLARE
    total1 float;
    total2 float;
    SELECT int;--technically, it's allowed
  --`begin` is still missing
  SELECT --it is still a variable declaration
    -- now it just fails on a duplicate declaration 
      SUM(CASE totals.valid = 0 WHEN TRUE THEN amount ELSE 0 END)::float,
      SUM(CASE totals.valid <> 0 WHEN TRUE THEN amount ELSE 0 END)::float
  FROM
    totals
  INTO total1, total2;
end $f$;

ERROR:  duplicate declaration at or near "SELECT"
LINE 8:   SELECT --it is still a variable declaration
          ^


--works now
do $f$
  DECLARE
    total1 float;
    total2 float;
    SELECT int;--technically, it's allowed
  begin --added
  SELECT SUM(CASE totals.valid = 0 WHEN TRUE THEN amount ELSE 0 END)::float,
      SUM(CASE totals.valid <> 0 WHEN TRUE THEN amount ELSE 0 END)::float
  FROM
    totals
  INTO total1, total2;
end $f$;

DO


do $f$
  DECLARE
    total1 float;
    total2 float;
  BEGIN
  SELECT
      SUM(amount)FILTER(WHERE totals.valid=0),
      SUM(amount)FILTER(WHERE totals.valid<>0)
  INTO total1, total2
  FROM
    totals;
end $f$;

DO


do $f$
  DECLARE
    total1 float;
    total2 float;
  BEGIN
  SELECT
  INTO total1, total2
      SUM(amount)FILTER(WHERE totals.valid=0),
      SUM(amount)FILTER(WHERE totals.valid<>0)
  FROM
    totals;
end $f$;

DO


do $f$
  DECLARE
    total1 float;
    total2 float;
  BEGIN
  SELECT
      SUM(amount)FILTER(WHERE totals.valid=0),
      SUM(amount)FILTER(WHERE totals.valid<>0)
  FROM
    totals
  INTO total1, total2;
end $f$;

DO