PostgreSQL 8.3.3문서
Prev Fast Backward Chapter 33. SQL 확장 Fast Forward Next

33.4. 쿼리 언어(SQL) 함수

SQL 함수는, 임의의 SQL쿼리 리스트를 실행해, 그 리스트의 마지막 쿼리의 결과를 돌려줍니다. 단순한(비집합의) 경우, 마지막 쿼리의 결과의 최초의 행이 돌려주어집니다 (복수행의 결과 가운데 "최초의 행"은, ORDER BY를 사용하지 않는 한 붙이지 못하는 것을 기억해주세요). 마지막 쿼리가 아무것도 행을 돌려주지 않을 때는 NULL값이 돌려주어집니다.

그 밖에도, SQL 함수는, SETOF sometype 형을 돌려주도록 지정하는 것으로써, 집합을 돌려주도록 선언할 수도 있습니다. 이 경우, 마지막 쿼리의 결과의 모든 행이 돌려주어집니다. 자세한 것은 다음에 설명합니다.

SQL 함수의 본체는, 세미콜론으로 단락지은 SQL명령문의 리스트가 아니면 안됩니다. 마지막 명령문 뒤의 세미콜론은 생략 가능합니다. 함수가 void를 돌려주도록 선언되어 있지 않은 한, 마지막 명령문은 SELECT가 아니면 안됩니다.

SQL언어로 작성된, 임의의 명령 군은 정리해, 함수로서 정의할 수가 있습니다. SELECT쿼리 외에, 데이터 변경용의 쿼리(즉, INSERT, UPDATE, DELETE)나 그 외의 SQL 명령을 포함할 수가 있습니다. (예외가 1개있습니다. 그것은 BEGIN, COMMIT, ROLLBACK, SAVEPOINT를 SQL함수에 포함할 수 없는 점입니다.) 그러나, 마지막 명령은, 함수의 반환값형으로 정의한 것을 돌려주는 SELECT가 아니면 안됩니다. 그 외에도, 어떤 동작을 시키지만, 유용한 값을 돌려주지 않는 SQL 함수를 정의하고 싶은 것이면, void를 돌려주는 것이라고 정의하는 것으로 실현 가능합니다. 그 경우, 함수 본체는 SELECT로 끝나지 않습니다. 예를 들어, 이하의 함수는 emp테이블에서 부의 급료가 되고 있는 행을 삭제합니다. 

CREATE FUNCTION clean_emp() RETURNS void AS '
    DELETE FROM emp
        WHERE salary < 0;
' LANGUAGE SQL;

SELECT clean_emp();

 clean_emp
-----------

(1 row)

CREATE FUNCTION명령의 구문에서는, 함수 본체는 문자열 정수로서 작성될 필요가 있습니다. 이 문자열 정수의 기술에는, 통상, 달러 인용부호를 붙이는(Section 4.1.2.2)것이 가장 편리합니다. 문자열 정수를 단일 인용부호로 괄통상의 구문에서는, 함수 본체중에서 사용되는 단일 인용부호(')와 backslash(\)(이스케이프 문자열 구문을 가정)를, 일반적으로, 이 문자를 이중 인용부호 처리하지 않으면 안됩니다(Section 4.1.2.1를 참조).

SQL 함수의 인자는 함수 본체내에서 $ n 라고 하는 구문을 이용해 나타낼 수가 있습니다. 즉, $1은 제1 인자를 나타내, $2는 제2 인자와 같이 됩니다. 인자가 복합형인 경우, $1. name와 같은 도트 표기를 이용해 인자의 속성에 접근 할 수가 있습니다. 이 인자는, 식별자로서가 아니고, 데이터값으로 보고 사용할 수 있습니다. 따라서, 예를 들면 

INSERT INTO mytable VALUES ($1);

(은)는 올바른 것입니다만, 이하는 동작하지 않습니다. 

INSERT INTO $1 VALUES (42);

33.4.1. 기본형을 사용하는SQL함수

가장 간단한 SQL함수는, 인자를 취하지 않고 단지 integer와 같은 기본형을 돌려주는 것입니다. 

CREATE FUNCTION one() RETURNS integer AS $$
    SELECT 1 AS result;
$$ LANGUAGE SQL;

-- 문자열 리터럴의 다른 구문에서는
CREATE FUNCTION one() RETURNS integer AS '
    SELECT 1 AS result;
' LANGUAGE SQL;

SELECT one();

 one
-----
   1

함수 본체내에서 함수의 결과로 열의 가명을 (result라는 이름으로) 정의했던 것에 주목해 주세요. 그러나 이 열의 가명은 이 함수 밖에서는 볼 수 없습니다. 따라서, 그 결과는 result는 아니고, one라는 라벨로 표시되고 있습니다.

기본형을 인자로서 취하는, SQL함수를 정의하는 것은 대부분의 경우 간단합니다. 이하의 예에 대해, 함수 내부에서 그 인자를 $1과 $2로서 접근하고 있는 것에 주의해 주세요. 

CREATE FUNCTION add_em(integer, integer) RETURNS integer AS $$
    SELECT $1 + $2;
$$ LANGUAGE SQL;

SELECT add_em(1, 2) AS answer;

 answer
--------
      3

이하에 좀 더 도움이 되는 함수를 나타냅니다. 이것은 은행 계좌로부터 의 자동 납부에 사용할 수 있습니다. 

CREATE FUNCTION tf1 (integer, numeric) RETURNS integer AS $$
    UPDATE bank 
        SET balance = balance - $2
        WHERE accountno = $1;
    SELECT 1;
$$ LANGUAGE SQL;

이하와 같이, 사용자는 이 함수를 사용해, 계좌 번호 17에서 100 달러를 꺼내는 것이 가능합니다. 

SELECT tf1(17, 100.0);

실제로, 함수의 결과를 정수 1보다 알기 쉬운 형태로 하기 때문에, 이하와 같이 정의하면 좋을 것입니다. 

CREATE FUNCTION tf1 (integer, numeric) RETURNS numeric AS $$
    UPDATE bank 
        SET balance = balance - $2
        WHERE accountno = $1;
    SELECT balance FROM bank WHERE accountno = $1;
$$ LANGUAGE SQL;

이것은 잔고를 조정해, 갱신 후의 잔고를 돌려줍니다.

33.4.2. 복합형을 사용하는SQL함수

함수의 인자에 복합형을 기술했을 경우, (지금까지 해온 $1와 $2와 같이) 필요한 인자만을 지정하는 것 만이 아니고, 필요로 하는 인자의 속성(필드)도 지정할 필요가 있습니다. 예를 들면, emp가 종업원 데이터를 가지는 테이블로 하면, 이 이름은 그 테이블내의 각 행을 나타내는 복합형의 이름이기도 합니다. 이하에 나타내는 double_salary함수는, 해당하는 종업원의 급료를 배로 증가시킵니다. 

CREATE TABLE emp (
    name        text,
    salary      numeric,
    age         integer,
    cubicle     point
);

CREATE FUNCTION double_salary(emp) RETURNS numeric AS $$
    SELECT $1.salary * 2 AS salary;
$$ LANGUAGE SQL;

SELECT name, double_salary(emp.*) AS dream
    FROM emp
    WHERE emp.cubicle ~= point '(2,1)';

 name | dream
------+-------
 Bill |  8400

$1. salary라는 구문을 사용해, 인자의 행값의 1 필드를 선택하고 있는 것에 주목해 주세요. 또, *를 사용한 SELECT명령의 호출에서는, 복합형의 값으로서 현재의 테이블행 전체를 나타내는 테이블명을 사용하고 있음에도 주목해 주세요. 다른 방법으로서 테이블행은 이하와 같이 테이블명만을 사용해 참조할 수 있습니다. 

SELECT name, double_salary(emp) AS dream
    FROM emp
    WHERE emp.cubicle ~= point '(2,1)';

그러나, 이 사용 방법은, 혼란하기 쉽기 때문에 추천하지 않습니다.

그 자리에서 복합형의 인자값을 작성하는 것이 편리한 경우가 있습니다. 이것은 ROW식에서 실시할 수가 있습니다. 예를 들면, 이하와 같이 해 함수에 건네주는 데이터를 조정할 수 있습니다. 

SELECT name, double_salary(ROW(name, salary*1.1, age, cubicle)) AS dream
    FROM emp;

복합형을 돌려주는 함수를 작성하는 일도 가능합니다. 이하에 단일의 emp행을 돌려주는 함수의 예를 나타냅니다. 

CREATE FUNCTION new_emp() RETURNS emp AS $$
    SELECT text 'None' AS name,
        1000.0 AS salary,
        25 AS age,
        point '(2,2)' AS cubicle;
$$ LANGUAGE SQL;

여기에서는, 각 속성을 정수로 지정하고 있습니다만, 이 정수를 어떠한 연산에 옮겨놓을 수도 있습니다.

함수를 정의하는데 있어서, 두 가지의 중요한 주의점을 이하에 나타냅니다.

같은 함수를 이하와 같이 정의할 수도 있습니다. 

CREATE FUNCTION new_emp() RETURNS emp AS $$
    SELECT ROW('None', 1000.0, 25, '(2,2)')::emp;
$$ LANGUAGE SQL;

여기서, 올바른 복합형의 단일의 열을 단지 돌려주는 SELECT를 기술했습니다. 너무 좋은 해법이라고는 말할 수 없습니다만, 예를 들면, 필요한 복합값을 돌려주는 다른 함수를 호출해 결과를 계산해야 하는 경우 등, 편리한 해법이 되는 일이 있습니다.

이하의 2개의 방법으로 이 함수를 직접 호출할 수 있습니다. 

SELECT new_emp();

         new_emp
--------------------------
 (None,1000.0,25,"(2,2)")

SELECT * FROM new_emp();

 name | salary | age | cubicle
------+--------+-----+---------
 None | 1000.0 |  25 | (2,2)

2번째의 방법에 대해서는, Section 33.4.4에서 보다 자세하게 설명합니다.

SQL식에서 행(복합형)을 돌려주는 함수를 호출할 때에, 그 결과로부터 1개의 필드(속성)만을 사용하고 싶은 경우가 있습니다. 이것은, 이하와 같은 구문으로 실시할 수가 있습니다. 

SELECT (new_emp()).name;

 name
------
 None

파서가 혼란하지 않게, 괄호를 추가할 필요가 있습니다. 괄호없이 실시하려고 하면, 이하와 같은 결과가 됩니다. 

SELECT new_emp().name;
ERROR:  syntax error at or near "." at character 17
LINE 1: SELECT new_emp().name;
                        ^

또, 함수표기를 사용해 속성을 추출할 수도 있습니다. 간단하게 설명하면, attribute(table)와 table.attribute라는 표기 방법에 사용할 수 있는 것입니다. 

SELECT name(new_emp());

 name
------
 None

-- 이것은
-- SELECT emp.name AS youngster FROM emp WHERE emp.age < 30;
-- (와)과 동일합니다.

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

 youngster
-----------
 Sam
 Andy

Tip: 함수표기와 속성 표기의 동등성에 의해, "계산 처리된 필드"를 모방하기 위해서 복합형에 대한 함수를 사용할 수 있습니다. "computed fields". 예를 들면, 상기로 정의된 double_salary(emp)를 사용해, 다음과 같이 기술할 수가 있습니다. 

SELECT emp.name, emp.double_salary FROM emp;

이것을 이용하는 어플리케이션은, double_salary가 실제로는 테이블의 열이 아닌 것을 직접 신경쓸 필요는 없습니다 (또, 뷰의 계산 처리된 필드를 모방하는 일도 가능합니다).

복합형을 결과적으로 돌려주는 함수를 사용하는 다른 방법은, 그 결과를, 그 행형을 입력으로서 받아들이는 함수에 건네주는, 이하와 같은 방법입니다. 

CREATE FUNCTION getname(emp) RETURNS text AS $$
    SELECT $1.name;
$$ LANGUAGE SQL;

SELECT getname(new_emp());
 getname
---------
 None
(1 row)

복합형의 결과를 돌려주는 함수의 사용 방법으로서 그 밖에도, 테이블 함수로서 호출하는 방법이 있습니다. 이것은 Section 33.4.4으로 설명합니다.

33.4.3. 출력 매개변수를 가지는 함수

함수의 결과의 기술 방법에는, 그 밖에도 출력 매개변수를 사용해 정의하는 방법이 있습니다. 이하에 예를 나타냅니다. 

CREATE FUNCTION add_em (IN x int, IN y int, OUT sum int)
AS 'SELECT $1 + $2'
LANGUAGE SQL;

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

Section 33.4.1에서 가리킨add_em판과 기본적인 차이는 없습니다. 복수열을 돌려주는 함수를 정의하는 간단한 방법을 제공하는 것이 출력 매개변수의 본래의 가치입니다. 이하에 예를 나타냅니다. 

CREATE FUNCTION sum_n_product (x int, y int, OUT sum int, OUT product int)
AS 'SELECT $1 + $2, $1 * $2'
LANGUAGE SQL;

 SELECT * FROM sum_n_product(11,42);
 sum | product
-----+---------
  53 |     462
(1 row)

이것은 기본적으로, 함수 결과용의 익명의 복합형의 작성을 실시합니다. 위의 예에서는, 

CREATE TYPE sum_prod AS (sum int, product int);

CREATE FUNCTION sum_n_product (int, int) RETURNS sum_prod
AS 'SELECT $1 + $2, $1 * $2'
LANGUAGE SQL;

그러나, 독립한 복합형 정의에 골치를 썩는 일이 없어져, 편리하다라고 말할 수 있습니다.

SQL로부터 함수를 호출할 때, 출력 매개변수가 호출해 옆의 인자 리스트에 포함되지 않는 것에 주의해 주세요. PostgreSQL가, 입력 매개변수만이 함수의 호출 서명을 정의하기 때문에입니다. 이것은 또, 함수를 삭제하기 위해서 함수를 참조하는 경우, 입력 매개변수만이 고려되는 것을 의미하고 있습니다. 위의 함수는, 다음의 몇개의 방법으로 삭제할 수가 있습니다. 

DROP FUNCTION sum_n_product (x int, y int, OUT sum int, OUT product int);
DROP FUNCTION sum_n_product (int, int);

매개변수에는, IN (기본값), OUT 또는 INOUT라는 표시를 부여할 수 있습니다. INOUT매개변수는, 입력 매개변수(호출 인수 리스트의 일부)와 출력 매개변수(결과의 레코드형의 일부분)의 양쪽 모두를 제공합니다.

33.4.4. 테이블 소스로서의 SQL함수

모든 SQL 함수는 쿼리의 FROM구로 사용할 수 있습니다만, 복합형을 돌려주는 함수에 특히 편리합니다. 함수가 기본형을 돌려주도록 정의되고 있는 경우, 테이블 함수는 1열로부터 되는 테이블을 작성합니다. 함수가 복합형을 돌려주도록 정의되고 있는 경우, 테이블 함수는 복합형의 열의 각각 대해 1개의 열을 작성합니다.

이하에 예를 나타냅니다. 

CREATE TABLE foo (fooid int, foosubid int, fooname text);
INSERT INTO foo VALUES (1, 1, 'Joe');
INSERT INTO foo VALUES (1, 2, 'Ed');
INSERT INTO foo VALUES (2, 1, 'Mary');

CREATE FUNCTION getfoo(int) RETURNS foo AS $$
    SELECT * FROM foo WHERE fooid = $1;
$$ LANGUAGE SQL;

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

 fooid | foosubid | fooname | upper
-------+----------+---------+-------
     1 |        1 | Joe     | JOE
(1 row)

예에서 보여주는 대로, 함수의 결과의 열을 보통의 테이블의 열과 같이 취급할 수 있습니다.

이 함수의 결과 얻을 수 있던 것은 1행뿐인 것에 주의해 주세요. 이것은 SETOF를 지정하지 않았기 때문입니다. 이것에 대해서는 다음 섹션에서 설명합니다.

33.4.5. 집합을 돌려주는SQL함수

SQL 함수가 SETOF sometype 를 돌려주도록 선언되고 있는 경우, 함수의 마지막 SELECT쿼리는 끝까지 실행되어 각 출력행은 결과 집합의 요소로서 돌려주어집니다.

이 기능은 통상, 함수를 FROM구내에서 호출할 때에 사용됩니다. 이 경우, 함수에 의해 돌려주어지는 각 행은, 쿼리에 의해 보이는 테이블의 행이 됩니다. 예를 들면, 테이블foo의 내용이 상기와 같으면 이하와 같이 됩니다. 

CREATE FUNCTION getfoo(int) RETURNS SETOF foo AS $$
    SELECT * FROM foo WHERE fooid = $1;
$$ LANGUAGE SQL;

SELECT * FROM getfoo(1) AS t1;

이 출력은 이하와 같습니다. 

 fooid | foosubid | fooname
-------+----------+---------
     1 |        1 | Joe
     1 |        2 | Ed
(2 rows)

현재, 집합을 돌려주는 함수는 쿼리의 선택 리스트내에서도 호출할 수가 있습니다. 쿼리 자신에 의해 생성하는 각 행에 대해, 집합을 돌려주는 함수가 불려 가 함수의 결과 집합의 각 요소에 대해서 출력행이 생성됩니다. 다만, 이 기능은 현재는 추천 되지 않고, 향후의 릴리스에서는 삭제될 가능성이 있습니다. 이하는, 선택 리스트로부터 집합을 돌려주는 함수의 예입니다. 

CREATE FUNCTION listchildren(text) RETURNS SETOF text AS $$
    SELECT name FROM nodes WHERE parent = $1
$$ LANGUAGE SQL;

SELECT * FROM nodes;
   name    | parent
-----------+--------
 Top       |
 Child1    | Top
 Child2    | Top
 Child3    | Top
 SubChild1 | Child1
 SubChild2 | Child1
(6 rows)

SELECT listchildren('Top');
 listchildren
--------------
 Child1
 Child2
 Child3
(3 rows)

SELECT name, listchildren(name) FROM nodes;
  name  | listchildren
--------+--------------
 Top    | Child1
 Top    | Child2
 Top    | Child3
 Child1 | SubChild1
 Child1 | SubChild2
(5 rows)

마지막 SELECT에 대해, Child2와Child3등이 출력행에 표시되어 있지 않은 것에 주의해 주세요. 이것은, listchildren가 이 입력에 대해서 빈값의 집합을 돌려주어, 출력행이 생성되지 않기 때문입니다.

33.4.6. 다형SQL함수

SQL함수는, 다형 anyelement, anyarray, anynonarray 및 anyenum를 받아들여 돌려주도록 선언할 수가 있습니다. 다형 함수의 상세 설명에 대해서는 Section 33.2.5을 참조해 주세요. 이하의 make_array다형 함수는, 임의의 2개의 데이터형 요소로부터 배열을 작성합니다. 

CREATE FUNCTION make_array(anyelement, anyelement) RETURNS anyarray AS $$
    SELECT ARRAY[$1, $2];
$$ LANGUAGE SQL;

SELECT make_array(1, 2) AS intarray, make_array('a'::text, 'b') AS textarray;
 intarray | textarray
----------+-----------
 {1,2}    | {a,b}
(1 row)

'a'::text라고 하는 형태 캐스트를 사용해, 인자가 text형인 것을 지정하고 있는 것에 주목해 주세요. 이것은 인자가 단순한 문자열 리터럴인 경우에 필요합니다. 그렇지 않으면, unknown형으로서 다루어지기 때문에, 무효인 unknown의 배열을 돌려주려고 끝냅니다. 형태 캐스트가 없으면 이하와 같은 에러가 발생합니다. 

ERROR:  could not determine "anyarray"/"anyelement" type because input has type "unknown"

고정의 반환값형으로 다형 인수를 가지는 것은 허용됩니다만, 역은 허용되지 않습니다. 이하에 예를 나타냅니다. 

CREATE FUNCTION is_greater(anyelement, anyelement) RETURNS boolean AS $$
    SELECT $1 > $2;
$$ LANGUAGE SQL;

SELECT is_greater(1, 2);
 is_greater
------------
 f
(1 row)

CREATE FUNCTION invalid_func() RETURNS anyelement AS $$
    SELECT 1;
$$ LANGUAGE SQL;
ERROR:  cannot determine result data type
DETAIL:  A function returning "anyarray" or "anyelement" must have at least one argument of either type.

출력 인자를 가지는 함수에서도 다형성을 사용할 수가 있습니다. 이하에 예를 나타냅니다. 

CREATE FUNCTION dup (f1 anyelement, OUT f2 anyelement, OUT f3 anyarray)
AS 'select $1, array[$1,$1]' LANGUAGE sql;

SELECT * FROM dup(22);
 f2 |   f3
----+---------
 22 | {22,22}
(1 row)

Prev Home Next
사용자 정의 함수 Up 함수의 오버로드