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

Question

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

user330315 · Accepted Answer · 2014-10-16 06:09:10Z

3

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

answered Oct 16, 2014 at 6:09

user330315

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

2 Comments

user3839717 Over a year ago

i was almost reaching there.

Craig Ringer Over a year ago

It can also be convenient to mark it VARIADIC, so you can call it as fn_getval(1,2,3,42) or as fn_getval(VARIADIC array_agg(some_vol)), depending on which form you need.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related