Pgsql函数编写

最新推荐文章于 2024-03-20 17:46:34 发布

被秃头支配的恐惧

最新推荐文章于 2024-03-20 17:46:34 发布

阅读量2.3k

点赞数

分类专栏： sql 地理信息文章标签：数据库开发 postgresql

本文链接：https://blog.csdn.net/qq_43582366/article/details/127430634

版权

sql 同时被 2 个专栏收录

5 篇文章 0 订阅

订阅专栏

地理信息

3 篇文章 1 订阅

订阅专栏

先贴源码
这里是带有一个参数的函数，参数类型为字符串

CREATE OR REPLACE FUNCTION "public"."test"("topicname" text)
  RETURNS "pg_catalog"."void" AS $BODY$ DECLARE
	dkrows RECORD;
var_ID TEXT;
var_DJQDM TEXT;
count INT;
var_geom TEXT;
firstGeom TEXT;
NUMBER INT;
RESULT TEXT;
dk_name TEXT;
djzq_name TEXT;
bm_name TEXT;
BEGIN
dk_name:='public."'||topicname||'_YDYM_DK'||'"';
djzq_name:='public."'||topicname||'_YDYM_DJZQ'||'"';
bm_name:='public."'||topicname||'_YDYM_BM'||'"';
/*查询地块表中一地一码为空的数据*/
	FOR dkrows IN  EXECUTE 'SELECT * FROM '||dk_name||' WHERE	"YDYM" IS NULL 	OR "YDYM" = '''''loop
	/*叠加分析*/
		var_ID := dkrows."ID";
		var_geom :=st_asewkt(dkrows."the_geom");
		raise notice'var_geom=%', var_geom;
		EXECUTE  'SELECT RESULT."DJQDM" FROM( SELECT "DJQDM", st_area ( st_intersection ( the_geom, geomfromewkt('''||var_geom||''') )) AS area FROM '||djzq_name||' ORDER BY area DESC LIMIT 1 ) AS RESULT' INTO var_DJQDM;
		firstGeom = var_DJQDM;
	/*判断是否有相同地籍子区代码和阶段的地块数据，没有则从0开始。有则取最大+1*/
	EXECUTE 'SELECT COUNT( * )FROM '||bm_name||' WHERE split_part( '||bm_name||'."YDYM", '||'''GB'||''', 1 ) = '''||firstGeom||'''' INTO count;
	IF count=0 THEN
		NUMBER:=00000;
	ELSE
		EXECUTE 'SELECT to_char(to_number(split_part( '||bm_name||'."YDYM", '||'''GB'''||', 2 ),'||'''99999'''||')+1,'||'''00000'''||') FROM '||bm_name||' WHERE split_part( '||bm_name||'."YDYM", '||'''GB'''||', 1 ) = '''||firstGeom||''' ORDER BY "YDYM" DESC limit 1' INTO NUMBER;
	END IF;
	RESULT := firstGeom || 'GB' ||replace(to_char(NUMBER, '00000'),' ','');
	EXECUTE 'UPDATE '||dk_name||' SET "YDYM" = '''||RESULT||''' WHERE '||dk_name||'."ID" = '''||var_ID||'''';
	EXECUTE 'INSERT into '||bm_name||'("YDYM") VALUES('''||RESULT||''')';

END loop;

END $BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100

解析：
1.RETURNS "pg_catalog"."void" AS $BODY$ DECLARE代表函数返回结果和定义变量，void代表无返回结果，将void替换为text则代表返回文本
2.var_ID TEXT;代表定义一个TEXT类型的变量
3.dk_name:='public."'||topicname||'_YDYM_DK'||'"';为赋值语句，:=为赋值表达式，topicname为函数的参数，比如参数为A，那么当前表达式最后会将public.“A_YDYM_DK"赋值给dk_name。这里需要说明一下字符串拼接：在pgsql中字符串与字符串之间通过||拼接，在这里我需要在字符串最后面拼接双引号，所以最后面的写法就是||'”'，注意这里不是四个单引号，是两个单引号中间包裹一个双引号
4.FOR dkrows IN EXECUTE 'SELECT * FROM '||dk_name||' WHERE "YDYM" IS NULL OR "YDYM" = '''''loop这里为一个for循环，循环查询dk_name这张表中的满足条件的数据，这里需要注意的是：我使用了EXECUTE关键字将SQL语句作为一个字符串进行查询，因为我的表名dk_name是一个变量，如果不使用这种方式，选择直接传参，比如FOR dkrows IN SELECT * FROM dk_name....，这时候数据库是无法将dk_name识别为一个变量的，会直接当做一个表名进行查询，所以运行就会报错找不到当前关系。
5.EXECUTE 'SELECT RESULT."DJQDM" FROM( SELECT "DJQDM", st_area ( st_intersection ( the_geom, geomfromewkt('''||var_geom||''') )) AS area FROM '||djzq_name||' ORDER BY area DESC LIMIT 1 ) AS RESULT' INTO var_DJQDM;在pgsql自带的叠加分析函数中，接收的参数必须是空间参数Geometry，但是在字符串拼接中变量只能为字符串，所以我的做法是先定义参数为字符串，再将字符串转为空间参数格式
6.EXECUTE 'SELECT COUNT( * )FROM '||bm_name||' WHERE split_part( '||bm_name||'."YDYM", '||'''GB'||''', 1 ) = '''||firstGeom||'''' INTO count;当我们需要往表中插入数据时，SQL语句中字段对应的值需要加上单引号，不然pgsql会识别不到，所以我们需要在数值的前后各加三个单引号，比如'''||firstGeom||'''，这样最后拼接的字符串就会自动带上单引号。
总结
1.pgsql函数中，格式和java一样，每一行都必须以分号(;)结尾
2.pgsql函数中赋值表达式为:=
3.pgsql函数中IF语句格式：

IF 表达式 THEN
		语句;
	ELSE
		语句;
	END IF;

4.pgsql函数中将查询结果赋值给变量的格式为：EXECUTE 表达式字符串 INTO 变量
5.pgsql函数中打印变量格式为raise notice'变量名=%', 变量名;
6.pgsql函数中表名为变量时，必须使用EXECUTE关键字，将SQL以字符串拼接的方式运行，否则参数读取不到
7.pgsql函数中SQL数值包含单引号时，需要用三个单引号进行拼接