PostgreSQL 返回多行多列

首先,代码如下

CREATE TYPE type_employeespost AS(SelPost boolean, ID character varying(32), Name character varying(64));  //执行pgScript命令执行

CREATE OR REPLACE FUNCTION getemployeespost(userid character varying, userdept character varying)
  RETURNS SETOF type_employeespost AS
$BODY$SELECT CASE WHEN "temp"."E_ID" IS NULL THEN false ELSE true END as SelPost,"Posts"."ID","Posts"."Name"
FROM "Posts"
LEFT JOIN (SELECT "E_ID","P_ID" FROM "EmployeesPost" WHERE "EmployeesPost"."E_ID"=userid ) temp
    ON "Posts"."ID" = "temp"."P_ID" WHERE "Posts"."State" and "Posts"."Department"=userdept;$BODY$
  LANGUAGE sql VOLATILE; //执行查询即可。

调用

SELECT * from getemployeespost(
    'admin',
    'MM0001'
);

假如仍然按照pgAdmin生成的脚本

SELECT getemployeespost(
    'admin',
    'MM0001'
);

他仍然返回单列。

假如返回单行将SETOF 去掉即可，这个没有多大意义，要是返回单行不如使用参数了。