PostgreSQL 表值函数

方法1
create type deptSon as (
mid varchar(36),
id varchar(36),
name varchar(200),
DeptParentId varchar(36)
);
 
CREATE OR REPLACE FUNCTION function2 () 
RETURNS SETOF deptSon AS 
$body$ 
SELECT id,id,name,parentId from dept where id='73684883-5F9C-44F3-8DAF-0037AB304A82'
union all
SELECT id,id,name,parentId from dept where id='73684883-5F9C-44F3-8DAF-0037AB304A82'; 
$body$ 
LANGUAGE 'sql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER; 

exampledb=> 
exampledb=> select function2 () ;
           function2
----------------------------------------------------------------------------------------------------------------------------------
 (73684883-5F9C-44F3-8DAF-0037AB304A82,73684883-5F9C-44F3-8DAF-0037AB304A82,人事行政部,04009320-7B51-4A6B-84E6-4BFDD4E9066A)
 ...
 (73684883-5F9C-44F3-8DAF-0037AB304A82,73684883-5F9C-44F3-8DAF-0037AB304A82,人事行政部,04009320-7B51-4A6B-84E6-4BFDD4E9066A)
(2 rows)
 
exampledb=> 
exampledb=> select * from function2 () ;
mid                  |id                   |name       |    deptparentid             
--------------------------------------+--------------------------------------+-----------------+--------------------------------------
 73684883-5F9C-44F3-8DAF-0037AB304A82 | 73684883-5F9C-44F3-8DAF-0037AB304A82 | 人事行政部 | 04009320-7B51-4A6B-84E6-4BFDD4E9066A
 ...
 73684883-5F9C-44F3-8DAF-0037AB304A82 | 73684883-5F9C-44F3-8DAF-0037AB304A82 | 人事行政部 | 04009320-7B51-4A6B-84E6-4BFDD4E9066A
(2 rows)
 

方法2
CREATE OR REPLACE FUNCTION GetSonDeptListById (varchar(36)) 
RETURNS SETOF deptSon AS 
$body$ 
WITH RECURSIVE deptSon (mid,id,name,parentId)  AS (
SELECT id as mid,id,name,parentId From Dept Where (id = $1) or ($1 is null)
UNION ALL
Select cte.mid,d.id,d.name,d.parentId from Dept d
Inner Join deptSon cte on cte.id=d.parentId
)select * from deptSon
$body$ 
LANGUAGE 'sql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER; 

CREATE OR REPLACE FUNCTION GetSonDeptListById () --设置默认值*
RETURNS SETOF deptSon AS 
$body$ 
select * from GetSonDeptListById(null);  
$body$ 
LANGUAGE 'sql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER; 

exampledb=> select * from GetSonDeptListById('04009320-7B51-4A6B-84E6-4BFDD4E9066A');                                    
                 mid                  |                  id                  |         name          |             deptparentid             
--------------------------------------+--------------------------------------+-----------------------+--------------------------------------
 04009320-7B51-4A6B-84E6-4BFDD4E9066A | 04009320-7B51-4A6B-84E6-4BFDD4E9066A | 广州                | 00000000-0000-0000-0000-000000000000
 ...
 04009320-7B51-4A6B-84E6-4BFDD4E9066A | 79FAD805-5B0F-4C45-B30B-D3CF86275978 | 天河                | E68F40DA-A88D-492A-9265-2A60678C8DC4
(10 rows)

方法3
CREATE OR REPLACE FUNCTION execute(filter varchar(36))
RETURNS TABLE (id varchar(36), name varchar(200))
AS $$
BEGIN
    RETURN QUERY EXECUTE 'SELECT id,name FROM dept where id = $1;' USING filter;
END;
$$ LANGUAGE PLPGSQL;

CREATE OR REPLACE FUNCTION execute(filter varchar(36),filter2 varchar(36))
RETURNS TABLE (Aid varchar(36), Aname varchar(200))
AS $$
BEGIN
    RETURN QUERY SELECT id,name FROM dept where id = $1 OR id = $2;
END;
$$ LANGUAGE PLPGSQL;

CREATE OR REPLACE FUNCTION execute(filter varchar(36),filter2 varchar(36))
RETURNS TABLE (Aid varchar(36), Aname varchar(200))
AS $$
BEGIN
    RETURN QUERY WITH RECURSIVE org AS (SELECT id,name FROM dept where id = $1 OR id = $2) SELECT * FROM org;
END;
$$ LANGUAGE PLPGSQL;