동적 SQL 반환 유형을 처리하기 위해 PL/pgSQL 함수를 리팩터링하는 방법은 무엇입니까?

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

동적 SQL 및 반환 유형

현재 함수는 생성된 SELECT 문을 나타내는 텍스트 문자열을 반환합니다. 실행을 자동화하고 결과를 반환하려면 동적 SQL을 사용할 수 있습니다. 그러나 정의되지 않은 반환 유형에 어려움이 있습니다. 함수는 RETURNS 절에 반환 유형을 정의해야 합니다.

고정반환형 솔루션

datahora이라는 타임스탬프 열과 다른 이름과 유형을 가진 두 개의 추가 열을 포함하는 고정 반환 유형을 가정하면 다음을 구현할 수 있습니다.

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

컬럼 개수는 가변적이지만 유형은 동일합니다

반환 유형에 가변 개수의 열이 있지만 모든 열이 동일한 유형(예: double)인 경우 배열 유형을 사용합니다.

<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()  -- 作为名称
           , ARRAY[%s]            -- 作为值
      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 返回 regtype，自动引用
      WHERE  id = 
      ORDER  BY datahora'
    , pg_typeof(_tbl_type))
   USING  _id;
END
$func$;</code>

사용방법:

<code class="language-sql">SELECT * FROM data_of(NULL::pcdmet, 17);</code>

위 내용은 동적 SQL 반환 유형을 처리하기 위해 PL/pgSQL 함수를 리팩터링하는 방법은 무엇입니까?의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!