Search Postgresql Archives

Re: Quotes, double quotes...

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.klaver@xxxxxxxxx>
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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux