Clever Geek Handbook

How to pass number of arguments to function to use in IN clause of Postgresql

Below is a sample script for what it is actually trying to achieve.

create table sample (id int,val text);

insert into sample values (1,'A');
insert into sample values (2,'B');
insert into sample values (3,'C');
insert into sample values (4,'Z');
insert into sample values (5,'X');
insert into sample values (6,'Y');

Then create Function

to get val

from table Sample

(don't say you can use select to do this, because this is just an example to work out my problem)

CREATE OR REPLACE FUNCTION FN_GETVAL (ids text) returns table ( val text) as 
$$
select val from sample where id in(ids); -- here i want to pass the ids for example 1,5
                                         --select val from sample where id in(1,5); 
$$
language sql

When creating this ERROR: operator does not exist: integer = text

so how to create a function for this - select * from FN_GETVAL(1,5)

Note: ID can be 2 or more

+3


source to share


1 answer


I would pass the values ​​as an array:



CREATE OR REPLACE FUNCTION FN_GETVAL (id_list int[]) 
   returns table (val text) 
as 
$$
  select val 
  from sample 
  where id = ANY (id_list);
$$
language sql ;

select *
from fn_getval(array[1,2,3]);
+3


source






More articles:


All Articles