PL / pgSQL을 사용하여 PostgreSQL에서 여러 필드를 레코드로 반환

PL / pgSQL을 사용하여 PostgreSQL에서 여러 필드를 레코드로 반환

---

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

---

새 유형을 정의하고 해당 유형을 반환하도록 함수를 정의해야합니다.

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

---

최신 정보

또 다른 옵션은 Postgres 8.4에 도입 된를 RETURNS TABLE()만드는 대신 사용하는 것입니다.TYPE

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

---

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;

입력에 따라 선택적으로 두 개 또는 세 개의 열을 반환 할 수 있다는 사실에주의 하십시오.

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 레이어가 값을 선택한 언어의 기본 데이터 유형으로 변환 할 수 있도록 어떻게 여러 행으로 분할합니까? 뜨거움 :

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에서 가장 멋지고 가장 많이 사용되지 않는 기능 중 하나입니다. 소식을 전 해주세요.

---

이 될 수있다 간단한 과 OUT매개 변수 :

CREATE OR REPLACE FUNCTION get_object_fields(
          name text
    ,OUT user1_id   int
    ,OUT user1_name varchar(32)
    ,OUT user2_id   int
    ,OUT user2_name varchar(32)
) AS 
$func$
BEGIN
    SELECT t.user1_id, t.user1_name
    INTO     user1_id,   user1_name
    FROM   tbl1 t
    WHERE  t.tbl1_id = 42;

    user2_id := user1_id + 43; -- some calculation

    SELECT t.user2_name
    INTO     user2_name
    FROM   tbl2 t
    WHERE  t.tbl2_i = user2_id;
END
$func$ LANGUAGE plpgsql;

• 이 plpgsql 함수를 위해 유형을 만들 필요 는 없습니다 . 이 수 는 동일한 유형의 기능 몇 가지를 결합하려는 경우 유용합니다. OUT매개 변수가 추가 되었기 때문에 더 이상 사용하지 않습니다 .

• 아시다시피 RETURN진술 이 없습니다 . OUT매개 변수는 자동으로 반환되며 RETURN문이 필요 하지 않습니다.

• Since OUT parameters 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.

Simpler yet - or return multiple rows

Most of the time this can be simplified further. Sometimes queries in the function body can be combined, which is usually (not always) faster. And you can use RETURNS TABLE() - introduced with Postgres 8.4 (long before this question was asked as well).

The example from above could be rewritten as:

CREATE OR REPLACE FUNCTION get_object_fields(name text)
  RETURNS TABLE (
     user1_id   int
    ,user1_name varchar(32)
    ,user2_id   int
    ,user2_name varchar(32)) AS 
$func$
BEGIN
    RETURN QUERY
    SELECT t1.user1_id, t1.user1_name, t2.user2_id, t2.user2_name
    FROM   tbl1 t1
    JOIN   tbl2 t2 ON t2.user2_id = t1.user1_id + 43
    WHERE  t1.tbl1_id = 42
    LIMIT  1;  -- may be optional
END
$func$ LANGUAGE plpgsql; 

• RETURNS TABLE is effectively the same as having a bunch of OUT parameters combined with RETURNS record, just a bit shorter / more elegant.

• The major difference is, that this function can return 0, 1 or many rows, while the first version always returns 1 row.
  If you want to make sure, this one returns just 0 or 1 row, add LIMIT 1 like demonstrated.

• RETURN QUERY is the very handy modern way to return results from a query directly.
  You can use multiple instances in a single function to add more rows to the output.

Various row-types

If your function is supposed to dynamically return results with different row-types, depending on the input, read more here:

• Refactor a PL/pgSQL function to return the output of various SELECT queries

---

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; 

---

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);

---

you can do this using OUT parameter and 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)

참고URL : https://stackoverflow.com/questions/4547672/return-multiple-fields-as-a-record-in-postgresql-with-pl-pgsql