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

Question

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

Erwin Brandstetter · Accepted Answer · 2021-04-13 03:59:46Z

1

Or, with a lot less noise, a proper boolean output column, and without the unhelpful CaMeL case identifiers in a plain SQL function:

CREATE OR REPLACE FUNCTION public.get_table_from_array(idds integer[])
  RETURNS TABLE(id int, is_chosen bool)
  LANGUAGE sql AS
'SELECT t.id, t.id = ANY(idds) FROM table1 t';

Might as well just run the SQL command directly, though:

SELECT id, id = ANY('{1,2,3}'::int[]) AS is_chosen FROM table1;

answered Apr 13, 2021 at 3:59

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

eshirvana · Accepted Answer · 2021-04-11 16:42:13Z

0

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

answered Apr 11, 2021 at 16:42

eshirvana

24.7k3 gold badges28 silver badges43 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related