By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,903 Members | 1,081 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,903 IT Pros & Developers. It's quick & easy.

Help with quote escaping in plpgsql

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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

Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.