天天看点
返回

GreenPlum（postgreSQL）自定义查询函数GreenPlum（postgreSQL）自定义查询函数

GreenPlum（postgreSQL）自定义查询函数

具体可以参考postgreSQL的官网：自定义函数and others

1 基本语法如下 

CREATE [OR REPLACE] FUNCTION name    
    ( [ [argmode] [argname] argtype [ { DEFAULT | = } defexpr ] [, ...] ] )
      [ RETURNS { [ SETOF ] rettype 
        | TABLE ([{ argname argtype | LIKE other table }
          [, ...]])
        } ]
    { LANGUAGE langname
    | IMMUTABLE | STABLE | VOLATILE
    | CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
    | [EXTERNAL] SECURITY INVOKER | [EXTERNAL] SECURITY DEFINER
    | COST execution_cost
    | SET configuration_parameter { TO value | = value | FROM CURRENT }
    | AS 'definition'
    | AS 'obj_file', 'link_symbol' } ...
    [ WITH ({ DESCRIBE = describe_function
           } [, ...] ) ]

gp的自定义函数可以接收基本数据类型和复合类型的参数、也可以返回基本数据类型和符合类型的值，查询函数只能在查询语句中使用，实际上gp还支持c语言函数、程序函数、内置函数等4种类型的函数自定义，具体可以参考pg函数，这里我们只讨论 select query function，用在查询语句中。函数默认返回

具体用法如下：

--创建一个函数clean_up()，这是一个不需要参数的函数，也没有返回值，language指定为SQL
CREATE FUNCTION clean_emp() RETURNS void AS '
    DELETE FROM emp
        WHERE salary < 0;
' LANGUAGE SQL;

--使用自定义函数查询
SELECT clean_emp();

 clean_emp
-----------

(1 row)

1.1 自定义函数要求

pg的自定函数除了关键字以外，函数主体需要被包含在**’ '中或者 **,

--以下2种方式都可以创建一个函数
CREATE FUNCTION one() RETURNS integer AS $$
    SELECT 1 AS result;
$$ LANGUAGE SQL;

-- Alternative syntax for string literal:
CREATE FUNCTION one() RETURNS integer AS '
    SELECT 1 AS result;
' LANGUAGE SQL;

SELECT one();

2 自定义查询函数的类型

2.1 基本数据类型 

--只返回查询的第一行
CREATE FUNCTION test_add(v1 integer,v2 integer) returns integer AS $$
	SELECT $1 + $2; --其中$1代表第一个参数
$$ LANGUAGE SQL;

SELECT test_ass(1,2)
>>3

2.2 复合数据类型 

--通过表来定义一个复合类型
CREATE TABLE fake_body(
	name varchar,
  id int2,
  salary numeric
);

--创建函数
CREATE FUNCTION new_fake_body() RETURNS fake_body --OUT声明的是返回的类型 
AS $$
	SELECT 
		"none" AS name,
		12 AS id,
		12.2 AS salary;
		
	--or
	SELECT ROW("none",12,12.3)::fake_body;	
$$ LANGUAGE SQL;

--使用函数,返回的参数不需要指定
SELECT new_fake_body();

2.3 带输出参数的类型 

CREATE FUNCTION FUNC1(IN a integer,IN b integer,OUT c integer,OUT d long) AS $$
	SELECT a+b , a*b;
$$ LANGUAGE SQL;


SELECT func1(1,2);

>>>>>>
3 | 2

2.4 以table为数据源的类型，类似于子查询 

CREATE TABLE foo (fooid int, foosubid int, fooname text);
INSERT INTO foo VALUES (1, 1, 'Joe');
INSERT INTO foo VALUES (1, 2, 'Ed');
INSERT INTO foo VALUES (2, 1, 'Mary');

CREATE FUNCTION getfoo(int) RETURNS foo AS $$
    SELECT * FROM foo WHERE fooid = $1;
$$ LANGUAGE SQL;

SELECT *, upper(fooname) FROM getfoo(1) AS t1;

>>>>>>>>>>>>>>>>>>>>
 fooid | foosubid | fooname | upper
-------+----------+---------+-------
     1 |        1 | Joe     | JOE
(1 row)

2.5 返回数据集Sets的类型

如果在创建函数的时候，不指定

RETURNS SETOF record

,那么默认值返回查询到的第一行数据，如果没有就是null，如果我们只需要查询一个字段的多行，我们只需要将record换成该行的数据类型如

RETURNS SETOF integer

。

CREATE FUNCTION getfoo(int) RETURNS SETOF foo AS $$
    SELECT * FROM foo WHERE fooid = $1;
$$ LANGUAGE SQL;

SELECT * FROM getfoo(1) AS t1;

>>>>>>>>>>>>
 fooid | foosubid | fooname
-------+----------+---------
     1 |        1 | Joe
     1 |        2 | Ed
(2 rows)
           
--显示所有name的子集
SELECT * FROM nodes;
   name    | parent
-----------+--------
 Top       |
 Child1    | Top
 Child2    | Top
 Child3    | Top
 SubChild1 | Child1
 SubChild2 | Child1
(6 rows)

CREATE FUNCTION listchildren(text) RETURNS SETOF text AS $$
    SELECT name FROM nodes WHERE parent = $1
$$ LANGUAGE SQL STABLE;

SELECT * FROM listchildren('Top');
 listchildren
--------------
 Child1
 Child2
 Child3
(3 rows)

SELECT name, child FROM nodes, LATERAL listchildren(name) AS child;
  name  |   child
--------+-----------
 Top    | Child1
 Top    | Child2
 Top    | Child3
 Child1 | SubChild1
 Child1 | SubChild2
(5 rows)

2.6 接收、返回多态类型anyelement,anyarray,anynoarray,anyenum

需要返回一个多态类型的参数，必须包含至少1个多态类型的入参

 
CREATE FUNCTION is_greater(anyelement, anyelement) RETURNS boolean AS $$
    SELECT $1 > $2;
$$ LANGUAGE SQL;

SELECT is_greater(1, 2);
 is_greater
------------
 f
(1 row)

CREATE FUNCTION invalid_func() RETURNS anyelement AS $$
    SELECT 1;
$$ LANGUAGE SQL;
ERROR:  cannot determine result data type
DETAIL:  A function returning a polymorphic type must have at least one polymorphic argument.


CREATE FUNCTION dup (f1 anyelement, OUT f2 anyelement, OUT f3 anyarray)
AS 'select $1, array[$1,$1]' LANGUAGE sql;

SELECT * FROM dup(22);
 f2 |   f3
----+---------
 22 | {22,22}
(1 row)

3 自定义组合查询函数 

CREATE FUNCTION func() RETURNS [SETOF] record AS $$
  DECLARE --声明光标（变量），可以在这里初始化，也可以不初始化 :DECLARE
    name varchar;
    id int2;
    salary := 2020020;
		a int2;
		b int2;
  --开始函数的方法体
  BEGIN
  	--这里使用声明的变量
		FOR a,b IN (SELECT a,b FROM table_name);
		--然后对a,b的值进行循环
		LOOP 
	      --这个循环是针对上面的FOR的，可以通过RAISE NOTICE + % 的方式进行提示，还可以使用，输出等,%是占位符
	      RAISE NOTICE 'a = %  b = %' ,a,b ;
	      
	      --在函数体中任何位置还可以使用IF ..THEN.. END IF;
	      IF 1=1 THEN 
	      	a=1;
	      [ELSE 
	      	a=2;]
	      END IF;
	      
      --在循环中还可以嵌套循环
      
		END LOOP; --结束循环，每个循环对应一个结束的标记 ：END LOOP;
		
		RETURN;--跳出函数
  END;--结束方法体,
$$ LANGUAGE plpgsql
SET statement_mem='1024MB' --设置该函数的内存
--VOLATILE EXECUTE ON ALL SEGMENTS --在所有的segment上执行
; --标记function的语言种类
greenplum
上一篇: FunBox-4: CTF
下一篇: 线性结构的查找

继续阅读