greenplum⾃定义函数
CREATE [OR REPLACE] FUNCTION proname(argname argtype) [returns {[SETOF] rettype|TABLE(argname argtype|like other table)}] {language langname|IMMUTABLE|STABLE|VOLATILE}
1.CREATE OR REPLACE FUNCTION不允许改变已经存在的函数的返回值类型，如果要这样做，那么需要将原函数删除，然后重新创建新的函数。
2.任何被归为STABLE 和VOLATILE类型的函数不能再segment上执⾏，因为会导致segment实例之间的数据不⼀致。
3.如果该函数不⽀持返回⼀个值，则使⽤void作为返回类型。只有OUT和INOUT参数时，RETURNS语句可以被省略。
4.SETOF修饰符表明函数会返回⼀个item集合, ⽽不是⼀个item。
5.IMMUTABLE|STABLE|VOLATILE该属性会告诉查询优化器关于函数的⾏为如果省略, VOLATILE 为默认值.
IMMUTABLE说明函数不能够修改数据库，并且对于给定相同的参数值的情况总是返回相同的结果。
STABLE 说明函数不能修改数据库
VOLATILE 说明函数可以修改事件（event）, 所以没有做任何优化
6.CALLED ON NULL INPUT|RETURNS NULL ON NULL INPUT|STRICT
CALLED ON NULL INPUT (默认值)说明当⼀些参数为null时该函数会被调⽤。
RETURNS NULL ON NULL INPUT和 STRICT 说明函数总是返回null，
7.plpgsql 的动态语句格式“execute <varchar变量的sql语句>”plpgsql没有 execute [into <variable_list>] [using <variable_list>] 的写法
8.plpgsql 不使⽤游标，⽽是⽤ for 循环代替。
9.plpgsql 的异常信息变量: sqlstate,sqlerrm。没有sqlcode。ORACLE 的异常信息变量:sqlcode,sqlerrm。
10.查询过程编译过的 select prosrc from pg_proc where proname="存储过程名称"
Examples
⼀个简单的加法函数:
CREATE FUNCTION add(integer, integer) RETURNS integer
AS 'select $1 + $2;'
LANGUAGE SQL
IMMUTABLE
RETURNS NULL ON NULL INPUT;
增加⼀个整形数的值，使⽤参数的名字, 使⽤ PL/pgSQL:
CREATE OR REPLACE FUNCTION increment(i integer) RETURNS
integer AS $$
BEGIN
RETURN i + 1;
END;
$$ LANGUAGE plpgsql;
对于⼀个PL/pgSQL函数，增加每次查询时的segment主机内存：
CREATE OR REPLACE FUNCTION function_with_query() RETURNS
SETOF text AS $$
BEGIN
RETURN QUERY
EXPLAIN ANALYZE SELECT * FROM large_table;
END;
$$ LANGUAGE plpgsql
SET statement_mem='256MB';
连接 blog.csdn/jmx_bigdata/article/details/84316863
异常
create or replace function prc_test(in par1 text,in par2 numeric,in par3 integer)
returns void as $$
declare
v_msg varchar(4000);
begin
insert into table1 values(par1,par2,par3);
EXCEPTION
WHEN OTHERS THEN
v_msg := substr(SQLERRM, 1, 2000);
PERFORM my_err(ic_cendat, ic_jobid, ic_seq, v_msg);
END;
end;
$$ language plpgsql;
循环 while
CREATE OR REPLACE FUNCTION fun_gp_test_xxx_20181026(v_month int)
RETURNS INT AS
$BODY$
declare
v_num int;
v_count int;
begin
v_num := 0;
v_count := 1;
while v_count > 0 loop
v_num := v_num + 1;
select count(1) into v_count from temp_cwh_test_1106 where split_part(rate_item,';',v_num) <> '';
if v_count > 0 then
insert into temp_cwh_test_1106_02 select serv_id,usage_date,latn_id,split_part(rate_item,';',v_num) from temp_cwh_test_1106 where split_part(rate_item,';',v_num) <> ''; ---- 需要提前建好temp_cwh_test_1106_02表
end if;
end loop;
return 0;
end;
循环 for
说明: loop variable of loop over rows must be a record or row variable or list of scalar variables。
--创建函数
drop function zzhtest.fnc_test();
create or replace _test()
returns varchar as $$
declare
rec record;
vv_result varchar(200);
begin
for rec in (select brand_code,brand_name from zzhtest.t_brand ) loop
insert into zzhtest.t_brand_bak values(rec.brand_code,rec.brand_name);
end loop;
vv_result := 'successful';
return vv_result;
exception
when others then
vv_result := substr(sqlstate ||':'|| sqlerrm,1,200);
end;
$$ language plpgsql;
2. for i 2 loop end loop
drop function zzhtest.fnc_test();
create or replace _test()
returns integer as $$
declare
vi_sum integer default 0;
begin
for i in 1..100 loop --此处i⽆需定义
vi_sum := vi_sum + i;
end loop;
return vi_sum;
exception
when others then
return -1;greenplum数据库
end;
分⽀ if then elsif then else end if
drop function zzhtest.fnc_test(integer,integer);
create or replace _test(
in iv_a integer,
in iv_b integer
)
returns varchar as $$
declare
vv_result varchar(10);
begin
if iv_a = iv_b then
vv_result := iv_a ||'与'|| iv_b ||'相等';
elsif iv_a > iv_b then
vv_result := iv_a ||'⼤于'|| iv_b;
else
vv_result := iv_a ||'⼩于'|| iv_b;
end if;
return vv_result;
exception
when others then
vv_result := substr(sqlstate ||':'|| sqlerrm,1,200);
return vv_result;
end;
$$ language plpgsql;
【greenplum 的静态语句和动态语句】
drop function zzhtest.fnc_test(varchar,varchar);
create or replace function zzhtest.fnc_test(
in iv_brand_code varchar,
in iv_brand_name varchar
)
returns varchar as $$
declare vi_cnt integer;
declare vv_sql varchar(100);
declare vv_result varchar(200);
begin
--select into语句
select count(*) into vi_cnt from zzhtest.t_brand where brand_code = upper(iv_brand_code);
--greenplum⼦程序中没有 execute into 的写法
--vv_sql := ' select count(*) from zzhtest.t_brand where brand_code = upper('''||iv_brand_code||''')';
--execute vv_sql into vi_cnt;
if vi_cnt = 0 then
--insert into zzhtest.t_brand values(upper(iv_brand_code),upper(iv_brand_name)); --静态语句
vv_sql := ' insert into zzhtest.t_brand values(upper('''||iv_brand_code||'''),upper('''||iv_brand_name||'''))';
execute vv_sql; --动态语句
else
--update zzhtest.t_brand set brand_name = upper(iv_brand_name) where brand_code = upper(iv_brand_code); -- 静态语句vv_sql := 'update zzhtest.t_brand set brand_name = upper('||quote_literal(iv_brand_name)||') where brand_code =
upper('||quote_literal(iv_brand_code)||') ';
execute vv_sql; --动态语句
end if;
vv_result := 'successful';
return vv_result;
exception
when others then
vv_result := substr(sqlstate ||':'|| sqlerrm,1,200); --异常信息
end;
$$ language plpgsql;
指定表结构⽅式：
CREATE OR REPLACE FUNCTION ads.fv_repay_detail() RETURNS
TABLE( product_id bigint, real_date varchar, principal_tot NUMERIC, principal NUMERIC, interest NUMERIC) AS $$
SELECT product_id, real_date, principal_tot, principal, interest from tb_repay_plan
$$ LANGUAGE sql;
使⽤已有表的结构：
CREATE OR REPLACE FUNCTION table_name1 () RETURNS
SETOF table_name1 AS
$body$
SELECT * from table_name1;
$body$
LANGUAGE 'sql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论