[SOLVED] Select into a variable with a "case" expression

Select into a variable with a "case" expression

I'm trying to assign a variable with the result of a case within a select statement. I've tried different approaches:

DO $$
BEGIN
    declare truefalse varchar(100);

    SELECT truefalse = CASE cfgvalue when 'TRUE' THEN 'FALSE' ELSE 'TRUE' END
    from cfg where cfglabel = 'DO_NOT_DISPLAY_WORK_ITEMS_IN_RECEIPT_CONFIRMATION_WIN';
END;
$$

and then

DO $$
BEGIN
    declare truefalse varchar(100);

    SELECT CASE cfgvalue when 'TRUE' THEN 'FALSE' ELSE 'TRUE' END 
    into truefalse
    from cfg where cfglabel = 'DO_NOT_DISPLAY_WORK_ITEMS_IN_RECEIPT_CONFIRMATION_WIN';
END;
$$

and also with the other "case" syntax: case when x=y then z else r end. No luck so far - I get error message: type "truefalse" does not exist. Can anyone shed light on my error?

Solution

As documented in the manual, the declare section goes before the begin. If you do that, your the second PL/pgSQL block will work.

DO $$
DECLARE
  truefalse varchar(100);
BEGIN
    SELECT CASE cfgvalue when 'TRUE' THEN 'FALSE' ELSE 'TRUE' END 
      into truefalse
    from cfg where cfglabel = 'DO_NOT_DISPLAY_WORK_ITEMS_IN_RECEIPT_CONFIRMATION_WIN';
END;
$$

But why not use a proper boolean value?

DO $$
DECLARE
  truefalse boolean;
BEGIN
    SELECT cfgvalue = 'TRUE'
      into truefalse
    from cfg where cfglabel = 'DO_NOT_DISPLAY_WORK_ITEMS_IN_RECEIPT_CONFIRMATION_WIN';
END;
$$

If the cfgvalue always stores the strings 'TRUE' and 'FALSE' you can even cast it directly:

    SELECT cfgvalue::boolean
      into truefalse
    from cfg where cfglabel = 'DO_NOT_DISPLAY_WORK_ITEMS_IN_RECEIPT_CONFIRMATION_WIN';