34.4. 쿼리 토토 핫(SQL) 기능

34.4.1. SQL기본 유형의 기능

가능한 가장 간단한SQL토토 핫에는 인수가 없으며 단순히 기본 유형을 반환합니다. 와 같이정수:

CREATE FUNCTION one()은 정수를 $$로 반환합니다.
    결과로 1개 선택;
$$ 언어 SQL;

-- 문자열 리터럴의 대체 구문:
CREATE FUNCTION one()은 정수 AS '를 반환합니다.
    결과로 1개 선택;
' 언어 SQL;

하나를 선택();

 하나
-----
   1

토토 핫 내에서 열 별칭을 정의했음을 확인하세요. 토토 핫 결과의 본문(이름 포함)결과), 하지만 이 열 별칭은 표시되지 않습니다. 함수 외부. 따라서 결과에는 라벨이 지정됩니다.하나대신에결과.

정의하는 것이 거의 쉽습니다SQL기본 유형을 다음과 같이 취하는 함수 인수. 아래 예에서 우리가 어떻게 참조하는지 주목하세요. 함수 내의 인수는 다음과 같습니다.$1그리고$2.

CREATE FUNCTION add_em(정수, 정수) $$로 정수를 반환합니다.
    $1 + $2를 선택하세요.
$$ 언어 SQL;

SELECT add_em(1, 2) AS 답변;

 대답하다
--------
      3

다음은 인출에 사용할 수 있는 더 유용한 기능입니다. 은행 계좌:

CREATE FUNCTION tf1 (정수, 숫자) $$로 정수를 반환합니다.
    업데이트 뱅크
        SET 잔액 = 잔액 - $2
        어디에 accountno = $1;
    1을 선택하세요.
$$ 언어 SQL;

사용자는 이 기능을 실행하여 계좌 17에서 인출할 수 있습니다. 다음과 같이 $100.00:

tf1(17, 100.0) 선택;

실제로는 더 유용한 결과를 원할 것입니다. 상수 1보다 함수에서 더 가능성이 높습니다. 정의는 다음과 같습니다:

CREATE FUNCTION tf1 (정수, 숫자) 숫자를 $$로 반환합니다.
    업데이트 뱅크
        SET 잔액 = 잔액 - $2
        어디에 accountno = $1;
    계좌 번호 = $1인 은행에서 잔액을 선택하세요.
$$ 언어 SQL;

잔액을 조정하고 새 잔액을 반환합니다. 는 다음을 사용하여 하나의 명령으로 동일한 작업을 수행할 수 있습니다.돌아오는 중:

CREATE FUNCTION tf1 (정수, 숫자) 숫자를 $$로 반환합니다.
    업데이트 뱅크
        SET 잔액 = 잔액 - $2
        계정 번호 = $1인 곳
    잔액 반환;
$$ 언어 SQL;

34.4.2. SQL복합체의 기능 유형

복합 유형의 인수로 토토 핫를 작성할 때 우리는 위에서 했던 것처럼 우리가 원하는 인수를 지정해야 할 뿐만 아니라 와 함께$1그리고$2)뿐만 아니라 원하는 속성(필드)도 그 주장. 예를 들어 다음과 같이 가정해 보겠습니다.emp은 직원 데이터가 포함된 테이블입니다. 따라서 각 행의 복합 유형 이름도 테이블. 다음은 함수입니다.double_salary누군가의 것을 계산하는 것 급여가 두 배가 된다면:

테이블 emp 생성(
    이름 텍스트,
    급여 숫자,
    나이 정수,
    큐비클 포인트
);

CREATE FUNCTION double_salary(emp) 숫자를 $$로 반환합니다.
    SELECT $1.급여 * 2 AS 급여;
$$ 언어 SQL;

SELECT 이름, double_salary(emp.*) AS dream
    엠프에서
    WHERE emp.cubicle ~= 포인트 '(2,1)';

 이름 | 꿈
------+---------
 빌 |  8400

구문 사용에 유의하세요$1.급여인수의 필드 하나를 선택하려면 행 값. 또한 호출 방법에 주목하세요.선택명령 사용*테이블의 현재 행 전체를 복합으로 선택하려면 가치. 또는 다음을 사용하여 테이블 행을 참조할 수도 있습니다. 테이블 이름은 다음과 같습니다.

SELECT 이름, double_salary(emp) AS 꿈
    엠프에서
    WHERE emp.cubicle ~= 포인트 '(2,1)';

그러나 이 사용법은 쉽게 얻을 수 있으므로 더 이상 사용되지 않습니다. 혼란스러워요.

때때로 복합 인수를 구성하는 것이 편리합니다. 즉시 가치를 부여합니다. 이는 다음을 사용하여 수행할 수 있습니다.ROW구조물. 예를 들어, 토토 핫에 전달되는 데이터:

SELECT name, double_salary(ROW(이름, 급여*1.1, 나이, 칸막이)) AS dream
    Emp에서;

다음을 반환하는 함수를 만드는 것도 가능합니다 복합형. 다음은 다음을 반환하는 함수의 예입니다. 싱글emp행:

함수 만들기 new_emp() emp를 $$로 반환합니다.
    SELECT 텍스트 '없음' AS 이름,
        AS연봉 1000.0,
        25 AS나이,
        포인트 '(2,2)' AS 칸막이;
$$ 언어 SQL;

이 예에서는 각 속성을 지정했습니다. 상수 값을 사용하지만 어떤 계산이라도 가능했습니다. 이 상수로 대체되었습니다.

토토 핫 정의에 관한 두 가지 중요한 사항에 유의하세요.

• 쿼리의 선택 목록 순서는 정확히 다음과 같아야 합니다. 테이블에 열이 나타나는 것과 동일 복합 유형과 연관되어 있습니다. (열 이름을 다음과 같이 지정합니다. 위에서 한 내용은 시스템과 관련이 없습니다.)

• 다음과 일치하도록 표현식을 유형 변환해야 합니다. 복합 유형을 정의하지 않으면 오류가 발생합니다 이렇게:

  오류: emp를 반환하도록 선언된 함수가 열 1의 텍스트 대신 varchar를 반환합니다.
  

동일한 기능을 정의하는 다른 방법은 다음과 같습니다:

함수 만들기 new_emp() emp를 $$로 반환합니다.
    SELECT ROW('없음', 1000.0, 25, '(2,2)')::emp;
$$ 언어 SQL;

여기에 우리는 다음을 썼습니다.선택반환 올바른 복합 유형의 단일 열입니다. 이건 아니다 이 상황에서는 정말 더 좋지만 편리한 대안입니다. 어떤 경우에는 — 예를 들어 결과를 계산해야 하는 경우 원하는 합성물을 반환하는 다른 함수를 호출하여 값.

우리는 이 함수를 두 가지 중 하나에서 직접 호출할 수 있습니다 방법:

SELECT new_emp();

         new_emp
-------------
 (없음,1000.0,25,"(2,2)")

SELECT * FROM new_emp();

 이름 | 급여 | 나이 | 칸막이
------+---------+------+---------
 없음 | 1000.0 |  25 | (2,2)

두 번째 방법은 다음에 더 자세히 설명되어 있습니다.섹션 34.4.6.

복합 유형을 반환하는 함수를 사용할 때, 결과에서 하나의 필드(속성)만 원할 수도 있습니다. 당신은 할 수 있습니다 다음과 같은 구문을 사용하세요.

SELECT (new_emp()).이름;

 이름
------
 없음

파서가 다음을 수행하지 못하도록 하려면 추가 괄호가 필요합니다. 혼란스러워지고 있습니다. 그들 없이 하려고 하면, 이런 것:

SELECT new_emp().name;
오류: '.' 근처에 구문 오류가 있습니다.
라인 1: SELECT new_emp().name;
                        ^

또 다른 옵션은 추출을 위해 함수 표기법을 사용하는 것입니다 속성. 이것을 설명하는 간단한 방법은 다음과 같습니다. 표기법속성(테이블)그리고테이블.속성교환 가능합니다.

SELECT 이름(new_emp());

 이름
------
 없음

-- 이는 다음과 같습니다:
-- emp.name AS youngster FROM emp WHERE emp.age < 30;

SELECT name(emp) AS youngster FROM emp WHERE age(emp) < 30;

 젊은이
-----------
 샘
 앤디

복합 유형을 반환하는 함수를 사용하는 또 다른 방법은 올바른 결과를 받아들이는 다른 함수에 결과를 전달하려면 입력으로서의 행 유형:

CREATE FUNCTION getname(emp) 텍스트를 $$로 반환합니다.
    $1.이름을 선택하세요.
$$ 언어 SQL;

SELECT getname(new_emp());
 getname
---------
 없음
(1행)

복합을 반환하는 토토 핫를 사용하는 또 다른 방법 유형은 에 설명된 대로 테이블 토토 핫로 호출하는 것입니다.섹션 34.4.6.

34.4.3. SQL출력 기능 매개변수

함수 결과를 설명하는 또 다른 방법은 다음과 같습니다. 다음으로 정의하세요.출력 매개변수, 이 예에서는:

CREATE FUNCTION add_em (IN x int, IN y int, OUT sum int)
AS '선택 $1 + $2'
언어 SQL;

SELECT add_em(3,7);
 add_em
--------
     10
(1행)

이것은 버전과 본질적으로 다르지 않습니다.add_em표시됨섹션 34.4.1. 출력 매개변수의 실제 가치는 여러 가지를 반환하는 함수를 정의하는 편리한 방법 열. 예를 들어,

CREATE FUNCTION sum_n_product (x int, y int, OUT sum int, OUT product int)
AS '선택 $1 + $2, $1 * $2'
언어 SQL;

 SELECT * FROM sum_n_product(11,42);
 합계 | 제품
------+---------
  53 |     462
(1행)

여기서 본질적으로 일어난 일은 우리가 함수 결과에 대한 익명 복합 유형입니다. 는 위의 예는 다음과 동일한 최종 결과를 갖습니다.

CREATE TYPE sum_prod AS (합계 정수, 제품 정수);

CREATE FUNCTION sum_n_product (int, int) 반환 sum_prod
AS '선택 $1 + $2, $1 * $2'
언어 SQL;

그러나 별도의 복합 유형을 사용하지 않아도 됩니다. 정의는 종종 편리합니다.

출력 매개변수는 SQL에서 이러한 함수를 호출할 때 인수 목록을 호출합니다. 그 이유는PostgreSQL함수 정의를 위해 입력 매개변수만 고려합니다. 서명을 부릅니다. 이는 또한 입력만 의미합니다. 매개변수는 목적을 위해 함수를 참조할 때 중요합니다. 떨어뜨리는 등. 위의 함수를 다음과 같이 삭제할 수 있습니다. 중 하나

드롭 함수 sum_n_product(x int, y int, OUT sum int, OUT product int);
삭제 함수 sum_n_product (int, int);

매개변수는 다음과 같이 표시될 수 있습니다.IN( 기본값),아웃, INOUT또는VARIADIC. 안INOUT매개변수는 입력 매개변수(호출 인수 목록의 일부) 및 출력 매개변수(결과 레코드 유형의 일부).VARIADIC매개변수는 입력 매개변수이지만 다음에 설명된 대로 특별히 취급됩니다.

34.4.4. SQL변수 개수의 토토 핫 인수

SQL함수는 다음과 같습니다. 가변 개수의 인수를 허용하도록 선언되었습니다. 모두"선택사항"인수는 다음과 같습니다. 동일한 데이터 유형. 선택적 인수가 전달됩니다. 함수를 배열로 사용합니다. 함수는 표시로 선언됩니다. 마지막 매개변수는 다음과 같습니다.VARIADIC; 이 매개변수는 배열 유형으로 선언되어야 합니다. 에 대한 예:

CREATE FUNCTION mleast(VARIADIC 숫자[]) 숫자를 $$로 반환합니다.
    SELECT min($1[i]) FROM generate_subscripts($1, 1) g(i);
$$ 언어 SQL;

SELECT meast(10, -1, 5, 4.4);
 최소 
--------
     -1
(1행)

사실상, 모든 실제 주장은 그 이상입니다.VARIADIC위치는 다음과 같이 정리됩니다. 마치 작성한 것처럼 1차원 배열

SELECT meast(ARRAY[10, -1, 5, 4.4]);    -- 작동하지 않습니다.

그렇지만 실제로는 쓸 수 없습니다. 또는 적어도 그렇게 될 것입니다. 이 함수 정의와 일치하지 않습니다. 표시된 매개변수VARIADIC하나 이상 일치 자체 유형이 아닌 요소 유형이 발생합니다.

때때로 다음을 통과할 수 있는 것이 유용합니다. 이미 구성된 배열을 가변 함수로 변환합니다. 이것은 하나의 가변 함수를 전달하려고 할 때 특히 유용합니다. 해당 배열 매개변수를 다른 매개변수로 변경합니다. 당신은 그렇게 할 수 있습니다 지정VARIADIC통화 중:

SELECT mleast(VARIADIC ARRAY[10, -1, 5, 4.4]);

이것은 토토 핫의 가변 매개변수 확장을 방지합니다 요소 유형으로 변환하여 배열 인수를 허용합니다. 정상적으로 일치하는 값입니다.VARIADIC할 수 있다 토토 핫의 마지막 실제 인수에만 연결됩니다. 전화해.

34.4.5. SQL다음에 대한 기본값을 가진 함수 인수

함수는 일부 또는 일부에 대해 기본값으로 선언될 수 있습니다. 모든 입력 인수. 기본값은 언제든지 삽입됩니다. 함수가 실제 개수가 충분하지 않은 상태로 호출되었습니다. 인수. 인수는 끝에서만 생략할 수 있으므로 실제 인수 목록, 매개변수 뒤의 모든 매개변수 기본값에는 기본값도 있어야 합니다.

예:

토토 핫 생성 foo(a int, b int DEFAULT 2, c int DEFAULT 3)
반환 정수
언어 SQL
AS $$
    $1 + $2 + $3을 선택하세요.
$$;

SELECT foo(10, 20, 30);
 푸 
-----
  60
(1줄)

SELECT foo(10, 20);
 푸 
-----
  33
(1줄)

SELECT foo(10);
 푸 
-----
  15
(1줄)

선택 foo();  -- 첫 번째 인수에 대한 기본값이 없으므로 실패합니다.
오류: foo() 함수가 존재하지 않습니다.

그=기호는 다음에서도 사용할 수 있습니다. 키워드의 위치기본값,

34.4.6. SQL테이블 소스로서의 기능

모든 SQL 함수는 다음에서 사용할 수 있습니다.발신쿼리의 절이지만 특히 복합 유형을 반환하는 함수에 유용합니다. 기능의 경우 기본 유형을 반환하도록 정의된 경우 테이블 함수는 1열 테이블. 함수가 다음을 반환하도록 정의된 경우 복합 유형의 경우 테이블 함수는 각 유형에 대한 열을 생성합니다. 복합 유형의 속성입니다.

다음은 예입니다:

CREATE TABLE foo(foid int, foosubid int, fooname 텍스트);
foo 값에 삽입(1, 1, 'Joe');
foo 값에 삽입(1, 2, 'Ed');
INSERT INTO foo VALUES(2, 1, 'Mary');

함수 만들기 getfoo(int) foo를 $$로 반환합니다.
    SELECT * FROM foo WHERE fooid = $1;
$$ 언어 SQL;

SELECT *, upper(fooname) FROM getfoo(1) AS t1;

 푸이드 | 후수비드 | 이름 | 상부
--------+----------+---------+-------
     1 |        1 | 조 | 조
(1행)

예제에서 볼 수 있듯이, 우리는 함수의 결과는 마치 함수의 열인 것처럼 동일합니다. 일반 테이블.

함수에서 단 하나의 행만 얻었음을 참고하세요. 이것은 우리는 사용하지 않았기 때문에SETOF. 즉 다음 섹션에서 설명합니다.

34.4.7. SQL토토 핫 반환 세트

SQL 함수가 반환으로 선언된 경우SETOF어떤 종류, 함수의 최종 쿼리가 완료될 때까지 실행되고 각 쿼리는 출력되는 행은 결과 집합의 요소로 반환됩니다.

이 기능은 일반적으로 다음에서 함수를 호출할 때 사용됩니다.발신절. 이 경우 각 행 함수에 의해 반환된 것은 테이블의 행이 됩니다. 쿼리. 예를 들어 다음 테이블을 가정해 보겠습니다.foo위와 내용이 동일하며 우리는 말하다:

토토 핫 생성 getfoo(int)는 setof foo를 $$로 반환합니다.
    SELECT * FROM foo WHERE fooid = $1;
$$ 언어 SQL;

SELECT * FROM getfoo(1) AS t1;

그러면 우리는 다음을 얻게 될 것입니다:

푸이드 | 후수비드 | fooname
--------+------------+---------
     1 |        1 | 조
     1 |        2 | 에드
(2행)

열과 함께 여러 행을 반환하는 것도 가능합니다 다음과 같이 출력 매개변수로 정의됩니다.

CREATE FUNCTION sum_n_product_with_tab (x int, OUT sum int, OUT product int) SETOF 레코드를 $$로 반환합니다.
    SELECT $1 + tab.y, $1 * tab.y FROM 탭;
$$ 언어 SQL;

여기서 핵심은 반드시 작성해야 한다는 것입니다.SETOF 레코드 반환다음을 나타냅니다. 함수는 하나가 아닌 여러 행을 반환합니다. 만약 있다면 출력 매개변수가 하나만 있으면 해당 매개변수의 유형을 대신 작성하세요. 의기록.

현재 집합을 반환하는 함수는 다음에서도 호출될 수 있습니다. 쿼리의 선택 목록입니다. 쿼리가 수행되는 각 행에 대해 자체적으로 생성되고, 집합을 반환하는 함수가 호출됩니다. 함수의 각 요소에 대해 출력 행이 생성됩니다. 결과 집합. 하지만 이 기능은 더 이상 사용되지 않습니다. 향후 릴리스에서는 제거될 수 있습니다. 다음은 선택 목록에서 세트를 반환하는 예제 함수:

CREATE FUNCTION listchildren(text) SETOF 텍스트를 $$로 반환합니다.
    SELECT 이름 FROM 노드 WHERE 상위 = $1
$$ 언어 SQL;

SELECT * FROM 노드;
   이름 | 부모
---------+---------
 탑 |
 어린이1 | 탑
 차일드2 | 탑
 어린이3 | 탑
 하위 하위1 | 어린이1
 하위 하위2 | 어린이1
(6열)

SELECT listchildren('상위');
 목록어린이
--------------
 어린이1
 어린이2
 어린이3
(3열)

SELECT 이름, listchildren(이름) FROM 노드;
  이름 | 목록어린이
------+---------------
 탑 | 어린이1
 탑 | 어린이2
 탑 | 어린이3
 어린이1 | 하위 하위1
 어린이1 | 하위 하위2
(5행)

마지막에선택, 아니요 다음에 대한 출력 행이 나타납니다.아이2, 아이3등. 이런 일이 발생하는 이유는목록어린이빈 세트를 반환합니다. 해당 인수에 대해서는 결과 행이 생성되지 않습니다.

34.4.8. SQL토토 핫 반환표

함수를 반환하는 것으로 선언하는 또 다른 방법이 있습니다. 다음 구문을 사용하는 집합반품 테이블(열). 이것은 하나 이상을 사용하는 것과 같습니다.밖으로매개변수와 토토 핫를 반환으로 표시SETOF 레코드(또는SETOF해당하는 경우 단일 출력 매개변수 유형). 이 최신 버전의 SQL 표준에는 표기법이 지정되어 있습니다. 따라서 다음을 사용하는 것보다 이식성이 더 좋을 수 있습니다.SETOF.

예를 들어, 위의 합계와 곱의 예는 다음과 같습니다. 이 방법으로도 할 수 있습니다:

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

명시적인 사용은 허용되지 않습니다.아웃또는INOUT매개변수 와 함께반품 테이블표기법 — 당신 모든 출력 열을에 넣어야 합니다.표목록.

34.4.9. 다형성SQL기능

SQL함수는 다음과 같습니다. 다형성 유형을 허용하고 반환하도록 선언되었습니다.모든 요소, 모든 배열, anynonarray및anyenum. 참조섹션 34.2.5다형성에 대한 더 자세한 설명은 기능. 다음은 다형성 함수입니다.make_array두 개의 배열로 구성된 배열 임의의 데이터 유형 요소:

CREATE FUNCTION make_array(anyelement, anyelement) 모든 배열을 $$로 반환합니다.
    배열 선택[$1, $2];
$$ 언어 SQL;

SELECT make_array(1, 2) AS intarray, make_array('a'::text, 'b') AS textarray;
 인타레이 | 텍스트 배열
---------+------------
 1,2 | a,b

타입캐스트 사용에 유의하세요'a'::텍스트인수가 다음과 같음을 지정합니다. 유형텍스트. 이는 다음과 같은 경우에 필요합니다. 인수는 단지 문자열 리터럴입니다. 그렇지 않으면 유형으로 취급됨알 수 없음및 배열알 수 없음은 유효한 유형이 아닙니다. 없이 typecast를 사용하면 다음과 같은 오류가 발생합니다.

오류: 입력에 "알 수 없음" 유형이 있으므로 다형성 유형을 결정할 수 없습니다.

고정된 다형성 인수를 갖는 것이 허용됩니다. 반환 유형이지만 그 반대는 그렇지 않습니다. 예를 들면:

CREATE FUNCTION is_greater(anyelement, anyelement) $$로 부울을 반환합니다.
    $1  $2를 선택하세요.
$$ 언어 SQL;

SELECT is_greater(1, 2);
 is_greater
------------
 에프
(1줄)

CREATE FUNCTION valid_func() 모든 요소를 $$로 반환합니다.
    1을 선택하세요.
$$ 언어 SQL;
오류: 결과 데이터 유형을 확인할 수 없습니다.
세부사항: 다형성 유형을 반환하는 함수에는 다형성 인수가 하나 이상 있어야 합니다.

다형성은 출력이 있는 토토 핫와 함께 사용할 수 있습니다. 인수. 예를 들면:

CREATE FUNCTION dup(f1 모든 요소, OUT f2 모든 요소, OUT f3 모든 배열)
AS '$1 선택, 배열[$1,$1]' LANGUAGE SQL;

SELECT * FROM dup(22);
 f2 |   f3
----+---------
 22 | 22,22

다형성은 가변 함수에도 사용할 수 있습니다. 에 대한 예:

CREATE FUNCTION anyleast (VARIADIC anyarray) 모든 요소를 $$로 반환합니다.
    SELECT min($1[i]) FROM generate_subscripts($1, 1) g(i);
$$ 언어 SQL;

SELECT 최소(10, -1, 5, 4);
 어쨌든 
----------
       -1
(1줄)

SELECT anyleast('abc'::text, 'def');
 어쨌든 
----------
 알파벳
(1줄)

CREATE FUNCTION concat(text, VARIADIC anyarray) 텍스트를 $$로 반환합니다.
    SELECT array_to_string($2, $1);
$$ 언어 SQL;

SELECT concat('|', 1, 4, 2);
 연결 
--------
 1|4|2
(1행)