I have a table as following,
create table test
(
id varchar(20),
text varchar(20)
);
insert into test values(1, 'a')
insert into test values(2, 'b')
insert into test values(3, 'c')
I understand, to check multi-column in where condition, I can do this
SELECT * FROM test WHERE (id, text) = ANY (VALUES('1', 'a'), ('2', 'b'), ('3', 'c'))
I have a function that takes 2 arrays as input,
create or replace function test_function
(
p_ids in varchar[],
p_texts in varchar[]
)
returns table (p_id varchar,
p_text varchar) as $$
begin
return query
SELECT id, text FROM test WHERE (id, text) = ANY (VALUES('1', 'a'), ('2', 'b'), ('3', 'c'))
end;
$$ LANGUAGE plpgsql;
;
My question is, what is the most efficient way to to replace ANY (VALUES(‘1’, ‘a’), (‘2’, ‘b’), (‘3’, ‘c’)) with the actual array input?