[PG] postgresql 存储过程示例

忘是亡心i 2022-01-20 00:11 408阅读 0赞

PostgreSQL函数也称为PostgreSQL存储过程。 PostgreSQL函数或存储过程是存储在数据库服务器上并可以使用SQL界面调用的一组SQL和过程语句(声明，分配，循环，控制流程等)。 它有助于您执行通常在数据库中的单个函数中进行多次查询和往返操作的操作。

您可以在许多语言(如SQL，PL/pgSQL，C，Python等)中创建PostgreSQL函数

CREATE [OR REPLACE] FUNCTION function_name (arguments)   
    RETURNS return_datatype AS $variable_name$  
      DECLARE  
        declaration;  
        [...]  
      BEGIN  
        < function_body >  
        [...]  
        RETURN { variable_name | value }  
      END; LANGUAGE plpgsql;

### 参数说明 ###

*  `function_name`：指定函数的名称。
 *  `[OR REPLACE]`：是可选的，它允许您修改/替换现有函数。
 *  `RETURN`：它指定要从函数返回的数据类型。它可以是基础，复合或域类型，或者也可以引用表列的类型。
 *  `function_body`：`function_body`包含可执行部分。
 *  `plpgsql`：它指定实现该函数的语言的名称。

简单示例

1. 对数据进行检测并打标记

CREATE OR REPLACE FUNCTION "public"."pg_materules"(varchar)
      RETURNS "pg_catalog"."int4" AS $BODY$
      Declare
       tableName VARCHAR;
       sql VARCHAR;
       res int;
       Begin
    			tableName:=$1;	
    			res:=0;
    			---- 初始化表内容
    			sql = 'update '||tableName||' set err_message=''异常信息:'';';execute sql;
    			---- 
    			----数据表字段检查
    			sql:= 'select count(*) from information_schema.columns WHERE table_name = '''||tableName||''' and column_name = ''id'';';
    			execute sql into res;
    			if res is null or res=0 then
    				sql = 'update '||tableName||' set err_message=err_message||''ID字段缺失,'';';execute sql;
    			end if;
    				sql:= 'select count(*) from information_schema.columns WHERE table_name = '''||tableName||''' and column_name = ''name'';';
    			execute sql into res;
    			if res is null or res=0 then
    				sql = 'update '||tableName||' set err_message=err_message||''NAME字段缺失,'';';execute sql;
    			end if;
    				sql:= 'select count(*) from information_schema.columns WHERE table_name = '''||tableName||''' and column_name = ''citycode'';';
    			execute sql into res;
    			if res is null or res=0 then
    				sql = 'update '||tableName||' set err_message=err_message||''CITYCODE字段缺失,'';';execute sql;
    			end if;
    			----
    			---- 坐标检查
    			----
    			sql:= 'select count(*) from information_schema.columns WHERE table_name = '''||tableName||''' and column_name = ''lat'';';
    			execute sql into res;
    			if res is null or res=0 then
    				sql = 'update '||tableName||' set err_message=err_message||''LAT字段缺失,'';';execute sql;
    			else
    				----初始化坐标类型
    				sql = 'ALTER TABLE '||tableName||' ALTER COLUMN lat TYPE varchar(100);';execute sql;
    				sql:= 'update '||tableName||' set err_message=err_message||''缺失坐标LAT,'' where lat='''' or lat is null;';execute sql;
    			end if;
    			
    			sql:= 'select count(*) from information_schema.columns WHERE table_name = '''||tableName||''' and column_name = ''lon'';';
    			execute sql into res;
    			if res is null or res=0 then
    				sql = 'update '||tableName||' set err_message=err_message||''LON字段缺失,'';';execute sql;
    			else
    				----初始化坐标类型
    				sql = 'ALTER TABLE '||tableName||' ALTER COLUMN lon TYPE varchar(80);';execute sql;
    				sql:= 'update '||tableName||' set err_message=err_message||''缺失坐标LON,'' where lon='''' or lon is null;';execute sql;
    			end if;
    			----
    			---- 销售额字段检查处理
    			----
    			sql:= 'select count(*) from information_schema.columns WHERE table_name = '''||tableName||''' and column_name = ''sale'';';
    			execute sql into res;
    			if res is null or res=0 then
    				sql = 'update '||tableName||' set type_message=''空白门店'';';execute sql;
    			else
    				----初始化销售额类型
    				sql = 'ALTER TABLE '||tableName||' ALTER COLUMN sale TYPE INT;';execute sql;
    				sql = 'update '||tableName||' set type_message=''覆盖门店'' where type_message='''' or type_message is null;';execute sql;
    				sql:= 'update '||tableName||' set err_message=err_message||''销售额异常,'' where sale is null or sale <=0;';execute sql;
    			end if;
    			----
    			---- 业态检查
    			----
    			sql:= 'select count(*) from information_schema.columns WHERE table_name = '''||tableName||''' and column_name = ''yt'';';
    			execute sql into res;
    			if res is null or res=0 then
    					sql = 'update '||tableName||' set err_message=err_message||''YT字段缺失,'';';execute sql;
    			else
    				sql:= 'update '||tableName||' set err_message=err_message||''缺失业态,'' where yt = '''' or yt is null;';execute sql;
    			end if;
        return 0;
       end;
     $BODY$
      LANGUAGE plpgsql VOLATILE
      COST 100

2. 给某张表添加多个列

CREATE OR REPLACE FUNCTION "public"."pg_addcolumn"(_text, varchar)
      RETURNS "pg_catalog"."int4" AS $BODY$
      Declare
    		cols text[];
    		newTableName VARCHAR;
    		scount INTEGER;
    		sql VARCHAR;
       Begin
    		cols:=$1;
    		newTableName:=$2;
    		scount:= array_length(cols,1);
        for i in 1..scount loop
    			sql:='alter table '||newTableName||' add column '||quote_ident(cols[i])||' VARCHAR default null;';
    			EXECUTE sql;
        end LOOP;
        return 0;
       end;
     $BODY$
      LANGUAGE plpgsql VOLATILE
      COST 100

3.删除三个月以前的数据

CREATE OR REPLACE FUNCTION delete_ebd_compute_log_three_month_ago_data()
      RETURNS void AS
    $BODY$
        begin
        delete from ebd_compute_log where create_time < CURRENT_TIMESTAMP(0) ::                 TIMESTAMP WITHOUT TIME ZONE - interval '3 months';
        END;
    $BODY$
    LANGUAGE plpgsql VOLATILE;
    
    GRANT EXECUTE ON FUNCTION delete_ebd_compute_log_three_month_ago_data() TO public;