I have a postgres DB and I want to create a function that returns a copy of my table with a new column that has a value of 1 if its id is inside the array(idds[]) that the function gets as an input. In the code below I’ve try to create a temporary table(chosen) that have id if it’s in the idds array and to manually add the isChosen column that obviously doesn’t work…
CREATE OR REPLACE FUNCTION public.getTableFromArray(idds integer[]) RETURNS table( id INTEGER, isChosen INTEGER ) LANGUAGE 'plpgsql' AS $BODY$ begin with chosen AS(SELECT id,isChosen=1 FROM table1 WHERE ARRAY[table1.id] <@ idds) return query SELECT id FROM table1 LEFT JOIN chosen ON table1.id=chosen.id; end; $BODY$;
Advertisement
Answer
you can use this query instead :
select * , case when ARRAY[table1.id] <@ idds then 1 else 0 end as choosen FROM table1;
so:
CREATE OR REPLACE FUNCTION public.getTableFromArray(idds integer[]) RETURNS table( id INTEGER, isChosen INTEGER ) LANGUAGE 'plpgsql' AS $BODY$ begin return query select id , case when ARRAY[table1.id] <@ idds then 1 else 0 end as isChosen FROM table1; end; $BODY$;