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