복붙노트

[SQL] PL / pgSQL의와 PostgreSQL을의 기록으로 여러 필드를 반환

SQL

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

해결법

  1. ==============================

    1.당신은 새로운 유형을 정의하고 그 유형을 반환하는 함수를 정의 할 필요가있다.

    당신은 새로운 유형을 정의하고 그 유형을 반환하는 함수를 정의 할 필요가있다.

    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;

    당신은 하나 개의 기록보다 더 많은 반환 할 경우에 당신은 반환 SETOF my_type 등의 기능을 정의 할 필요가

    최신 정보

    또 다른 옵션은 대신 포스트 그레스 8.4에 소개 된 TYPE을 만드는) RETURNS 표를 (사용하는 것입니다

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

    2.다형성 결과를 반환하는 TYPE을 CREATE 사용하지 마십시오. 사용 및 대신 RECORD 유형을 남용. 확인 해봐:

    다형성 결과를 반환하는 TYPE을 CREATE 사용하지 마십시오. 사용 및 대신 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의에서 멋진 가장 활용도가 낮은 기능 중 하나입니다. 말씀을 전파하십시오.

  3. ==============================

    3.OUT 매개 변수를 사용하여 간단한 :

    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;

    요구:

    SELECT * FROM get_object_fields(1);

    일반적으로이 함수 본문에 쿼리가 결합 될 수있는 경우에 간단하고 빠르게 할 수 있습니다. 그리고 당신은 RETURNS 표 ()를 사용할 수 있습니다 (포스트 그레스 8.4 이후, 긴 질문을 요청하기 전에) 0 - n 개의 행을 반환합니다.

    위의 예는과 같이 쓸 수있다 :

    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;

    요구:

    SELECT * FROM get_object_fields2(1);

    DB <> 바이올린 여기 (모두 시연)

    함수가 다른 행 유형의 입력에 따라 동적으로 리턴 결과를 가정하는 경우, 자세한 내용은 여기 읽기 :

  4. ==============================

    4.이 정확한 레코드 레이아웃과 테이블이있는 경우, 그렇지 않으면 당신은 명시 적으로 타입을 선언해야합니다, 유형으로 그 이름을 사용합니다 :

    이 정확한 레코드 레이아웃과 테이블이있는 경우, 그렇지 않으면 당신은 명시 적으로 타입을 선언해야합니다, 유형으로 그 이름을 사용합니다 :

    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;
  5. ==============================

    5.당신은 반환 쿼리를 사용하여 레코드 집합을 반환 단순히 사용하여이 작업을 수행 할 수 있습니다.

    당신은 반환 쿼리를 사용하여 레코드 집합을 반환 단순히 사용하여이 작업을 수행 할 수 있습니다.

    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$

    X로서 schemaName.get_two_users_from_school (schoolid) (a BIGINT, B의 VARCHAR)에서 선택 *; 그리고이 함수를 호출

  6. ==============================

    6.당신이 사용 OUT 매개 변수를 할 수 있으며, CROSS는 가입

    당신이 사용 OUT 매개 변수를 할 수 있으며, CROSS는 가입

    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;

    다음 테이블로 사용 :

    select get_object_fields( 'Pending') ;
get_object_fields
-------------------
(Pending,code)
(1 row)

    또는

    select * from get_object_fields( 'Pending');
f1    |   f
---------+---------
Pending | code
(1 row)

    또는

    select (get_object_fields( 'Pending')).f1;
f1
---------
Pending
(1 row)

    7. from https://stackoverflow.com/questions/4547672/return-multiple-fields-as-a-record-in-postgresql-with-pl-pgsql by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] JSON 함수에 SQL 서버 SELECT  (0) 2020.04.18
[SQL] 하나 개의 SQL 쿼리에서 모든 상위 행을 얻기  (0) 2020.04.18
[SQL] SQL 서버와 SELECT FOR UPDATE  (0) 2020.04.18
[SQL] T-SQL : MAX에 열을 기반으로 선택 (기타 열)  (0) 2020.04.18
[SQL] SQL - 쿼리 서버의 IP 주소를 얻을 수 있습니다  (0) 2020.04.18
,

티스토리툴바