Quotes, double quotes...

[António M. Rodrigues <amcrgrodrigues@xxxxxxxxx>]

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;
$$;
------------------------------------------------------

I get the following error:
-----------------------------------------------------
NOTICE:  identifier "
      SELECT gid AS id,
          source,
          target,
          tempo::float8 AS cost
      FROM lisboa" will be truncated to "
      SELECT gid AS id,
          source,
          target,
  "
CONTEXT:  SQL statement "create table contagio18 as
SELECT *
 FROM pgr_drivingdistance("
      SELECT gid AS id,
          source,
          target,
          tempo::float8 AS cost
      FROM lisboa",
      18 ,
      30,
      false,
      false)
"
PL/pgSQL function "inline_code_block" line 7 at EXECUTE statement


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)

CONTEXT:  PL/pgSQL function "inline_code_block" line 7 at EXECUTE statement


********** Error **********

ERROR: column "
      SELECT gid AS id,
          source,
          target,
  " does not exist
SQL state: 42703
Context: PL/pgSQL function "inline_code_block" line 7 at EXECUTE statement
-------------------------------------------------------------------------------------------

I suspect the solution is probably simple; yet, I can't get it.

Thanks in advance for any help.

António