Обсуждение: How to escape apostrophes when apostrophes already used to escape something else

Hi there,

probably not too complicated, but although googling my way through
many pages, I don't find the solution.

I have a query which uses already an apostrophe to escape something
else:

$query = "SELECT
            *
        FROM
            crosstab('SELECT
                        COALESCE(c.name, '''') AS name,
                        d.year_start AS year,
                        d.value AS value
                    FROM
                        co2_total_cdiac AS d
                    RIGHT JOIN
                        countries_view AS c ON c.id = d.id_country
                    WHERE
                        (d.year_start = 1960 ) AND
                        (c.name = ''Cote d'Ivoire'' )
                    ORDER BY
                        1,2;', 3) AS ct(name varchar, y_1960 numeric)

Now, "Cote d'Ivoire" needs to be escaped too. But how can I achieve
this?

Thanks for any hints.

Stef

use $$
Or you can always use double single quotes, which is going to
translate into single one, ie : blah = 'foo '' bar';
will give you "foo ' bar" string.

Stefan Schwarzer wrote:
> probably not too complicated, but although googling my way through
> many pages, I don't find the solution.
>
> I have a query which uses already an apostrophe to escape something
> else:
>
> $query = "SELECT
>             *
>         FROM
>             crosstab('SELECT
[...]
>                         (c.name = ''Cote
d'Ivoire'' )
>                     ORDER BY
>                         1,2;', 3) AS ct(name
varchar, y_1960 numeric)
>
> Now, "Cote d'Ivoire" needs to be escaped too. But how can I achieve
> this?

In your case, by using ''Cote d''''Ivoire''.

Yours,
Laurenz Albe

On Fri, Feb 5, 2010 at 1:29 PM, Albe Laurenz <laurenz.albe@wien.gv.at> wrote:
> In your case, by using ''Cote d''''Ivoire''.

single quotes for string literals. So again: 'Cote d''lvoire'.

--
GJ

On Fri, Feb 05, 2010 at 10:13:21AM +0100, Stefan Schwarzer wrote:
> probably not too complicated, but although googling my way through
> many pages, I don't find the solution.
>
> I have a query which uses already an apostrophe to escape something
> else:

Not sure if "dollar quoting" may be easier for you here.  Have a look
at:

 http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-DOLLAR-QUOTING

--
  Sam  http://samason.me.uk/