Question

PostgreSQL语句报错

如图以及以下语句的报错信息，为什么还原不了表结构？CREATE TABLE "public"."sp_metadata_base_landsat8" ("id" varchar(100) COLLATE "default","fid" varchar(100) COLLATE "default","satelliteid" varchar(50) COLLATE "default","sensorid" varchar(50) COLLATE "default","importdate" timestamp(6),"simpleimg" varchar(500) COLLATE "default","thumbimg" varchar(500) COLLATE "default","url" varchar(500) COLLATE "default","topleftlatitude" varchar(100) COLLATE "default","topleftlongitude" varchar(100) COLLATE "default","toprightlatitude" varchar(100) COLLATE "default","toprightlongitude" varchar(100) COLLATE "default","bottomrightlatitude" varchar(100) COLLATE "default","bottomrightlongitude" varchar(100) COLLATE "default","bottomleftlatitude" varchar(100) COLLATE "default","bottomleftlongitude" varchar(100) COLLATE "default","fbounds" "public"."geometry","fgeometry" "public"."geometry","shoottime" timestamp(6))WITH (OIDS=FALSE);COMMENT ON COLUMN "public"."sp_metadata_base_landsat8"."id" IS '产品编号';COMMENT ON COLUMN "public"."sp_metadata_base_landsat8"."fid" IS '文件编号';COMMENT ON COLUMN "public"."sp_metadata_base_landsat8"."satelliteid" IS '卫星';COMMENT ON COLUMN "public"."sp_metadata_base_landsat8"."sensorid" IS '传感器';COMMENT ON COLUMN "public"."sp_metadata_base_landsat8"."importdate" IS '入库时间';COMMENT ON COLUMN "public"."sp_metadata_base_landsat8"."simpleimg" IS '缩略图';COMMENT ON COLUMN "public"."sp_metadata_base_landsat8"."thumbimg" IS '拇指图';COMMENT ON COLUMN "public"."sp_metadata_base_landsat8"."url" IS '数据存储路径';COMMENT ON COLUMN "public"."sp_metadata_base_landsat8"."topleftlatitude" IS '左上角纬度';COMMENT ON COLUMN "public"."sp_metadata_base_landsat8"."topleftlongitude" IS '左上角经度';COMMENT ON COLUMN "public"."sp_metadata_base_landsat8"."toprightlatitude" IS '右上角纬度';COMMENT ON COLUMN "public"."sp_metadata_base_landsat8"."toprightlongitude" IS '右上角经度';COMMENT ON COLUMN "public"."sp_metadata_base_landsat8"."bottomrightlatitude" IS '右下角纬度';COMMENT ON COLUMN "public"."sp_metadata_base_landsat8"."bottomrightlongitude" IS '右下角经度';COMMENT ON COLUMN "public"."sp_metadata_base_landsat8"."bottomleftlatitude" IS '左下角纬度';COMMENT ON COLUMN "public"."sp_metadata_base_landsat8"."bottomleftlongitude" IS '左下角经度';COMMENT ON COLUMN "public"."sp_metadata_base_landsat8"."fbounds" IS '四至范围';COMMENT ON COLUMN "public"."sp_metadata_base_landsat8"."fgeometry" IS '空间范围';COMMENT ON COLUMN "public"."sp_metadata_base_landsat8"."shoottime" IS '拍摄时间';CREATE INDEX "sp_metadata_base_landsat8_id_idx" ON "public"."sp_metadata_base_landsat8" USING btree ("id");CREATE INDEX "sp_metadata_base_landsat8_satelliteid_idx" ON "public"."sp_metadata_base_landsat8" USING btree ("satelliteid", "fbounds", "shoottime");CREATE INDEX "sp_metadata_base_landsat8_shoottime_idx" ON "public"."sp_metadata_base_landsat8" USING btree ("shoottime");

Answer 1

PostgreSQL的PL/pgSQL语言是支持动态SQL语句的(说execute immediate的是ECPG所支持的)。但是，要记得重要的一点: 是在PL/pgSQL语言中支持。而PL/pgSQL语言一个块结构的语言，它以begin ... end为块的开始与结束标识。这也就是说，要执行动态SQL语句，就必须放到begin ... end块中，而不要想实现一个单独的动态SQL语句。在SQL Server中，倒是可以轻松的实现，我们可以直接执行一个这样的动态SQL:
1execute sp_executesql N'select 1 as val'
而在PostgreSQL中，就不要有此想法了。当然，SQL Server的这种动态SQL语句的执行方法也有其局限与不便的地方。
在PL/pgSQL中，执行动态SQL的格式如下(摘录自说明文档):
1EXECUTE command-string [ INTO [STRICT] target ] [ USING expression [, ... ] ];
其中，
command-string就是要执行的动态SQL语句(一定要记住：这里是SQL语句，不是PL/pgSQL语句，像raise notice就不能使用)；
INTO子句是把SQL查询到的值赋给INTO指定的变量；
USING子句是前面的command-string中替代变量($1, $2, ...)的赋值；
示例：
123456789do $$declarev_c1 integer;v_c2 integer;beginexecute 'select count(*) as c1, count(*) as c2 from (select 1 as idx union select 11 as idx union select 21 as idx) s where idx > $1' into v_c1, v_c2using 10;raise notice '%, %', v_c1, v_c2;

追问

我想要答案。怎么解决这个问题，而不是为什么会出现这个问题。