Home > OS >  Select into a variable with a "case" expression
Select into a variable with a "case" expression

Time:03-03

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?

CodePudding user response:

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';
  • Related