postgresql Postgres 存储函数如何返回表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10329247/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can a Postgres Stored Function return a table
提问by greatkalu
I would like to find out how a Postgres stored function can return a table, with identified columns. I've used returns setof returnType:
我想了解 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",-- text,
employee.dob,-- date,
employee.father,-- text,
employee.mother,-- text,
employee.wife,-- text,
employee."userId",-- integer NOT NULL,
employee."statusId",-- integer,
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';
and also return 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,
dob date,
father text,
mother text,
wife 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,-- bigserial NOT NULL,
employee."positionId",-- integer,
employee."subjectId",-- bigint NOT NULL,
employee."dateEngaged",-- date,
employee."nextKin",-- text,
employee."nrcNo",-- text,
employee.dob,-- date,
employee.father,-- text,
employee.mother,-- text,
employee.wife,-- text,
employee."userId",-- integer NOT NULL,
employee."statusId",-- integer,
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';
But both have outputs in the following formats:
但两者都有以下格式的输出：
"(1,1,1,2011-12-01,Timea,fg1254,1981-12-27,moses,sarada,timea,1,1,"ADM ","1 ")"
"(37,3,10,2011-11-11,s,s,2011-11-11,s,s,s,1,1,"OP ","1 ")"
Is there anyway in which I can have outputs such as those of a select result from a table?
无论如何，我可以有输出，例如从表中选择结果的输出吗？
"1";1;1;"2011-12-01";"Timea";"fg1254";"1981-12-27";"moses";"sarada";"timea";1;1;"ADM ";"1 "
Such that handling result data from the front end wont require a parser.
这样处理来自前端的结果数据就不需要解析器。
回答by vyegorov
You should query your function like this:
你应该像这样查询你的函数：
SELECT * FROM employee_search_by_name('Bob');
Also, to simplify your function, you might look into the RETURN QUERY EXECUTE ...construct. And there's no need to quote plpgsqlkeyword.
此外，为了简化您的功能，您可以查看RETURN QUERY EXECUTE ...构造。并且不需要引用plpgsql关键字。
