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.klaver@gmail.com