Обсуждение: Quotes, double quotes...

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

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

On nie, wrz 29, 2013 at 02:09:54 +0100, António M. Rodrigues wrote:
> 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("


" character is not for strings - it's for identifiers.

if you want to have string within string you have following options:
a. multiply ' - i.e. use '' (not ")
b. use $ quotation

so you can:
execute 'whatever ''sub string'' anything ';
or
execute 'whatever $sub$sub string$sub$';

depesz

--
The best thing about modern society is how easy it is to avoid contact with it.
                                                             http://depesz.com/