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