Use parameter inside subquery

Question

How can i use my parameters inside a subquery with ' '? (Postgres v.10)

create or replace function test(p_1 character varying, p_2 character varying)
returns table (id integer, total integer, fruit character varying)
LANGUAGE plpgsql
AS $$ 
begin
 return query
    Select * from dblink(
                        'host=myhost
                        user=myuser
                        password=mypw
                        dbname=mydb',
                        'select id,total,fruit from fruits 
                        where fruit in (p_1,p_2)') as x(id integer,total integer,fruit varchar);
end;
$$

If i call the function

select * from test('apple','orange')

I get this ERROR: colum "p_1" and "p_2" does not exist..

Instead of saying p_1 it's possible to call parameters with $ signs or something. I don't know if that's the real approach but i can't find any documentation about it anywhere?

Answer 1

You can try this (not tested) :

 return query
    Select * from dblink(
                        'host=myhost
                        user=myuser
                        password=mypw
                        dbname=mydb',
                        'select id,total,fruit from fruits 
                        where fruit in (' || p_1 || ',' || p_2 || ')') as x(id integer,total integer,fruit varchar);