sql - 空间数据 SQL 重投影函数问题

Question

你好，我正在学习 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;

我阅读了指定如何执行此操作的文档，但每次我尝试从 pgAdmin 中的 sql 编辑器创建函数时，我都会收到以下错误:

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 更容易理解。

如有任何帮助，我们将不胜感激!

Answer 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 子句传入值以避免强制转换和引用疯狂。