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(dot)klaver(at)gmail(dot)com