Re: Help with quote escaping in plpgsql

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. #
#================================================== JanWieck(at)Yahoo(dot)com #