[SQL] 함수의 반환 SETOF 레코드 (가상 테이블)

함수의 반환 SETOF 레코드 (가상 테이블)

나는 사용자 정의 컨텐츠 (오라클에서 같은) 가상 테이블을 반환하는 포스트 그레스 기능이 필요합니다. 표 3 열 및 행의 알 수있을 것입니다.

난 그냥 인터넷에서 올바른 구문을 찾을 수 없습니다.

이 상상 :

CREATE OR REPLACE FUNCTION "public"."storeopeninghours_tostring" (numeric)
  RETURNS setof record AS
DECLARE
  open_id ALIAS FOR $1;
  returnrecords setof record;
BEGIN
  insert into returnrecords('1', '2', '3');
  insert into returnrecords('3', '4', '5');
  insert into returnrecords('3', '4', '5');
  RETURN returnrecords;
END;

어떻게 이런 일이 제대로 작성?

해결법

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

   1.(이 모든 단지 "$ 1에 대한 ALIAS"의 사용을보고 8.3.7-- PostgreSQL을 사용하면? 이전 버전이 있습니까 테스트)

   (이 모든 단지 "$ 1에 대한 ALIAS"의 사용을보고 8.3.7-- PostgreSQL을 사용하면? 이전 버전이 있습니까 테스트)

   CREATE OR REPLACE FUNCTION storeopeninghours_tostring(numeric)
    RETURNS SETOF RECORD AS $$
   DECLARE
    open_id ALIAS FOR $1;
    result RECORD;
   BEGIN
    RETURN QUERY SELECT '1', '2', '3';
    RETURN QUERY SELECT '3', '4', '5';
    RETURN QUERY SELECT '3', '4', '5';
   END
   $$;
   

   당신은 (대신 쿼리 결과의) 반환 할 레코드 또는 행 변수가있는 경우, 사용 "RETURN NEXT"보다는 "RETURN QUERY".

   당신이 좋아하는 일을 할 필요가있는 함수를 호출합니다 :

   select * from storeopeninghours_tostring(1) f(a text, b text, c text);
   

   그래서 당신은 당신이 함수의 출력 행 스키마 쿼리에있을 것으로 예상을 정의해야합니다. 이를 방지하려면 함수 정의에 출력 변수를 지정할 수 있습니다 :

   CREATE OR REPLACE FUNCTION storeopeninghours_tostring(open_id numeric, a OUT text, b OUT text, c OUT text)
    RETURNS SETOF RECORD LANGUAGE 'plpgsql' STABLE STRICT AS $$
   BEGIN
    RETURN QUERY SELECT '1'::text, '2'::text, '3'::text;
    RETURN QUERY SELECT '3'::text, '4'::text, '5'::text;
    RETURN QUERY SELECT '3'::text, '4'::text, '5'::text;
   END
   $$;
   

   (확실히 확인 추가 :: 텍스트 캐스트가 필요한 이유는 ... '1'은 기본적으로 VARCHAR 어쩌면입니까?)

2. ==============================

   2.모든 기존의 대답은 오래된 또는 시작하는 것이 비효율적된다.

   모든 기존의 대답은 오래된 또는 시작하는 것이 비효율적된다.

   세 정수 열을 반환 할 가정.

   여기에 당신이 (나중에 PostgreSQL을 8.4) 현대 PL / pgSQL에 함께 할 방법은 다음과 같습니다

   CREATE OR REPLACE FUNCTION f_foo() -- (open_id numeric) -- parameter not used
     RETURNS TABLE (a int, b int, c int) AS
   $func$
   BEGIN
   RETURN QUERY VALUES
     (1,2,3)
   , (3,4,5)
   , (3,4,5)
   ;
   END
   $func$  LANGUAGE plpgsql IMMUTABLE ROWS 3;
   

   포스트 그레스 9.6 이상에서는 또한 병렬 SAFE를 추가 할 수 있습니다.

   요구:

   SELECT * FROM f_foo();
   

   이 같은 간단한 사례를 들어, 대신 일반 SQL 문을 사용할 수 있습니다 :

   VALUES (1,2,3), (3,4,5), (3,4,5)
   

   또는 당신이 원하는 (또는이) 특정 열 이름과 유형을 정의 할 경우 :

   SELECT *
   FROM  (
      VALUES (1::int, 2::int, 3::int)
           , (3, 4, 5)
           , (3, 4, 5)
      ) AS t(a, b, c);
   

   대신 간단한 SQL 함수로 포장 할 수 있습니다

   CREATE OR REPLACE FUNCTION f_foo()
      RETURNS TABLE (a int, b int, c int) AS
   $func$
      VALUES (1, 2, 3)
           , (3, 4, 5)
           , (3, 4, 5);
   $func$  LANGUAGE sql IMMUTABLE ROWS 3;
   

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

   3.내 기능에 임시 테이블을 꽤 사용합니다. 당신은 데이터베이스에 대한 반환 유형을 만든 다음 반환에 해당 유형의 변수를 작성해야합니다. 아래는 그냥 않는 샘플 코드입니다.

   내 기능에 임시 테이블을 꽤 사용합니다. 당신은 데이터베이스에 대한 반환 유형을 만든 다음 반환에 해당 유형의 변수를 작성해야합니다. 아래는 그냥 않는 샘플 코드입니다.

   CREATE TYPE storeopeninghours_tostring_rs AS
   (colone text,
    coltwo text,
    colthree text
   );
   
   CREATE OR REPLACE FUNCTION "public"."storeopeninghours_tostring" () RETURNS setof storeopeninghours_tostring_rs AS
   $BODY$
   DECLARE
     returnrec storeopeninghours_tostring_rs;
   BEGIN
       BEGIN 
           CREATE TEMPORARY TABLE tmpopeninghours (
               colone text,
               coltwo text,
               colthree text
           );
       EXCEPTION WHEN OTHERS THEN
           TRUNCATE TABLE tmpopeninghours; -- TRUNCATE if the table already exists within the session.
       END;
       insert into tmpopeninghours VALUES ('1', '2', '3');
       insert into tmpopeninghours VALUES ('3', '4', '5');
       insert into tmpopeninghours VALUES ('3', '4', '5');
   
       FOR returnrec IN SELECT * FROM tmpopeninghours LOOP
           RETURN NEXT returnrec;
       END LOOP;
   END;
   $BODY$
   LANGUAGE 'plpgsql' VOLATILE;
   
   
   select * from storeopeninghours_tostring()
   

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

   4.:( PostgreSQL을에 존재하지 않는 임시 테이블을 만들고 수익으로 그 기록을 투기의 MSSQL에 해당 ... 찾고 여기에 착륙 한 사람들에게 -. 당신이 반환 유형을 정의해야 할 두 가지 방법이 있습니다 이 함수 생성시 또는 쿼리 생성시.

   :( PostgreSQL을에 존재하지 않는 임시 테이블을 만들고 수익으로 그 기록을 투기의 MSSQL에 해당 ... 찾고 여기에 착륙 한 사람들에게 -. 당신이 반환 유형을 정의해야 할 두 가지 방법이 있습니다 이 함수 생성시 또는 쿼리 생성시.

   여기를 봐: http://wiki.postgresql.org/wiki/Return_more_than_one_row_of_data_from_PL/pgSQL_functions

5. ==============================

   5.

   CREATE OR REPLACE FUNCTION foo(open_id numeric, OUT p1 varchar, OUT p2 varchar, OUT p3 varchar) RETURNS SETOF RECORD AS $$
   BEGIN
     p1 := '1'; p2 := '2'; p3 := '3';
     RETURN NEXT; 
     p1 := '3'; p2 := '4'; p3 := '5';
     RETURN NEXT; 
     p1 := '3'; p2 := '4'; p3 := '5';
     RETURN NEXT; 
     RETURN;
   END;
   $$ LANGUAGE plpgsql;
   

from https://stackoverflow.com/questions/955167/return-setof-record-virtual-table-from-function by cc-by-sa and MIT license