가변 열 번호 및 유형을 사용하여 동적 SELECT 쿼리의 결과를 반환하도록 PL/pgSQL 함수를 리팩터링하려면 어떻게 해야 합니까?

다양한 SELECT 쿼리의 출력을 반환하도록 PL/pgSQL 함수를 리팩터링

소개

SELECT 쿼리를 텍스트 문자열로 생성하는 PL/pgSQL 함수가 있습니다. 이제 독립 실행형 쿼리의 동작과 유사하게 생성된 쿼리를 실행하고 실제 결과를 반환하도록 이 기능을 더욱 향상하려고 합니다.

변수 반환 유형

문제는 반환되는 데이터의 구조와 유형이 쿼리되는 기본 테이블에 따라 달라질 수 있다는 것입니다. 간단한 해결책은 다음과 같이 수정된 함수에서와 같이 공통 열 이름으로 고정 반환 유형을 정의하고 모든 값을 텍스트로 캐스팅하는 것입니다.

<code class="language-sql">CREATE OR REPLACE FUNCTION data_of(_id integer)
  RETURNS TABLE (datahora timestamp, col2 text, col3 text)
  LANGUAGE plpgsql AS
$func$
DECLARE
   _sensors text := 'col1::text, col2::text';
   _type    text := 'foo';
BEGIN
   RETURN QUERY EXECUTE '
      SELECT datahora, ' || _sensors || '
      FROM   ' || quote_ident(_type) || '
      WHERE  id = 
      ORDER  BY datahora'
   USING  _id;
END
$func$;</code>

가변 개수의 열 처리

반환된 열 수가 변경되었지만 유형이 동일하게 유지되는 경우(예: 모두 float8인 경우) 중첩된 값의 배열을 사용할 수 있습니다.

<code class="language-sql">CREATE OR REPLACE FUNCTION data_of(_id integer)
  RETURNS TABLE (datahora timestamp, names text[], values float8[])
  LANGUAGE plpgsql AS
$func$
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
$func$;</code>

모든 테이블 유형을 동적으로 반환

다양한 구조의 다양한 완전한 테이블 유형을 처리하려면 다형성 유형을 활용할 수 있습니다.

<code class="language-sql">CREATE OR REPLACE FUNCTION data_of(_tbl_type anyelement, _id int)
  RETURNS SETOF anyelement
  LANGUAGE plpgsql AS
$func$
BEGIN
   RETURN QUERY EXECUTE format('
      SELECT *
      FROM   %s  -- pg_typeof returns regtype, quoted automatically
      WHERE  id = 
      ORDER  BY datahora'
    , pg_typeof(_tbl_type))
   USING  _id;
END
$func$;</code>

이 함수를 사용하려면 대상 테이블 유형으로 캐스팅되는 NULL 값(예: NULL::pcdmet)을 전달해야 합니다. 그런 다음 적절한 행 유형을 자동으로 결정하고 SELECT * FROM data_of()와 함께 사용할 때 개별 열을 반환합니다.

위 내용은 가변 열 번호 및 유형을 사용하여 동적 SELECT 쿼리의 결과를 반환하도록 PL/pgSQL 함수를 리팩터링하려면 어떻게 해야 합니까?의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!