I have a table if key - array pairs and need to check if a string is an element in a given array in this table. I have managed to write an if statement to check if the element is in an array but if I try to get the array from the table it does not work.
In the example below,
SELECT list FROM public."HelloWorldTable" WHERE name = 'HelloWorldList'
selects the list element that contains an array that is identical to the one in the other if statement but I can not seem to access it the same.
CREATE TABLE IF NOT EXISTS "HelloWorldTable"( Name text PRIMARY KEY, List text ); INSERT INTO public."HelloWorldTable" (name, list) VALUES ('HelloWorldList', ARRAY['Hello', 'World']); DO $$ BEGIN IF 'Hello' = ANY (ARRAY['Hello', 'World']::text) THEN RAISE NOTICE 'Found'; ELSE RAISE NOTICE 'Not found'; END IF; -- failing attempt: -- IF 'Hello' = ANY (SELECT list FROM public."HelloWorldTable" -- WHERE name = 'HelloWorldList') THEN -- RAISE NOTICE 'Found'; -- ELSE -- RAISE NOTICE 'Not found'; -- END IF; END $$;
I found this other question but I can not get it working the way I need it to. I have tried adding
::text to the second if statement but I only get different errors.
How can I solve this?