Skip to content
Advertisement

How to add column inside postgres function without saving it to the db?

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$;
User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement