使用 PL/pgSQL 在 PostgreSQL 中返回多个字段作为记录 [英] Return multiple fields as a record in PostgreSQL with PL/pgSQL

问题描述

我正在编写一个 SP，使用 PL/pgSQL.
我想返回一个记录，由几个不同表中的字段组成.可能看起来像这样:

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:

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?

我意识到我上面给出的例子有点过于简单了.我需要检索的一些字段将在被查询的数据库表中保存为单独的行，但我想在扁平化"记录结构中返回它们.

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 AS (
  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

推荐答案

您需要定义一个新类型并定义您的函数以返回该类型.

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; 

如果你想返回多个记录，你需要定义函数为returns setof my_type

If you want to return more than one record you need to define the function as returns setof my_type

更新

另一种选择是使用 RETURNS TABLE() 而不是创建 Postgres 8.4 中引入的 TYPE

Another option is to use RETURNS TABLE() instead of creating a TYPE which was introduced in Postgres 8.4

CREATE OR REPLACE FUNCTION get_object_fields(name text) 
  RETURNS TABLE (f1 varchar(10), f2 varchar(10) /* , ... */ )
...

这篇关于使用 PL/pgSQL 在 PostgreSQL 中返回多个字段作为记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！