我想知道一个Postgres存储函数如何返回一个表,具有标识列.我使用返回setof returnType：
-- create employeeSearchResult returnType
create type employeeAllReturnType as
(
id bigserial,"positionId" integer,"subjectId" bigint,"dateEngaged" date,"nextKin" text,"nrcNo" text,dob date,father text,mother text,wife text,"userId" integer,"statusId" integer,"mainCode" text,"subCode" text
);
-- Search for emmployee by name
CREATE OR REPLACE FUNCTION "employee_search_by_name"(employeeNameIN text)
returns setof employeeAllReturnType as
$$
declare
results record;
resultsRow employee%rowtype;
nameIn text;
begin
nameIn = employeeNameIN || '%';
for results in select
employee.id,-- bigserial NOT NULL,employee."positionId",-- integer,employee."subjectId",-- bigint NOT NULL,employee."dateEngaged",-- date,employee."nextKin",-- text,employee."nrcNo",employee.dob,employee.father,employee.mother,employee.wife,employee."userId",-- integer NOT NULL,employee."statusId",employee."mainCode",-- character(5) NOT NULL,employee."subCode"-- character(10),from employee,subject where employee."subjectId" = subject.id and (subject.name1 ILIKE nameIn OR subject.name2 ILIKE nameIn OR subject.name3 ILIKE nameIn OR subject.name4 ILIKE nameIn) loop
return next results;
end loop;
end;
$$language 'plpgsql';
并返回table()：
-- Search for emmployee by name
CREATE OR REPLACE FUNCTION "employee_search_by_name"(employeeNameIN text)
returns table (id bigserial,position integer,subject bigint,date_engaged date,next_kin text,nrc_no text,user_id integer,status_id integer,main_code text,sub_code text) as
$$
declare
results record;
resultsRow employee%rowtype;
nameIn text;
begin
nameIn = employeeNameIN || '%';
for results in select
employee.id,subject where employee."subjectId" = subject.id and (subject.name1 ILIKE nameIn OR subject.name2 ILIKE nameIn OR subject.name3 ILIKE nameIn OR subject.name4 ILIKE nameIn) loop
return next results;
end loop;
end;
$$language 'plpgsql';
但都有以下格式的输出：
"(1,1,2011-12-01,Timea,fg1254,1981-12-27,moses,sarada,timea,"ADM ","1 ")" "(37,3,10,2011-11-11,s,"OP ","1 ")"
"1";1;1;"2011-12-01";"Timea";"fg1254";"1981-12-27";"moses";"sarada";"timea";1;1;"ADM ";"1 "
这样从前端处理结果数据就不需要一个解析器.
你应该像这样查询你的函数：
SELECT * FROM employee_search_by_name('Bob');
另外,为了简化您的功能,您可以查看RETURN QUERY EXECUTE ...的结构.并且不需要引用plpgsql关键字.
