CODEWITHSUNDEEP
sqlpostgresql
user3839717
user3839717

Reputation:

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

Below is a sample scenario for what actually am 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 a Function to get val from table Sample(don't say that you can use select for this because this is just an example to elaborate what is 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 : the id can be 2 or more

Upvotes: 3

Views: 70

Answers (1)

user330315
user330315

Reputation:

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]);

Upvotes: 3

Related Questions