Обсуждение: Help with quote escaping in plpgsql

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

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@Yahoo.com #