Re: BUG #15632: Correctly escaped strings are mishandled in function

On Mon, Feb 11, 2019 at 11:47 AM Kaleb Akalework
<kaleb(dot)akalework(at)asg(dot)com> wrote:
> To be clear what I want is to send in 'SOLD''' in the function so in the query it can be used as is to store the final string as SOLD'. IF I use the functions you mention it will just "" or add more quotes.

Please don't top-post.

The methods mentioned work perfectly; any failure to do what you are
desiring is from improper usage. Mistakes cannot be pointed out
unless you show them but here is one that does work.

create or replace function echo(in_str text)
returns text
language plpgsql
AS $func$
DECLARE qry text; res text;
BEGIN
--qry = 'SELECT ' || quote_literal(in_str) || ';';
--qry = format('SELECT %L', in_str);
--EXECUTE qry INTO res;
qry = 'SELECT $1';
EXECUTE qry INTO res USING in_str;
RETURN res;
END;
$func$
;
SELECT echo($in$SOLD'$in$);
-> SOLD'

A combination of "format" (for identifiers) and "execute using" (for
literals) is arguably the best solution.

David J.