postgresql存储过程_多行多列_多表且结构不同

突然发现之前测的都只是单表，如果多表结构不同，返回类型该如何定义呢？

准备工作

经典用户——角色——关联表

--emp 用户表
CREATE TABLE "emp" (
  "id" int4 NOT NULL,
  "name" text NOT NULL,
  "age" int4 NOT NULL,
  "address" char(50),
  "salary" float4
);

INSERT INTO "emp" VALUES (1, 'Paul', 32, 'California', 20000);
INSERT INTO "emp" VALUES (2, 'ja', 33, 'aaaa', 10000);
INSERT INTO "emp" VALUES (3, 'ca', 55, 'ggss', 5000);
INSERT INTO "emp" VALUES (4, 'jj', 24, 'aasd', 80000);

ALTER TABLE "emp" ADD CONSTRAINT "company_pkey1" PRIMARY KEY ("id");

--company 公司表
CREATE TABLE "company" (
  "id" int4 NOT NULL,
  "name" varchar(255)
);

INSERT INTO "company" VALUES (1, 'bs');
INSERT INTO "company" VALUES (2, 'rj');

ALTER TABLE "company" ADD CONSTRAINT "company_pkey1" PRIMARY KEY ("id");

--ec 用户公司关联表
CREATE TABLE "ec" (
  "eid" int4 NOT NULL,
  "cid" int4 NOT NULL
);

INSERT INTO "ec" VALUES (1, 1);
INSERT INTO "ec" VALUES (2, 1);
INSERT INTO "ec" VALUES (3, 1);
INSERT INTO "ec" VALUES (4, 2);

实验

create type ectype as (
	eid int,
	ename text,
	cid int,
	cname varchar
);

• 单条sql版

CREATE OR REPLACE FUNCTION agetuser()
RETURNS SETOF ectype as $$
BEGIN
   return query (select e.id eid,e.name ename,c.id cid,c.name cname from emp e left join ec ON e.id = ec.eid left join company c ON ec.cid = c.id);
   return;
END;
$$
LANGUAGE PLPGSQL;

select * from agetuser();

• WITH版

CREATE OR REPLACE FUNCTION agetuser1()
RETURNS SETOF ectype as $$
BEGIN
   return query (
			WITH eResult AS (
				SELECT e.id empid,e.name empname FROM emp e
			),
			ecResult AS (
				SELECT e.*,ec.* FROM eResult e left join ec ON e.empid = ec.eid
			),
			cResult AS (
				SELECT ec.empid,ec.empname,c.id cid,c.name cname FROM ecResult ec left join company c ON ec.cid = c.id
			)
			SELECT * FROM cResult
	 );
   return;
END;
$$
LANGUAGE PLPGSQL;
select * from agetuser1();

1、创建一个有多表字段的复合类型
2、查询时给各字段取别名，一一对应复合类型里定义的属性