[sql] PostgreSQL의 함수 내에서 SELECT 결과를 반환하는 방법은 무엇입니까?

Program / 글쓴이

PostgreSQL에이 함수가 있지만 쿼리 결과를 반환하는 방법을 모르겠습니다.

CREATE OR REPLACE FUNCTION wordFrequency(maxTokens INTEGER)
  RETURNS SETOF RECORD AS
$$
BEGIN
    SELECT text, count(*), 100 / maxTokens * count(*)
    FROM (
        SELECT text
    FROM token
    WHERE chartype = 'ALPHABETIC'
    LIMIT maxTokens
    ) as tokens
    GROUP BY text
    ORDER BY count DESC
END
$$
LANGUAGE plpgsql;

하지만 PostgreSQL 함수 내에서 쿼리 결과를 반환하는 방법을 모르겠습니다.

반환 유형이 SETOF RECORD 습니다. 그러나 반환 명령은 옳지 않습니다.

이를 수행하는 올바른 방법은 무엇입니까?

답변

사용 RETURN QUERY:

CREATE OR REPLACE FUNCTION word_frequency(_max_tokens int)
  RETURNS TABLE (txt   text   -- also visible as OUT parameter inside function
               , cnt   bigint
               , ratio bigint) AS
$func$
BEGIN
   RETURN QUERY
   SELECT t.txt
        , count(*) AS cnt                 -- column alias only visible inside
        , (count(*) * 100) / _max_tokens  -- I added brackets
   FROM  (
      SELECT t.txt
      FROM   token t
      WHERE  t.chartype = 'ALPHABETIC'
      LIMIT  _max_tokens
      ) t
   GROUP  BY t.txt
   ORDER  BY cnt DESC;                    -- potential ambiguity 
END
$func$  LANGUAGE plpgsql;

요구:

SELECT * FROM word_frequency(123);

설명:

  • 반환 유형을 단순히 레코드로 선언하는 것보다 명시 적으로 정의하는 것이 훨씬 더 실용적입니다. 이렇게하면 모든 함수 호출에 열 정의 목록을 제공 할 필요가 없습니다. RETURNS TABLE그렇게하는 한 가지 방법입니다. 다른 것들이 있습니다. OUT매개 변수의 데이터 유형은 쿼리에서 반환 된 것과 정확히 일치해야합니다.

  • OUT매개 변수의 이름을 신중하게 선택하십시오 . 거의 모든 곳에서 기능 본문에서 볼 수 있습니다. 충돌이나 예기치 않은 결과를 방지하기 위해 동일한 이름의 열을 테이블 한정합니다. 내 예제의 모든 열에 대해 그렇게했습니다.

    그러나 매개 변수 와 동일한 이름의 열 별칭 간에 잠재적 인 이름 지정 충돌에 유의하십시오 . 이 특별한 경우 ( ) Postgres는 매개 변수에 대해 열 별칭을 사용합니다 . 하지만 다른 상황에서는 모호 할 수 있습니다. 혼동을 피할 수있는 다양한 방법이 있습니다.OUTcntRETURN QUERY SELECT ...OUT

    1. SELECT 목록에서 항목의 서수 위치를 사용하십시오 ORDER BY 2 DESC.. 예:
    2. 표현을 반복 ORDER BY count(*) .
    3. (여기서는 해당되지 않습니다.) 구성 매개 변수를 설정 plpgsql.variable_conflict하거나 특수 명령을 사용 하십시오.#variable_conflict error | use_variable | use_column 기능에서 을 . 보다:

  • 열 이름으로 “text”또는 “count”를 사용하지 마십시오. 둘 다 Postgres에서 사용할 수 있지만 “count”는 표준 SQL 의 예약어 이며 기본 함수 이름이고 “text”는 기본 데이터 유형입니다. 혼란스러운 오류가 발생할 수 있습니다. 내가 사용 txt및 cnt내 예이다.

  • ;헤더의 구문 오류가 누락 및 수정되었습니다. (_max_tokens int)하지 (int maxTokens)– 입력 한 후 이름 .

  • 정수 나누기로 작업하는 동안 반올림 오류를 최소화하려면 먼저 곱하고 나중에 나누는 것이 좋습니다. 더 좋은 numeric점 은 (또는 부동 소수점 유형)으로 작업하는 것입니다. 아래를 참조하십시오.

대안

이것은 귀하의 쿼리가 실제로 어떻게 보일 것이라고 생각 합니다 ( 토큰 당 상대 점유율 계산 ).

CREATE OR REPLACE FUNCTION word_frequency(_max_tokens int)
  RETURNS TABLE (txt            text
               , abs_cnt        bigint
               , relative_share numeric) AS
$func$
BEGIN
   RETURN QUERY
   SELECT t.txt, t.cnt
        , round((t.cnt * 100) / (sum(t.cnt) OVER ()), 2)  -- AS relative_share
   FROM  (
      SELECT t.txt, count(*) AS cnt
      FROM   token t
      WHERE  t.chartype = 'ALPHABETIC'
      GROUP  BY t.txt
      ORDER  BY cnt DESC
      LIMIT  _max_tokens
      ) t
   ORDER  BY t.cnt DESC;
END
$func$  LANGUAGE plpgsql;

표현식 sum(t.cnt) OVER ()은 윈도우 함수 입니다. 당신은 수있는 용도 CTE를 꽤 있지만, 하위 쿼리는이 같은 간단한 경우에 일반적으로 저렴 – 서브 쿼리 대신.

매개 변수로 작업 할 때 또는 매개 변수 를 암시 적으로 사용하는 경우 최종 명시 적 RETURN문은 필요 하지 않지만 허용됩니다 .OUTRETURNS TABLEOUT

round()두 개의 매개 변수 는 numeric유형에 대해서만 작동합니다 . count()하위 쿼리에서 bigint결과가 생성 되고 sum()오버 bigint가 numeric결과를 생성 하므로 numeric숫자를 자동으로 처리하고 모든 것이 제자리에 들어갑니다.

답변

안녕하세요 아래 링크를 확인하십시오

https://www.postgresql.org/docs/current/xfunc-sql.html

전의:

CREATE FUNCTION sum_n_product_with_tab (x int)
RETURNS TABLE(sum int, product int) AS $$
    SELECT $1 + tab.y, $1 * tab.y FROM tab;
$$ LANGUAGE SQL;

답변