Using a variable in the IN clause of a postgresql query

Question

I am very new to postgres. I'd like to accomplish a simple task.

I have a function as below:

create or replace function function1(source json)     
returns json as $$
DECLARE
    output_data json;
begin
    raise notice '%',source::text;
    select json_agg(name_id) into output_data from table1 where channel_id in SOURCE;   -- I want to use SOURCE as dynamic
    return output_data;
end;
$$ LANGUAGE plpgsql;

The function takes input a json parameter (source) and I want to run the IN clause using the parameter.

For example, When I run the below:

select function1('[5555558,5555559]');

I should get an output like [11111,22222] ---> these are the name_id

One way to accomplish this task is to insert all the values of SOURCE into a table (new_table) and then do the following.

select json_agg(name_id) into output_data from table1 where channel_id in (select channel_id from new_table);

I would like to know of other better techniques, such as using equivalent of collection or dynamic queries.

thank you

Patrick · Accepted Answer · 2016-06-16 13:53:45Z

1

In principle this can be done with a single SQL statement:

SELECT json_agg(t.name_id)
FROM table1 t
JOIN json_array_elements_text('[5555558,5555559]'::json) j(value)
  ON j.value = t.channel_id::text;

If you want a function then it is simply:

CREATE FUNCTION function1 (source json) RETURNS SETOF json AS $$
  SELECT json_agg(t.name_id)
  FROM table1 t
  JOIN json_array_elements_text(source) j(value) ON j.value = t.channel_id::text;
$$ LANGUAGE sql;

edited Jun 16, 2016 at 13:53

answered Jun 16, 2016 at 13:43

Patrick

33k7 gold badges73 silver badges102 bronze badges

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

1 Comment

Sam Over a year ago

The query worked perfectly, thanks for the solution.

Collectives™ on Stack Overflow

Using a variable in the IN clause of a postgresql query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related