1:多态sql
SQL函数可以声明为接受多态类型(anyelement 和 anyarray )的参数或返回多态类型的返回值。
CREATE OR REPLACE FUNCTION get_array(anyelement, anyelement) ---定义函数 RETURNS anyarray AS $$ SELECT ARRAY[$1, $2]; $$ LANGUAGE SQL; SELECT get_array(1,2) a1, get_array('a'::text,'b'::text) b1; ---调用函数 a1 | b1 -----------+----------- {1,2} | {a,b} CREATE OR REPLACE FUNCTION is_greater(anyelement, anyelement) RETURNS BOOLEAN AS $$ SELECT $1 > $2; $$ LANGUAGE SQL; SELECT is_greater(1.0, 4.5); f SELECT is_greater(7.0, 4.5); t DROP FUNCTION if exists get_array(anyelement, anyelement); CREATE OR REPLACE FUNCTION get_array (IN anyelement, IN anyelement, OUT anyelement, OUT anyarray) AS $$ SELECT $1, ARRAY[$1, $2]; $$ LANGUAGE SQL; SELECT get_array(4,5), get_array('c'::text, 'd'::text); get_array | get_array -------------+------------- (4,"{4,5}") | (c,"{c,d}")
2: 动态更新sql
--tablename 表名 --feildname 字段名数组 --feildvalue 字段值数组 --returnvalue 返回值 create or replace function f_update ( tablename text, condition text, feildname text[], feildvalue text[], out returnvalue text ) as $$ declare mysql text; myid integer; myresult integer; items text; counts integer; i integer; begin counts:=array_length(feildname,1); mysql:='update '||quote_ident(tablename)||' set '; for i in 1..counts loop mysql:= mysql||quote_ident(feildname[i])||'='''||feildvalue[i]||''','; end loop; mysql:=substring(mysql from 1 for (char_length(mysql)-1)) || ' where 1=1 '||condition; execute mysql; GET DIAGNOSTICS myresult:= ROW_COUNT; if myresult<>0 then returnvalue='{"success":"执行更新'||mysql||'成功！"}'; else returnvalue='{"success":"执行更新'||mysql||'失败！"}'; end if; end; $$ language plpgsql; -- 实际操作 create table test(id integer,name text,gen_time date); insert into test(id,name,gen_time) values(1,'office','2017-08-19'); select f_update('test',' and id=1','{name,gen_time}','{ssqhan,2017-08-20}');
3:动态更新sql改进
create or replace function f_update_all( tablename text, update_feilds text, condition_feilds text, out return_value text ) as $$ declare ex_sql text; recs record; _key text ; _value text; begin ex_sql:='update '||quote_ident(tablename)||' set '; --setting values for updated table for recs in select * from json_array_elements(update_feilds::json) loop _key := recs.value ->> 'feild_name'; _value := recs.value ->> 'feild_value' ; if json_typeof(recs.value -> 'feild_value') ='number' then ex_sql:=ex_sql|| _key || '=' || _value ||','; else ex_sql:=ex_sql|| _key || '='''|| (recs.value ->> 'feild_value') || ''','; end if; end loop; ex_sql:= substring(ex_sql from 0 for length(ex_sql)); --setting condition in where ex_sql:=ex_sql||' where 1=1'; for recs in select * from json_array_elements(condition_feilds::json) loop _key := recs.value ->> 'feild_name'; _value := recs.value ->> 'feild_value' ; if json_typeof(recs.value -> 'feild_value') ='number' then ex_sql:=ex_sql|| ' and ' || _key || '=' || _value ||','; else ex_sql:=ex_sql|| ' and ' || _key || '='''|| (recs.value ->> 'feild_value') || ''','; end if; end loop; ex_sql:= substring(ex_sql from 0 for length(ex_sql)); return_value:=ex_sql; end; $$ language plpgsql;
4:动态插入sql
drop function f_insert_all(text,text); create or replace function f_insert_all( table_name text, --表名 insert_feilds text, --需要插入的字段和字段 [{"feild_name":"pt_name","feild_value":"我是中国人"},{"feild_name":"pt_description","feild_value":"我骄傲"}] out return_value text --返回值 ) as $$ declare ex_sql text; recs record; _key text ; _value text; ex_result integer; _maxid integer; begin --检查是否插入重复数据,如果重复,返回重复提示 ex_result:=f_insert_check(table_name,insert_feilds); if ex_result<>0 then return_value:='{"ERROR":"插入操作失败！请检查是该记录是否已存在!"}'; else _maxid:=f_getnewid(table_name,'id'); ex_sql:='insert into '||quote_ident(table_name)||'(id'; _value:='('||_maxid; for recs in select * from json_array_elements(insert_feilds::json) loop ex_sql := ex_sql|| ',' || (recs.value ->> 'feild_name'); --insert feilds if json_typeof(recs.value -> 'feild_value') ='number' then _value:=_value||',' ||(recs.value ->> 'feild_value') ; --insert values numeric else _value:=_value||','''||(recs.value ->> 'feild_value')||'''' ; --insert values not numeric end if; end loop; ex_sql:=ex_sql||') values'||_value||')'; execute ex_sql; GET DIAGNOSTICS ex_result:= ROW_COUNT; if ex_result<>0 then return_value:='{"SUCCESS":"插入操作'||ex_sql||'成功！","id":'||_maxid||'}'; else return_value:='{"ERROR":"插入操作'||ex_sql||'失败！"}'; end if; --return_value:='{"ERROR":"插入操作'||ex_result||'失败！"}'; end if; end; $$ language plpgsql;
5:动态行转列
CREATE or REPLACE FUNCTION long_to_wide( table_name VARCHAR, row_name VARCHAR, cat VARCHAR, value_field VARCHAR) returns void as $$ /* table_name : 表名 row_name : 行名字段 cat : 转换为列名的字段 value_field : 转换为值的字段 */ DECLARE v_sql text; arow record; value_type VARCHAR; BEGIN v_sql=' drop table if exists temp_table; CREATE TABLE temp_table as SELECT distinct '||cat||' as col from '||table_name||' order by '||cat; execute v_sql; v_sql=' SELECT t.typname AS type FROM pg_class c ,pg_attribute a ,pg_type t WHERE c.relname = lower('''||table_name||''') and a.attnum > 0 and a.attrelid = c.oid and a.atttypid = t.oid and a.attname='''||value_field||''' ORDER BY a.attnum '; execute v_sql into value_type;--获取值字段的数据类型 v_sql='select '||row_name; IF value_type in ('numeric','int8','int4','int')--判断值字段是否是数值型 THEN FOR arow in (SELECT col FROM temp_table) loop v_sql=v_sql||' ,sum(case '||cat||' when '''||arow.col||''' then '||value_field||' else 0 end) '||cat||'_'||arow.col; end loop; ELSE FOR arow in (SELECT col FROM temp_table) loop v_sql=v_sql||' ,string_agg((case '||cat||' when '''||arow.col||''' then '||value_field||' else '''' end),'''') '||cat||'_'||arow.col; end loop; END IF; v_sql=' drop table if exists '||table_name||'_wide; CREATE TABLE '||table_name||'_wide as '||v_sql||' from '||table_name||' group by '||row_name||'; drop table if exists temp_tabl '; execute v_sql; end; $$ LANGUAGE plpgsql; SELECT long_to_wide('Student_grade', '姓名','课程', '等级') ---调用函数 生成的表名为Student_grade_wide
6:静态行转列
CREATE EXTENSION tablefunc; select * from crosstab('select s_name::text, s_class, c_value from test1 order by 1,2') as ct (姓名 text,"化学" numeric,"数学" numeric,"物理" numeric,"英语" numeric,"语文" numeric
7:generate_series函数
SELECT * FROM generate_series('2008-03-01 00:00'::timestamp, '2020-03-04 12:00', '1 month') SELECT * FROM generate_series('2008-03-01 00:00'::timestamp, '2020-03-04 12:00', '10 hours'); SELECT current_date + s.a AS dates FROM generate_series(0,14,1) AS s(a); SELECT current_date - s.a AS dates FROM generate_series(0,14,1) AS s(a); SELECT to_char(generate_series('2008-03-01'::date,'2008-03-04', '1 days'),'yyyy-MM-dd');
8:json函数
select * from json_each('{"a":"foo", "b":"bar"}') ; select * from json_each_text('{"a":"foo", "b":"bar"}') ; select * from json_extract_path('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','f2'); select * from jsonb_pretty('[{"f1":1,"f2":null},2,null,3]'); select * from jsonb_set('[{"f1":1,"f2":null},2]', '{0,f3}','[2,3,4]'); select * from json_array_elements_text('["foo", "bar"]'); select * from json_array_elements('[1,true, [2,false]]'); select * from json_object_keys('{"f1":"abc","f2":{"f3":"a", "f4":"b"}}');
