Re: Quotes, double quotes...

On 09/29/2013 06:09 AM, António M. Rodrigues wrote:
> Hi all,
>
> I have a problem with quotes which I can't find a solution.
>
> Inside a query string I have another query string; I used for the later
> double quotes, but it is still complaining
>
> The problem is with the function pgr_drivingdistance (from pgrouting
> extension).
>
> The code is the following:
>
> -----------------------------------------------------
> DO $$
> DECLARE
>      i integer;
> BEGIN
> FOR i IN (select nn from numeros)
> LOOP
> EXECUTE
> 'create table contagio' || i || ' as
> SELECT *
>   FROM pgr_drivingdistance("
>        SELECT gid AS id,
>            source,
>            target,
>            tempo::float8 AS cost
>        FROM lisboa",
>        ' || i || ' ,
>        30,
>        false,
>        false)
> ';
> END LOOP;
> END;
> $$;

>
> ERROR:  column "
>        SELECT gid AS id,
>            source,
>            target,
>    " does not exist
> LINE 3:  FROM pgr_drivingdistance("
>                                    ^
> QUERY:  create table contagio18 as
> SELECT *
>   FROM pgr_drivingdistance("
>        SELECT gid AS id,
>            source,
>            target,
>            tempo::float8 AS cost
>        FROM lisboa",
>        18 ,
>        30,
>        false,
>        false)

>
> I suspect the solution is probably simple; yet, I can't get i

I think you need two single quotes around the embedded sql string. What
is happening is that Postgres is seeing the sql string as an identifier
and is looking for a column of that name.

By way of example:

DO $$



BEGIN
RAISE NOTICE 'test is "good"';
END;
$$
language plpgsql;
NOTICE:  test is "good"


DO


DO $$





BEGIN


RAISE NOTICE 'test is ''good''';


END;


$$


language plpgsql;


NOTICE:  test is 'good'


DO

> Thanks in advance for any help.
>
> António


--
Adrian Klaver
adrian.klaver@gmail.com