Блог

Главная - Вопросы по программированию - Проблемы с функцией перепроектирования SQL пространственных данных

Проблемы с функцией перепроектирования SQL пространственных данных

#sql #postgresql #gis #postgis #sql-function

#sql #postgresql #ГИС #postgis #sql-функция

Вопрос:

Здравствуйте, я только изучаю PostGIS и, следовательно, postgresql (9.1) и пытаюсь сэкономить время, копируя один и тот же код снова и снова, создавая функцию sql для перепроектирования некоторых пространственных данных. 

 Create Function reproject_shapefile(text,text,numeric) returns void as $$

    -- Reprojects shapefiles given that they follow the pattern "gid * the_geom"

    CREATE TABLE $2 AS
        SELECT *, ST_Transform(the_geom,$3) AS the_geom2
        FROM $1;
    Alter table $2 add Primary Key (gid);
    Alter table $2 drop column the_geom;
    Alter table $2 rename column the_geom2 to the_geom;
$$ Language SQL;

Я прочитал документы, в которых указано, как это сделать, но каждый раз, когда я пытаюсь создать функцию из редактора sql в pgAdmin, я получаю следующую ошибку:

 ERROR:  syntax error at or near "$2"
LINE 5:     CREATE TABLE $2 AS
                     ^

********** Error **********

ERROR: syntax error at or near "$2"
SQL state: 42601
Character: 175

В отличие от сообщений об ошибках в python, это не говорит мне абсолютно ничего полезного, поэтому я надеюсь, что кто-нибудь сможет указать мне правильное направление, как исправить эту ошибку.

Если есть какой-то способ выполнить эту же функцию с использованием python, не стесняйтесь размещать это в качестве решения вместо / а также, поскольку синтаксис python мне намного проще понять, чем древний SQL.

Любая помощь была бы высоко оценена!

Комментарии:

1. Почему вы хотите создать таблицу в этой функции? Разве вы не можете просто удалить строки из таблицы и вставить новые?

Ответ №1:

Вы не можете написать динамический SQL в этой форме. Параметры могут передаваться только в значениях, а не в идентификаторах. Что-то подобное невозможно в функции SQL:

 CREATE TABLE $2 AS

Для этого вам нужно написать функцию plpgsql и использовать EXECUTE . Может выглядеть следующим образом:

 CREATE OR REPLACE FUNCTION reproject_shapefile(text, text, numeric)
  RETURNS void as $$
BEGIN

EXECUTE '
   CREATE TABLE ' || quote_ident($2) || ' AS
   SELECT *, ST_Transform(the_geom,$1) AS the_geom2
   FROM  ' || quote_ident($1)
USING $3;

EXECUTE 'ALTER TABLE ' || quote_ident($2) || ' ADD PRIMARY KEY (gid)';
EXECUTE 'ALTER TABLE ' || quote_ident($2) || ' DROP COLUMN the_geom';
EXECUTE 'ALTER TABLE ' || quote_ident($2) || ' RENAME column the_geom2 TO the_geom';

END;
$$ Language plpgsql;

Основные моменты

  • plpgsql функция, не sql
  • EXECUTE любой запрос с динамическими идентификаторами
  • используйте quote_ident для защиты от SQLi
  • передавайте значения с помощью предложения USING, чтобы избежать безумного приведения и цитирования.