How to deal with apostrophes when calling a stored function?

Question

I have a pretty straight forward question about something that bugs me for some reason.

Let's say we have this PL/SQL function:

CREATE OR REPLACE FUNCTION gettextfromtitle(title text) RETURNS SETOF record
    LANGUAGE plpgsql
    AS $$
DECLARE

##############

END
$$;

The point of this function is to get any variable (called title here) to get through it.

if I try to call this function on a terminal with this:

psql -h ### -X -U ### -d ### -t -c "select text from gettextfromtitle('I type anything') AS (text varchar);"

It will work.

If I try to call this function on a terminal with the same text but with an apostrophe in it, it will break:

psql -h ### -X -U ### -d ### -t -c "select text from functionName('I t'ype anything') AS (text varchar);"

Even if I handle the apostrophe with a quote_literral() inside my function, it will break on the function call.

Is there any way to prevent this ? Or the only solution is having a preliminary treatment that change any single quote into 2xsingle quote ?

psql -h ### -X -U ### -d ### -t -c "select text from functionName('I t''ype anything') AS (text varchar);"

Thanks for any help

Answer 1

Use dollar quoting:

psql -h xxx -X -U xxx -d xxx -t \
    -c "select text from functionName(\$\$I t'ype anything\$\$) AS (text varchar)"

Since we use double quotes, the $ has to be escaped with a backslash.