Re: Quotes, double quotes...

On 09/29/2013 08:31 AM, António M. Rodrigues wrote:
> Thanks for your reply.
>
> If I substitute double quotes with single quotes (or with double dollar
> sign",
> and run the code:
>
> 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 error:
>
> --------------------------------------------------------------------------------------------
> ERROR:  syntax error at or near "SELECT"
> LINE 11:       SELECT gid AS id,
>                 ^
>
>
> ********** Error **********
>
> ERROR: syntax error at or near "SELECT"
> SQL state: 42601
> Character: 165
> ---------------------------------------------------------------------------------------------
>
> I'm probably missing something simple, but what?

I probably was not clear enough. You need doubled single quotes:

'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)
'


You might also want to take a look at this section of the docs:

http://www.postgresql.org/docs/9.3/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

In particular the part on using quote_literal and quote_ident.

>
> António

--
Adrian Klaver
adrian.klaver@gmail.com