sql - 使用 PL/pgSQL 在 PostgreSQL 中返回多个字段作为记录

Question

我正在使用 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; 

如何将不同表中的字段作为单个记录中的字段返回？

[编辑]

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

下面的代码应该有助于进一步说明:

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

Answer 1

不要使用 CREATE TYPE返回多态结果。使用和滥用 RECORD type反而。检查一下:

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;

请注意，它可以根据输入选择返回两个 或三个 列。

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)

这确实会对代码造成严重破坏，因此请使用一致数量的列，但它对于返回可选的错误消息以及返回操作成功的第一个参数非常方便。使用一致数量的列重写:

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;

几乎达到史诗般的热度:

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)

但是如何将其拆分为多行，以便您选择的 ORM 层可以将值转换为您选择的语言的 native 数据类型？热度:

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)

这是 PostgreSQL 中最酷和最未被充分利用的特性之一。请广而告之。