12.2. 쿼리 토토(SQL) 기능

12.2.1. 예시

간단한 SQL 함수를 설명하려면 다음을 고려하십시오. 은행 계좌에서 인출하는 데 사용될 수 있습니다.

CREATE FUNCTION tp1 (정수, 숫자) RETURNS 정수 AS '
    업데이트 뱅크 
        SET 잔액 = 잔액 - $2
        어디에 accountno = $1;
    1을 선택하세요.
' 언어 SQL;

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

tp1 선택(17, 100.0);

실제로는 더 유용한 결과를 원할 것입니다. 상수보다 함수에서"1", 따라서 더 그럴듯한 정의는

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

잔액을 조정하고 새 잔액을 반환합니다.

다음 명령 모음SQL토토는 함께 패키지될 수 있으며 함수로 정의됩니다. 명령에는 데이터가 포함될 수 있습니다. 수정(예:삽입, 업데이트및삭제) 글쎄요선택쿼리. 그러나 최종 명령은 다음과 같아야 합니다.선택그것 함수의 반환으로 지정된 모든 것을 반환합니다. 유형.

CREATE FUNCTION clean_EMP () 정수를 '로 반환합니다.
    EMP에서 삭제 
        EMP.salary <= 0;
    SELECT 1 ASignore_this;
' 언어 SQL;

clean_EMP()를 선택하세요;

x
---
 1

12.2.2. SQL베이스의 기능 유형

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

CREATE FUNCTION one()은 정수 AS '를 반환합니다.
    결과로 1을 선택하십시오.
' 언어 SQL;

하나를 선택하세요();

하나
-----
   1

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

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

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

SELECT add_em(1, 2) AS 답변;

답변
--------
      3

12.2.3. SQL복합체의 기능 유형

복합 유형의 인수로 함수를 지정할 때, 우리는 우리가 원하는 인수를 지정해야 할 뿐만 아니라 (우리가 그랬던 것처럼) 위와 같이$1그리고$2) 뿐만 아니라 해당 인수의 속성도 포함됩니다. 예를 들어 다음과 같이 가정해 보겠습니다.EMP테이블입니다 직원 데이터를 포함하므로 직원의 이름도 포함됩니다. 테이블의 각 행의 복합 유형입니다. 다음은 함수입니다.double_salary무엇을 계산하는지 당신의 급여가 두 배가 된다면:

CREATE FUNCTION double_salary(EMP) 정수 AS '를 반환합니다.
    SELECT $1.급여 * 2 AS 급여;
' 언어 SQL;

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

이름 | 꿈
------+---------
 샘 |  2400

구문 사용에 유의하세요$1.급여인수의 필드 하나를 선택하려면 행 값. 또한 호출 방법에 주목하세요.선택명령은 테이블 이름을 사용하여 해당 테이블의 전체 현재 행을 복합 값으로 사용합니다.

다음을 반환하는 함수를 만드는 것도 가능합니다 복합형. (그러나 아래에서 볼 수 있듯이 몇 가지 사항이 있습니다. 안타깝게도 함수 사용 방법에 제한이 있습니다.) 단일을 반환하는 함수의 예입니다.EMP행:

함수 생성 new_emp()는 EMP를 '로 반환합니다.
    SELECT 텍스트 ''없음'' AS 이름,
        AS연봉 1000,
        25 AS나이,
        포인트 ''(2,2)'' AS 칸막이;
' 언어 SQL;

이 경우 우리는 각 속성을 상수 값이지만 모든 계산이나 표현식은 이 상수로 대체되었습니다. 중요한 두 가지 사항을 참고하세요. 함수 정의에 대해:

• 대상 목록 순서는 그것과 정확히 동일해야 합니다 다음과 연관된 테이블에 열이 표시됩니다. 복합 유형입니다.

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

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

현재 릴리스에서는PostgreSQL불쾌한 일이 좀 있어요 복합 유형을 반환하는 함수의 제한 사항 사용. 간단히 말해서, 행을 반환하는 함수를 호출할 때 우리는 전체 행을 검색할 수 없습니다. 우리는 단일 프로젝트를 계획해야 합니다. 행에서 속성을 지정하거나 전체 행을 다른 행에 전달 기능. (전체 행 값을 표시하려고 하면 의미 없는 숫자입니다.) 예를 들어,

이름 선택(new_emp());

이름
------
 없음

이 예에서는 다음에 대한 함수 표기법을 사용합니다. 프로젝션 속성. 이를 설명하는 간단한 방법은 다음과 같습니다. 우리는 일반적으로 표기법을 사용할 수 있습니다속성(테이블)그리고테이블.속성교환 가능:

--
-- 이는 다음과 같습니다.
-- EMP.name AS youngster FROM EMP WHERE EMP.age < 30을 선택하세요.
--
SELECT 이름(EMP) AS 어린아이
    EMP에서
    WHERE 연령(EMP) < 30;

젊은이
-----------
 샘

일반적으로 함수 구문을 사용해야 하는 이유 함수 반환 값의 속성을 투영하는 방법은 파서는 투영을 위한 도트 구문을 이해하지 못합니다. 함수 호출과 결합될 때.

SELECT new_emp().name AS none;
오류: 파서: "." 근처에서 구문 분석 오류가 발생했습니다.

행 결과를 반환하는 함수를 사용하는 또 다른 방법은 다음과 같습니다. 행 유형 매개변수를 허용하는 두 번째 함수를 선언하고 함수 결과를 전달합니다.

CREATE FUNCTION getname(emp) 텍스트를 다음과 같이 반환합니다.
'$1.이름 선택;'
언어 SQL;

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

12.2.4. SQL함수 반환 세트

앞서 언급한 바와 같이 SQL 함수는 다음과 같이 선언될 수 있습니다. 돌아오는 중SETOF어떤 유형. 이 경우에는 함수의 마지막선택쿼리는 다음과 같습니다 실행이 완료되고 출력되는 각 행은 다음과 같이 반환됩니다. 집합의 요소입니다.

세트를 반환하는 함수는 대상에서만 호출될 수 있습니다. a의 목록선택질의. 각 행에 대해 그선택스스로 생성되며, 집합을 반환하는 함수가 호출되고 출력 행은 다음과 같습니다. 함수 결과 집합의 각 요소에 대해 생성됩니다. 안 예:

CREATE FUNCTION listchildren(text) SETOF 텍스트를 AS로 반환합니다.
'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등. 이런 일이 발생하는 이유는목록어린이빈 세트를 반환합니다. 해당 입력에 대해서는 출력 행이 생성되지 않습니다.