34.4. 질문 토토 핫 (SQL) 기능

34.4.1. SQL기본 유형의 기능

가장 간단한 가능한SQL함수는 인수가 없으며 단순히 기본 유형을 반환합니다. 처럼Integer:

함수 생성 1 ()는 정수를 $$로 반환합니다
    결과적으로 1을 선택하십시오.
$$ 언어 SQL;

- 문자열 리터럴에 대한 대체 구문 :
기능 one ()를 작성하여 정수를 반환합니다.
    결과적으로 1을 선택하십시오.
'언어 SQL;

하나를 선택하십시오 ();

 하나
-----
   1

토토 핫 내에서 열 별칭을 정의했음을 알 수 있습니다. 토토 핫 결과에 대한 본문 (이름 포함결과), 그러나이 열 별칭은 보이지 않습니다 기능 밖. 따라서 결과는 레이블이 지정됩니다ONE대신결과.

거의 정의하기 쉽습니다SQL기본 유형을 사용하는 기능 논쟁. 아래의 예에서, 우리가 어떻게 참조하는지 주목하십시오. 기능 내의 인수$ 1and$ 2.

함수 생성 add_em (정수, 정수)은 정수를 $$로 반환합니다
    $ 1 + $ 2를 선택하십시오.
$$ 언어 SQL;

답변으로 add_em (1, 2)을 선택하십시오.

 답변
--------
      3

여기에 더 유용한 기능이 있습니다.이 기능은 차변에 사용될 수 있습니다. 은행 계좌 :

함수 생성 tf1 (정수, 숫자)은 정수를 $$로 반환합니다
    은행 업데이트
        밸런스 설정 = 균형 - $ 2
        여기서 AccountNo = $ 1;
    선택 1;
$$ 언어 SQL;

사용자는이 기능을 차변 계정으로 실행할 수 있습니다 17 다음과 같이 $ 100.00 :

TF1 (17, 100.0); 선택

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

함수 생성 tf1 (정수, 숫자)은 숫자를 $$로 반환합니다
    은행 업데이트
        밸런스 설정 = 균형 - $ 2
        여기서 AccountNo = $ 1;
    AccountNo = $ 1 인 은행에서 잔액을 선택하십시오.
$$ 언어 SQL;

균형을 조정하고 새로운 잔액을 반환합니다. 그만큼 same thing could be done in one command using반환:

기능 생성 TF1 (정수, 숫자) 숫자를 $$로 반환합니다
    은행 업데이트
        밸런스 설정 = 균형 - $ 2
        여기서 AccountNo = $ 1
    반환 균형;
$$ 언어 SQL;

34.4.2. SQL복합재의 기능 유형

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

테이블 emp 생성 (
    이름 텍스트,
    급여 숫자,
    나이 정수,
    칸막이 지점
);

기능을 작성하여 Double_salary (EMP)를 $$로 반환합니다
    $ 1. Salary * 2를 급여로 선택하십시오.
$$ 언어 SQL;

꿈으로 이름, double_salary (emp.*)를 선택하십시오
    emp에서
    여기서 emp.cubicle ~ = point '(2,1)';

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

구문의 사용에 주목하십시오$ 1. Salary인수의 한 분야를 선택하려면 행 값. 또한 부름이 어떻게select명령 용도*테이블의 전체 현재 행을 복합재로 선택하려면 값. 테이블 행을 대안으로 만 사용하여 참조 할 수 있습니다 테이블 이름 :

꿈으로 이름, double_salary (EMP)를 선택하십시오
    emp에서
    여기서 emp.cubicle ~ = point '(2,1)';

그러나이 사용법은 얻기가 쉽기 때문에 더 이상 사용되지 않습니다. 혼란스러운.

때로는 복합 인수를 구성하는 것이 편리합니다 금속에 가치. 이것은로 수행 할 수 있습니다.Row구성. 예를 들어, 우리는 조정할 수 있습니다 기능으로 전달되는 데이터 :

꿈으로서 이름, double_salary (행 (이름, 급여*1.1, 나이, 칸막이))을 선택하십시오
    emp에서;

반환하는 함수를 빌드 할 수도 있습니다. 복합 유형. 이것은 반환하는 함수의 예입니다. 하나의emp행 :

기능 작성 new_emp ()는 emp를 $$로 반환합니다
    이름으로 '없음'을 선택하고
        급여로 1000.0,
        25 나이로
        칸막이로서 '(2,2)';
$$ 언어 SQL;

이 예에서는 각 속성을 지정했습니다. 일정한 가치를 지니고 있지만 어떤 계산이든 이 상수로 대체되었습니다.

토토 핫 정의에 관한 두 가지 중요한 사항을 참고하십시오.

• 쿼리의 선택 목록 순서는 정확히 열이 테이블에 나타나는 것과 동일 복합 유형과 관련이 있습니다. (열의 이름 지정 우리는 위에 있었고 시스템과 관련이 없습니다.)

• 당신은 표현을 일치시키기위한 표현을 타이핑해야합니다 복합 유형의 정의 또는 오류가 발생합니다. 이와 같이:

  오류 : EMP를 반환하라고 선언 한 함수 1 열에서 텍스트 대신 varchar를 반환합니다.
  

동일한 함수를 정의하는 다른 방법은 다음과 같습니다.

기능 작성 new_emp ()는 emp를 $$로 반환합니다
    행 ( 'None', 1000.0, 25, '(2,2)') :: emp;
$$ 언어 SQL;

여기에 우리는select반환됩니다 올바른 복합 유형의 단일 열만. 이것은 아닙니다 이 상황에서는 정말 좋지만 편리한 대안입니다. 예를 들어 결과를 계산 해야하는 경우 경우에도 원하는 복합재를 반환하는 다른 함수를 호출함으로써 값.

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

select new_emp ();

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

선택 *에서 new_emp ();

 이름 | 급여 | 나이 | 작은 침실
------+--------+-----+-------
 없음 | 1000.0 |  25 | (2,2)

두 번째 방법은 더 자세히 설명합니다섹션 34.4.6.

복합 유형을 반환하는 함수를 사용하면 결과에서 하나의 필드 (속성) 만 원할 수 있습니다. 당신은 할 수 있습니다 다음과 같은 구문으로 수행하십시오.

select (new_emp ()). 이름;

 이름
------
 없음

파서를 유지하려면 여분의 괄호가 필요합니다. 혼란스러워. 당신이 그들없이 그것을하려고한다면, 당신은 다음과 같은 것 :

new_emp (). 이름을 선택하십시오.
오류 : "또는 그 근처의 구문 오류."
1 행 : SELECT NEW_EMP (). 이름;
                        ^

또 다른 옵션은 추출을 위해 기능 표기법을 사용하는 것입니다 속성. 이것을 설명하는 간단한 방법은 우리가 사용할 수 있다는 것입니다. 표기법속성 (표)andtable.attribute상호 교환 적으로.

select name (new_emp ());

 이름
------
 없음

- 이것은 다음과 같습니다.
- Emp.age <30;

EMP Where Age (EMP) <30에서 젊은이로 이름 (EMP)을 선택하십시오.

 어린이
----------
 샘
 앤디

복합 유형을 반환하는 기능을 사용하는 또 다른 방법은 다음과 같습니다. 결과를 올바른 것을 받아들이는 다른 함수로 전달하려면 입력으로 행 유형 :

function getName (EMP) 작성 텍스트를 $$로 반환합니다
    1. 이름을 선택하십시오.
$$ 언어 SQL;

getName (new_emp ())을 선택하십시오.
 getName
-------
 없음
(1 행)

복합재를 반환하는 함수를 사용하는 또 다른 방법 유형은에 설명 된대로 테이블 함수라고 부릅니다.섹션 34.4.6.

34.4.3. SQL출력 기능 매개 변수

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

함수 생성 add_em (x int, y int, out sum int)
'$ 1 + $ 2'를 선택하십시오.
언어 SQL;

add_em (3,7)을 선택하십시오.
 add_em
--------
     10
(1 행)

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

함수 생성 Sum_n_Product (x int, y int, out sum int, out product int)
'$ 1 + $ 2, $ 1 * $ 2'로 AS
언어 SQL;

 sum_n_product (11,42)에서 *를 선택하십시오.
 합계 | 제품
-----+-------
  53 |     462
(1 행)

본질적으로 일어난 일은 우리가 만든 것입니다. 함수 결과를위한 익명 복합 유형. 그만큼 위의 예는와 동일한 최종 결과를 갖습니다.

sum_prod 유형 생성 (sum int, product int);

함수 sum_n_product (int, int) sum_prod를 반환합니다
'$ 1 + $ 2, $ 1 * $ 2'로 AS
언어 SQL;

그러나 별도의 복합 유형으로 귀찮게 할 필요는 없습니다. 정의는 종종 편리합니다.

출력 매개 변수가 포함되어 있지 않다는 점에 주목하십시오 SQL에서 그러한 함수를 호출 할 때 인수 목록 호출. 이건이기 때문입니다.postgresql함수를 정의하기 위해 입력 매개 변수 만 고려합니다 서명을 호출합니다. 그것은 또한 입력만을 의미합니다 매개 변수는 목적으로 함수를 참조 할 때 중요합니다 떨어 뜨리는 것과 같은. 위의 기능을 사용하여 삭제할 수 있습니다 어느 쪽이든

드롭 토토 핫 sum_n_product (x int, y int, out sum int, out product int);
드롭 토토 핫 sum_n_product (int, int);

매개 변수는로 표시 될 수 있습니다.in( 기본),out, inout또는variadic.inout매개 변수는 둘 다 역할을합니다 입력 매개 변수 (호출 인수 목록의 일부) 및 출력 매개 변수 (결과 레코드 유형의 일부).variadic매개 변수는 입력 매개 변수이지만 다음에 설명 된대로 특별히 처리됩니다.

34.4.4. SQL가변 숫자가있는 토토 핫 인수

SQL토토 핫 일 수 있습니다 가변적 인 수의 인수를 받아들이라고 선언했습니다. 모든"선택 사항"인수가 있습니다 동일한 데이터 유형. 선택적 인수는 전달됩니다 배열로서의 기능. 토토 핫는 마킹으로 선언됩니다 마지막 매개 변수variadic; 이것 매개 변수는 배열 유형으로 선언해야합니다. 을 위한 예:

함수 생성 mleast (variadic numeric []) 숫자를 $$로 반환합니다
    generate_subscripts ($ 1, 1) g (i)에서 min ($ 1 [i])을 선택하십시오.
$$ 언어 SQL;

mleast (10, -1, 5, 4.4)를 선택하십시오.
 mleast 
--------
     -1
(1 행)

효과적으로, 모든 실제 인수는variadic위치가 모여 있습니다 당신이 작성한 것처럼 1 차원 배열

mleast (배열 [10, -1, 5, 4.4]);    - 작동하지 않습니다

당신은 실제로 그것을 쓸 수는 없습니다. 이 기능 정의와 일치하지 않습니다. 표시된 매개 변수variadic하나 이상의 일치 자체 유형이 아닌 요소 유형의 발생.

때때로 통과 할 수있는 것이 유용합니다. variadic 함수에 대한 이미 구성된 배열; 이것은 한 가지 변동 함수가 전달되기를 원할 때 특히 편리합니다. 배열 매개 변수에 대한 배열 매개 변수. 당신은 그렇게 할 수 있습니다 지정variadic전화에서 :

mleast (variadic array [10, -1, 5, 4.4]); 선택

이것은 토토 핫의 변수 매개 변수의 확장을 방지합니다 그 요소 유형으로 배열 인수를 허용합니다 정상적으로 일치하는 가치.variadiccan 기능의 마지막 실제 인수에만 첨부됩니다. 부르다.

34.4.5. SQL기본값이있는 토토 핫 인수

기능은 일부 또는 일부 또는 기본값으로 선언 할 수 있습니다. 모든 입력 인수. 기본값은 언제든지 삽입됩니다 이 기능은 실제가 충분하지 않은 실제로 호출됩니다 논쟁. 인수는 끝까지 만 생략 할 수 있기 때문입니다. 실제 인수 목록, 매개 변수의 모든 매개 변수 기본값도 기본값이 있어야합니다.

예 :

함수 생성 foo (a int, b int default 2, c int default 3)
int를 반환합니다
언어 SQL
$$로
    $ 1 + $ 2 + $ 3을 선택하십시오.
$$;

foo (10, 20, 30)를 선택하십시오.
 foo 
-----
  60
(1 줄)

foo (10, 20)를 선택하십시오.
 foo 
-----
  33
(1 줄)

foo (10)를 선택하십시오.
 foo 
-----
  15
(1 줄)

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

the=표시도 사용할 수 있습니다 핵심 단어의 장소기본값,

34.4.6. SQL테이블 소스로 기능

모든 SQL 함수는에서 사용할 수 있습니다.From쿼리 조항이지만 특히 그렇습니다 복합 유형을 반환하는 기능에 유용합니다. 함수 인 경우 기본 유형을 반환하도록 정의되며 테이블 함수는 1 열 테이블. 함수가 반환하도록 정의 된 경우 복합 유형, 테이블 함수는 각각의 열을 생성합니다. 복합 유형의 속성.

여기 예입니다.

테이블 foo (FOOD INT, FOOSUBID int, fooname 텍스트) 만들기;
INSERT INTO foo VALUES (1, 1, 'Joe');
foo 값에 삽입 (1, 2, 'ed');
foo 값에 삽입 (2, 1, 'Mary');

function getfoo (int)를 만들기 foo를 $$로 반환합니다
    FOO에서 * FOO FOOOD = $ 1;
$$ 언어 SQL;

getfoo (1)에서 *, 상단 (Fooname)을 선택하여 t1;

 거품 | foosubid | Fooname | 높은
-------+----------+---------+-------
     1 |        1 | 조 | 조
(1 행)

예에서 알 수 있듯이, 우리는 기능의 결과는 마치 일반 테이블.

우리는 토토 핫에서 하나의 행만 얻었습니다. 이것은 우리가 사용하지 않았기 때문에setof. 그게 다음 섹션에서 설명합니다.

34.4.7. SQL반환 기능 세트

SQL 함수가 반환으로 선언 될 때setofSomegeype, 함수의 최종 쿼리는 완료로 실행되며 각각 행 출력은 결과 세트의 요소로 반환됩니다.

이 기능은 일반적으로 토토 핫를 호출 할 때 사용됩니다. 그만큼From절. 이 경우 각 행 함수에 의해 반환 된 것은 질문. 예를 들어, 해당 테이블을 가정합니다foo위와 동일한 내용이 있습니다 말하다:

함수 생성 getfoo (int)는 foo를 $$로 반환합니다
    FOO에서 * FOO FOOOD = $ 1;
$$ 언어 SQL;

getfoo (1)에서 *를 선택하여 t1;

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

폼 | foosubid | Fooname
-------+----------+-------
     1 |        1 | 조
     1 |        2 | 에드
(2 줄)

열과 함께 여러 행을 반환 할 수도 있습니다. 다음과 같이 출력 매개 변수로 정의됩니다.

함수 만들기 Sum_n_Product_with_tab (x int, Out Sum int, Out upt int)는 SETOF를 $$로 반환합니다.
    $ 1 + Tab.y, $ 1 * Tab.y에서 탭에서 선택하십시오.
$$ 언어 SQL;

여기서 핵심 요점은 당신이 써야한다는 것입니다레코드를 반환함수는 단지 하나 대신 여러 행을 반환합니다. 있다면 하나의 출력 매개 변수 만 대신 해당 매개 변수 유형을 작성하십시오 의레코드.

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

함수 생성 ListChildren (텍스트)은 텍스트를 $$로 반환합니다
    부모 = $ 1 인 노드에서 이름을 선택하십시오
$$ 언어 SQL;

* 노드에서 *를 선택하십시오.
   이름 | 조상
----------+--------
 상단 |
 child1 | 맨 위
 child2 | 맨 위
 child3 | 맨 위
 하위 하일드 1 | child1
 subchild2 | child1
(6 줄)

ListChildren ( 'top')을 선택하십시오.
 목록
--------------
 child1
 child2
 child3
(3 줄)

노드에서 이름, ListChildren (이름)을 선택하십시오.
  이름 | 목록
--------+-------------
 상단 | child1
 상단 | child2
 상단 | child3
 child1 | 하위 하드 1
 child1 | subchild2
(5 줄)

마지막select출력 행이 나타납니다Child2, Child3등. 이런 일이 발생합니다ListChildren빈 세트를 반환합니다 이러한 인수의 경우 결과 행이 생성되지 않습니다.

34.4.8. SQL반환 기능테이블

기능을 반환하는 것으로 선언하는 또 다른 방법이 있습니다. 구문을 사용하는 세트반환 테이블(열). 이것은 하나 이상 사용하는 것과 동일합니다out매개 변수 플러스 기능을 반환으로 표시Setof Record(또는setof적절한 단일 출력 매개 변수 유형). 이것 표기법은 최근 SQL 표준 버전에 지정되어 있습니다. 따라서 사용하는 것보다 휴대가 가능할 수 있습니다setof.

예를 들어, 이전의 합계 및 제품 예제가 될 수 있습니다 이런 식으로 완료됩니다 :

함수 만들기 Sum_n_Product_with_tab (x int) 반환 테이블 (sum int, product int)을 $$로 반환합니다.
    $ 1 + Tab.y, $ 1 * Tab.y에서 탭에서 선택하십시오.
$$ 언어 SQL;

명시 적 사용이 허용되지 않습니다out또는inout매개 변수 와 함께테이블 리턴표기법 - 당신 모든 출력 열을에 넣어야합니다.테이블목록.

34.4.9. 다형성SQL기능

SQL함수 일 수 있습니다 다형성 유형을 받아들이고 반환하도록 선언Anylement, AnyArray, AnynonArray및AnyEnum. 보다섹션 34.2.5다형성에 대한 자세한 설명 기능. 다형성 기능은 다음과 같습니다.make_array두 개에서 배열을 구축합니다 임의의 데이터 유형 요소 :

함수 생성 make_array (Anylement, Anylement)는 anyArray를 $$로 반환합니다
    배열을 선택하십시오 [$ 1, $ 2];
$$ 언어 SQL;

intarray, make_array ( 'a':: text, 'b')로 make_array (1, 2)를 선택하여 textarray;
 intarray | TextArray
----------+-----------
 1,2 | a, b

typecast의 사용에 주목하십시오'a':: text인수가 유형텍스트. 이 경우 필요합니다 인수는 단지 문자열 문자입니다. 그렇지 않으면 유형으로 취급알 수없는및 배열알 수없는는 유효한 유형이 아닙니다. 없이 typecast, 당신은 다음과 같은 오류를 얻을 것입니다 :

오류 : 입력 유형이 "알 수없는"이기 때문에 다형성 유형을 결정할 수 없습니다.

고정 된 다형성 논증이 허용됩니다. 반환 유형이지만 대화는 아닙니다. 예를 들어:

함수 생성 IS_GREATER (Anylement, Anylement)는 부울을 $$로 반환합니다
    $ 1 $ 2를 선택하십시오.
$$ 언어 SQL;

IS_GREATER (1, 2)를 선택하십시오.
 is_greater
------------
 에프
(1 줄)

function invalid_func ()를 $$로 반환합니다
    선택 1;
$$ 언어 SQL;
오류 : 결과 데이터 유형을 결정할 수 없습니다
세부 사항 : 다형성 유형을 반환하는 함수는 하나 이상의 다형성 인수를 가져야합니다.

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

기능 DUP 작성 (F1 Anylement, Out F2 Anylement, Out F3 AnyArray)
'선택 $ 1, 배열 [$ 1, $ 1]'언어 SQL;

* DUP에서 *를 선택하십시오 (22);
 f2 |   F3
----+-------
 22 | 22,22

다형성은 변수 토토 핫와 함께 사용할 수 있습니다. 을 위한 예:

함수 생성 anyleast (variadic anyArray)는 Anylement를 $$로 반환합니다
    generate_subscripts ($ 1, 1) g (i)에서 min ($ 1 [i])을 선택하십시오.
$$ 언어 SQL;

anyleast (10, -1, 5, 4)를 선택하십시오.
 anyleast 
---------
       -1
(1 줄)

anyleast ( 'abc':: text, 'def')를 선택하십시오.
 anyleast 
---------
 알파벳
(1 줄)

함수 concat (텍스트, variadic anyArray)를 작성하십시오 텍스트를 $$로 반환합니다
    array_to_string ($ 2, $ 1)을 선택하십시오.
$$ 언어 SQL;

CONCAT ( '|', 1, 4, 2)를 선택하십시오.
 콘서트 
--------
 1 | 4 | 2
(1 행)