Help with quote escaping in plpgsql

[Joseph]

Hi,

I want to make a function that accepts a pre-formatted varchar
argument which will then be used in an IN clause:

create or replace function testing(varchar) returns int as '
declare
int c := 0;
begin
select count(*) from my_table where some_field in ( $1 ) into c;
return c;
end
' language 'plpgsql';
But I can't figure out how to escape the varchar string I pass. I have
tried:

'''hello'',''world'''

and all sorts of other things.

Any suggestions?

Thanks,

Joseph

[Jan Wieck]

Joseph wrote:

Hi,

I want to make a function that accepts a pre-formatted varchar
argument which will then be used in an IN clause:

create or replace function testing(varchar) returns int as '
declare
int c := 0;
begin
select count(*) from my_table where some_field in ( $1 ) into c;
return c;
end
' language 'plpgsql';
But I can't figure out how to escape the varchar string I pass. I have
tried:

'''hello'',''world'''

and all sorts of other things.

Any suggestions?

This is currently not supported. Your best bet on this would be to use
the EXECUTE functionality like

create function testing(varchar) returns int as '
declare
r record;
begin
for r in execute ''select count(*) as cnt from my_table
where some_field in ('' || $1 || '')''
loop
return r.cnt;
end loop;
return 0;
end;
' language plpgsql;

select testing('''hello'',''world''');
Jan

--
#================================================= =====================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================= = Ja******@Yahoo.com #
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org