SQL 使用 PL/pgSQL 在 PostgreSQL 中返回多个字段作为记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4547672/
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
Return multiple fields as a record in PostgreSQL with PL/pgSQL
提问by skyeagle
I am writing a SP, using PL/pgSQL.
I want to return a record, comprised of fields from several different tables. Could look something like this:
我正在使用 PL/pgSQL 编写 SP。
我想返回一个记录，由几个不同表中的字段组成。可能看起来像这样：
CREATE OR REPLACE FUNCTION get_object_fields(name text)
RETURNS RECORD AS $$
BEGIN
-- fetch fields f1, f2 and f3 from table t1
-- fetch fields f4, f5 from table t2
-- fetch fields f6, f7 and f8 from table t3
-- return fields f1 ... f8 as a record
END
$$ language plpgsql;
How may I return the fields from different tables as fields in a single record?
如何将不同表中的字段作为单个记录中的字段返回？
[Edit]
[编辑]
I have realized that the example I gave above was slightly too simplistic. Some of the fields I need to be retrieving, will be saved as separate rows in the database table being queried, but I want to return them in the 'flattened' record structure.
我意识到我上面给出的例子有点过于简单了。我需要检索的一些字段将在被查询的数据库表中保存为单独的行，但我想在“扁平化”记录结构中返回它们。
The code below should help illustrate further:
下面的代码应该有助于进一步说明：
CREATE TABLE user (id int, school_id int, name varchar(32));
CREATE TYPE my_type (
user1_id int,
user1_name varchar(32),
user2_id int,
user2_name varchar(32)
);
CREATE OR REPLACE FUNCTION get_two_users_from_school(schoolid int)
RETURNS my_type AS $$
DECLARE
result my_type;
temp_result user;
BEGIN
-- for purpose of this question assume 2 rows returned
SELECT id, name INTO temp_result FROM user where school_id = schoolid LIMIT 2;
-- Will the (pseudo)code below work?:
result.user1_id := temp_result[0].id ;
result.user1_name := temp_result[0].name ;
result.user2_id := temp_result[1].id ;
result.user2_name := temp_result[1].name ;
return result ;
END
$$ language plpgsql
采纳答案by a_horse_with_no_name
You need to define a new type and define your function to return that type.
您需要定义一个新类型并定义您的函数以返回该类型。
CREATE TYPE my_type AS (f1 varchar(10), f2 varchar(10) /* , ... */ );
CREATE OR REPLACE FUNCTION get_object_fields(name text)
RETURNS my_type
AS
$$
DECLARE
result_record my_type;
BEGIN
SELECT f1, f2, f3
INTO result_record.f1, result_record.f2, result_record.f3
FROM table1
WHERE pk_col = 42;
SELECT f3
INTO result_record.f3
FROM table2
WHERE pk_col = 24;
RETURN result_record;
END
$$ LANGUAGE plpgsql;
If you want to return more than one record you need to define the function as returns setof my_type
如果要返回多个记录，则需要将该函数定义为 returns setof my_type
Update
更新
Another option is to use RETURNS TABLE()instead of creating a TYPEwhich was introduced in Postgres 8.4
另一种选择是使用RETURNS TABLE()而不是创建TYPEPostgres 8.4 中引入的
CREATE OR REPLACE FUNCTION get_object_fields(name text)
RETURNS TABLE (f1 varchar(10), f2 varchar(10) /* , ... */ )
...
回答by Sean
Don't use CREATE TYPEto return a polymorphic result. Use and abuse the RECORD typeinstead. Check it out:
不要使用CREATE TYPE返回多态结果。改为使用和滥用RECORD 类型。一探究竟：
CREATE FUNCTION test_ret(a TEXT, b TEXT) RETURNS RECORD AS $$
DECLARE
ret RECORD;
BEGIN
-- Arbitrary expression to change the first parameter
IF LENGTH(a) < LENGTH(b) THEN
SELECT TRUE, a || b, 'a shorter than b' INTO ret;
ELSE
SELECT FALSE, b || a INTO ret;
END IF;
RETURN ret;
END;$$ LANGUAGE plpgsql;
Pay attention to the fact that it can optionally return twoor threecolumns depending on the input.
请注意，它可以根据输入选择返回两列或三列。
test=> SELECT test_ret('foo','barbaz');
test_ret
----------------------------------
(t,foobarbaz,"a shorter than b")
(1 row)
test=> SELECT test_ret('barbaz','foo');
test_ret
----------------------------------
(f,foobarbaz)
(1 row)
This does wreak havoc on code, so do use a consistent number of columns, but it's ridiculously handy for returning optional error messages with the first parameter returning the success of the operation. Rewritten using a consistent number of columns:
这确实对代码造成了严重破坏，因此请使用一致数量的列，但是返回可选错误消息并返回操作成功的第一个参数非常方便。使用一致的列数重写：
CREATE FUNCTION test_ret(a TEXT, b TEXT) RETURNS RECORD AS $$
DECLARE
ret RECORD;
BEGIN
-- Note the CASTING being done for the 2nd and 3rd elements of the RECORD
IF LENGTH(a) < LENGTH(b) THEN
ret := (TRUE, (a || b)::TEXT, 'a shorter than b'::TEXT);
ELSE
ret := (FALSE, (b || a)::TEXT, NULL::TEXT);
END IF;
RETURN ret;
END;$$ LANGUAGE plpgsql;
Almost to epic hotness:
几乎达到史诗般的热度：
test=> SELECT test_ret('foobar','bar');
test_ret
----------------
(f,barfoobar,)
(1 row)
test=> SELECT test_ret('foo','barbaz');
test_ret
----------------------------------
(t,foobarbaz,"a shorter than b")
(1 row)
But how do you split that out in to multiple rows so that your ORM layer of choice can convert the values in to your language of choice's native data types? The hotness:
但是如何将其拆分为多行，以便您选择的 ORM 层可以将值转换为您选择的语言的本机数据类型？热度：
test=> SELECT a, b, c FROM test_ret('foo','barbaz') AS (a BOOL, b TEXT, c TEXT);
a | b | c
---+-----------+------------------
t | foobarbaz | a shorter than b
(1 row)
test=> SELECT a, b, c FROM test_ret('foobar','bar') AS (a BOOL, b TEXT, c TEXT);
a | b | c
---+-----------+---
f | barfoobar |
(1 row)
This is one of the coolest and most underused features in PostgreSQL. Please spread the word.
这是 PostgreSQL 中最酷和最未被充分利用的功能之一。请广而告之。
回答by Erwin Brandstetter
To return a single row
返回单行
Simpler with OUTparameters:
更简单的OUT参数：
CREATE OR REPLACE FUNCTION get_object_fields(_school_id int
, OUT user1_id int
, OUT user1_name varchar(32)
, OUT user2_id int
, OUT user2_name varchar(32)) AS
$func$
BEGIN
SELECT INTO user1_id, user1_name
u.id, u.name
FROM users u
WHERE u.school_id = _school_id
LIMIT 1; -- make sure query returns 1 row - better in a more deterministic way?
user2_id := user1_id + 1; -- some calculation
SELECT INTO user2_name
u.name
FROM users u
WHERE u.id = user2_id;
END
$func$ LANGUAGE plpgsql;
Call:
称呼：
SELECT * FROM get_object_fields(1);
You don't needto create a type just for the sake of this plpgsql function. It maybe useful if you want to bind multiple functions to the same composite type. Else,
OUTparameters do the job.There is no
RETURNstatement.OUTparameters are returned automatically with this form that returns a single row.RETURNis optional.Since
OUTparameters are visible everywhere inside the function body (and can be used just like any other variable), make sure to table-qualify columns of the same name to avoid naming conflicts! (Better yet, use distinct names to begin with.)
你并不需要，只是这种PLPGSQL功能的缘故创建一个类型。如果您想将多个函数绑定到同一个复合类型，这可能很有用。否则，
OUT参数完成工作。没有
RETURN声明。OUT使用这种返回单行的形式自动返回参数。RETURN是可选的。由于
OUT参数在函数体内的任何地方都是可见的（并且可以像任何其他变量一样使用），请确保对同名的列进行表限定以避免命名冲突！（更好的是，使用不同的名称开始。）
Simpler yet - also to return 0-n rows
更简单 - 也返回 0-n 行
Typically, this can be simpler and faster if queries in the function body can be combined. And you canuse RETURNS TABLE()(since Postgres 8.4, long before the question was asked) to return 0-n rows.
通常，如果可以组合函数体中的查询，这会更简单、更快。您可以使用RETURNS TABLE()（自 Postgres 8.4 起，早在提出问题之前）返回 0-n 行。
The example from above can be written as:
上面的例子可以写成：
CREATE OR REPLACE FUNCTION get_object_fields2(_school_id int)
RETURNS TABLE (user1_id int
, user1_name varchar(32)
, user2_id int
, user2_name varchar(32)) AS
$func$
BEGIN
RETURN QUERY
SELECT u1.id, u1.name, u2.id, u2.name
FROM users u1
JOIN users u2 ON u2.id = u1.id + 1
WHERE u1.school_id = _school_id
LIMIT 1; -- may be optional
END
$func$ LANGUAGE plpgsql;
Call:
称呼：
SELECT * FROM get_object_fields2(1);
RETURNS TABLEis effectively the same as having a bunch ofOUTparameters combined withRETURNS SETOF record, just shorter.The major difference: this function can return 0, 1 or many rows, while the first version alwaysreturns 1 row.
AddLIMIT 1like demonstrated to only allow 0 or 1 row.RETURN QUERYis simple way to return results from a query directly.
You can use multiple instances in a single function to add more rows to the output.
RETURNS TABLE实际上与将一堆OUT参数与 结合起来是一样的RETURNS SETOF record，只是更短。主要区别：此函数可以返回 0、1 或多行，而第一个版本始终返回 1 行。
添加LIMIT 1类似演示只允许 0 或 1 行。RETURN QUERY是直接从查询返回结果的简单方法。
您可以在单个函数中使用多个实例向输出添加更多行。
db<>fiddle here(demonstrating both)
db<>在这里小提琴（展示两者）
Varying row-type
不同的行类型
If your function is supposed to dynamically return results with a different row-typedepending on the input, read more here:
如果您的函数应该根据输入动态返回具有不同行类型的结果，请在此处阅读更多信息：
回答by Quassnoi
If you have a table with this exact record layout, use its name as a type, otherwise you will have to declare the type explicitly:
如果您有一个具有这种精确记录布局的表，请将其名称用作类型，否则您必须明确声明该类型：
CREATE OR REPLACE FUNCTION get_object_fields
(
name text
)
RETURNS mytable
AS
$$
DECLARE f1 INT;
DECLARE f2 INT;
…
DECLARE f8 INT;
DECLARE retval mytable;
BEGIN
-- fetch fields f1, f2 and f3 from table t1
-- fetch fields f4, f5 from table t2
-- fetch fields f6, f7 and f8 from table t3
retval := (f1, f2, …, f8);
RETURN retval;
END
$$ language plpgsql;
回答by Ritesh Jha
You can achieve this by using simply as a returns set of records using return query.
您可以通过简单地使用返回查询作为返回记录集来实现这一点。
CREATE OR REPLACE FUNCTION schemaName.get_two_users_from_school(schoolid bigint)
RETURNS SETOF record
LANGUAGE plpgsql
AS $function$
begin
return query
SELECT id, name FROM schemaName.user where school_id = schoolid;
end;
$function$
And call this function as : select * from schemaName.get_two_users_from_school(schoolid) as x(a bigint, b varchar);
并将此函数称为： select * from schemaName.get_two_users_from_school(schoolid) as x(a bigint, b varchar);
回答by Jerome RIVRON
you can do this using OUT parameter and CROSS JOIN
您可以使用 OUT 参数和 CROSS JOIN 执行此操作
CREATE OR REPLACE FUNCTION get_object_fields(my_name text, OUT f1 text, OUT f2 text)
AS $$
SELECT t1.name, t2.name
FROM table1 t1
CROSS JOIN table2 t2
WHERE t1.name = my_name AND t2.name = my_name;
$$ LANGUAGE SQL;
then use it as a table:
然后将其用作表格：
select get_object_fields( 'Pending') ;
get_object_fields
-------------------
(Pending,code)
(1 row)
or
或者
select * from get_object_fields( 'Pending');
f1 | f
---------+---------
Pending | code
(1 row)
or
或者
select (get_object_fields( 'Pending')).f1;
f1
---------
Pending
(1 row)
