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@xxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
