Re: Quotes, double quotes...

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?

António

2013/9/29 Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com>

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