Question

Hello 我只是学习GIS,从而张贴了Gresql(9.1),并试图通过创建凯尔功能,重新预测一些空间数据,节省一些时间复制同一代码。

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的ql编辑那里产生职能时,我就收到以下错误:

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

与在座标上的错误信息不同,这告诉我绝对没有任何用处,因此,我希望有人能够向我说明如何纠正这一错误。

如果能够以普思松语来履行这一职能,那么我就能够自由地把这一解决办法作为解决办法,而花on的辛子比古的QL更容易理解。

任何帮助都将受到高度赞赏。

Answer 1

你不能用这种形式写活。参数只能通过t/em>,而不是identifiers。在一项职能中,不可能做到这一点:

CREATE TABLE $2 AS

You need to write a plpgsql function for that and use EXECUTE. Could look like this:

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;

Major points

plpgsql function, not sql
EXECUTE any query with dynamic identifiers
use quote_ident to safeguard against SQLi
pass in values with the USING clause to avoid casting and quoting madness.

Major points

友情链接