동적 SQL 쿼리 결과를 반환하도록 PL/pgSQL 함수를 어떻게 수정합니까?

쿼리 결과를 반환하도록 PL/pgSQL 함수 수정

배경

처음에는 올바른 형식의 PostgreSQL SELECT 쿼리 문자열을 출력하는 함수를 생성합니다. 이제 생성된 SELECT 문을 데이터베이스에 대해 직접 실행하고 해당 결과를 검색하려고 합니다.

메서드: 동적 SQL 및 반환 유형

동적 SQL

동적 SQL을 실행하려면 EXECUTE 명령을 사용합니다. 커서를 사용하지 않으므로 RETURN QUERY EXECUTE 구문을 사용할 수 있습니다.

반품 유형

함수가 반환 유형을 선언해야 하기 때문에 문제는 정의되지 않은 유형의 레코드를 반환하는 것입니다. 데이터의 구체적인 특성에 따라 다양한 접근 방식을 고려할 것입니다.

고정 반환형을 이용한 간단한 솔루션

타임스탬프, 텍스트 및 텍스트 열에 대한 고정 반환 유형을 가정하면 다음과 같이 함수를 정의할 수 있습니다.

CREATE FUNCTION data_of(_id integer)
RETURNS TABLE (datahora timestamp, col2 text, col3 text)
AS $$
DECLARE
   _sensors text := 'col1, col2, col3';
   _type text := 'foo';
BEGIN
   RETURN QUERY EXECUTE format(
      'SELECT datahora, %s
       FROM   %s
       WHERE  id = 
       ORDER  BY datahora',
      _sensors,
      _type
   )
   USING _id;
END
$$ LANGUAGE plpgsql;

동일한 유형의 다양한 열 수

가변 개수의 열이 있고 모든 열이 동일한 유형(예: double)인 경우 ARRAY 유형을 사용하여 값을 중첩할 수 있습니다.

CREATE FUNCTION data_of(_id integer)
RETURNS TABLE (datahora timestamp, names text[], values float8[])
AS $$
DECLARE
   _sensors text := 'col1, col2, col3';
   _type text := 'foo';
BEGIN
   RETURN QUERY EXECUTE format(
      'SELECT datahora
           , string_to_array(, ',')  -- AS names
           , ARRAY[%s]            -- AS values
       FROM   %s
       WHERE  id = 
       ORDER  BY datahora',
      _sensors,
      _type
   )
   USING _sensors, _id;
END
$$ LANGUAGE plpgsql;

다양한 완성형 테이블

테이블의 모든 열을 반환하려면 다형성 유형을 사용할 수 있습니다.

CREATE FUNCTION data_of(_tbl_type anyelement, _id int)
RETURNS SETOF anyelement
AS $$
BEGIN
   RETURN QUERY EXECUTE format(
      'SELECT *
       FROM   %I  -- pg_typeof returns regtype, quoted automatically
       WHERE  id = 
       ORDER  BY datahora',
      pg_typeof(_tbl_type)
   )
   USING _id;
END
$$ LANGUAGE plpgsql;

참고: 두 번째 예에서 string_to_array 함수에는 구분 기호가 필요하며 ,가 여기에 구분 기호로 추가됩니다. 세 번째 예에서는 %I 형식 지정 식별자를 사용하여 SQL 삽입을 방지하기 위해 보다 안전한 방법으로 테이블 이름을 처리합니다. 선택하는 방법은 특정 요구 사항과 데이터 구조에 따라 다릅니다. 가능하다면 테이블 구조와 원하는 결과에 대한 자세한 정보를 제공하시면 보다 정확하고 효율적인 솔루션을 제공하는 데 도움이 될 수 있습니다.

위 내용은 동적 SQL 쿼리 결과를 반환하도록 PL/pgSQL 함수를 어떻게 수정합니까?의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!