一个Postgres存储函数如何返回一个表 [英] How can a Postgres Stored Function return a table
问题描述
我想知道一个Postgres存储函数如何返回一个带有标识列的表。我使用了返回setof returnType：
$ p $ - create employeeSearchResult returnType
创建类型employeeAllReturnType as
（
id bigserial，
positionId整数，
subjectIdbigint，
dateEngaged日期，
nextKin文本，
nrcNo文本，
dob日期，
父文本，
母文本，
妻子文本，
userId整数，
statusId整数，
mainCode文本，
subCode文本
）;
$ b $ - 按名称搜索emmployee
创建或替换功能employee_search_by_name（employeeNameIN text）
返回setof employeeAllReturnType为
$$
申报
结果记录;
resultsRow员工％rowtype;
name in text;
begin
nameIn = employeeNameIN || ％;
for select
employee.id， - bigserial NOT NULL，
employee。positionId， - integer，
employee。subjectId， - bigint NOT NULL，
雇员。dateEngaged， - 日期，
员工。nextKin， - 文本，
员工。nrcNo， - 文本，
员工。 dob， - date，
employee.father， - text，
employee.mother， - text，
employee.wife， - text，
employee。userId - integer NOT NULL，
employeestatusId， - integer，
employee。mainCode， - character（5）NOT NULL，
employee。subCode - 字符（10），雇员
，雇员所在的主题。subjectId= subject.id和（subject.name1 ILIKE nameIn OR subject.name2 ILIKE nameIn OR subject.name3 ILIKE nameIn OR subject.name4 ILIKE nameIn ）循环
返回下一个结果;
结束循环;
end;
$$语言'plpgsql';
以及返回表格（）：
- 通过名称搜索emmployee
CREATE OR REPLACE FUNCTIONemployee_search_by_name（employeeNameIN text）
返回表（id bigserial，
position integer，
主题bigint，
date_engaged日期，
next_kin文本，
nrc_no文本，
dob日期，
父文字，
母文，
的妻子文本，
user_id整数，
status_id整数，
main_code文本，
sub_code文本）作为
$$
申报
结果记录;
resultsRow员工％rowtype;
name in text;
begin
nameIn = employeeNameIN || ％;
for select
employee.id， - bigserial NOT NULL，
employee。positionId， - integer，
employee。subjectId， - bigint NOT NULL，
雇员。dateEngaged， - 日期，
员工。nextKin， - 文本，
员工。nrcNo， - 文本，
员工。 dob， - date，
employee.father， - text，
employee.mother， - text，
employee.wife， - text，
employee。userId - integer NOT NULL，
employeestatusId， - integer，
employee。mainCode， - character（5）NOT NULL，
employee。subCode - 字符（10），雇员
，雇员所在的主题。subjectId= subject.id和（subject.name1 ILIKE nameIn OR subject.name2 ILIKE nameIn OR subject.name3 ILIKE nameIn OR subject.name4 ILIKE nameIn ）循环
返回下一个结果;
结束循环;
end;
$$语言'plpgsql';
但是两者都有以下格式的输出：
（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 ）
是否有我可以输出的结果，例如来自表格的选择结果
1; 1; 1;2011-12-01;Timea;fg1254; 1981-12-27;moses;sarada;timea; 1; 1;ADM;1
这样来自前端的处理结果数据不会需要解析器。
您应该查询你的函数是这样的：
$ b
SELECT * FROM employee_search_by_name（'Bob'）;
另外，为了简化您的功能，您可以查看 RETURN QUERY EXECUTE ... 构造。并且不需要引用 plpgsql 关键字。
I would like to find out how a Postgres stored function can return a table, with identified columns. I've used returns 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.
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 plpgsql keyword.
这篇关于一个Postgres存储函数如何返回一个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
