SQL 함수는 여러 개의 SQL 구문을 한 번에 실행 할 수 있으며,
그 구문들 가운데 마지막 실행 된 구문의 결과를 반환 한다.
(집합을 반환하지 않는) 단순 SQL 함수는 마지막 쿼리의
결과 가운데 첫 로우를 반환한다.
(“첫번째 로우”는 ORDER BY
구문을 사용하지 않는 이상 항상 같은 로우를 반환하지 않을 수도
있음에 주의해야 한다.)
실행 된 마지막 쿼리의 결과값이 없는 경우는 null을
반환한다.
또 달리, SQL 함수가 집합(다중 로우)을 반환 하도록 지정할 수도 있다.
이렇게 하려면, 함수 만들기 구문의 반환값을 지정할 때,
SETOF 구문을
사용하거나, 임의자료형RETURNS TABLE(
구문을 사용한다. 이런 형태의 함수인 경우는 그 마지막 실행되는
쿼리의 모든 결과를 반환한다. 보다 자세한 내용은 아래에서 다룬다.
칼럼들)
SQL 함수의 내용은 세미콜론으로 구분되는
SQL 구문들로 구성되어야 한다. 마지막 SQL 구문 뒤에는
세미콜론을 써도 되고 안 써도 된다.
함수 반환값으로 void를 지정하지 않았다면,
함수 내용의 마지막 쿼리는 SELECT 명령이어야 하고,
INSERT, UPDATE, DELETE
명령이라면, RETURNING 구문이 있어야 한다.
SQL로 된 어떤 명령들도 한 함수에서
함께 사용될 수 있다. SELECT 쿼리 뿐만 아니라,
자료를 변경 하는 쿼리들 (INSERT,
UPDATE, 및 DELETE 쿼리들)
도 함수 내용으로 사용할 수 있으며, 또한 다른 쿼리들도
사용할 수 있다. (여기서 기억해야 할 것은
트랜잭션을 제어야하는 COMMIT, SAVEPOINT
같은 명령과, VACUUM 명령은 SQL
함수 안에서는 사용할 수 없다.)
반면, 함수 내용의 마지막에 오는 명령은
SELECT 명령이나,
함수의 반환 자료형과 일치하는 RETURNING 구문을
포함하는 어떤 명령이어야 한다.
부가적으로, 만일 어떤 작업을 수행하는데, 특별히 반환해야할
자료형이 없다면, 함수를 정의할 때 반환값으로 void를
지정한다.
다음 예제는 emp 테이블에서
판매액이 음수인 로우를 지우는 함수를 만들고 실행한 것이다:
CREATE FUNCTION clean_emp() RETURNS void AS '
DELETE FROM emp
WHERE salary < 0;
' LANGUAGE SQL;
SELECT clean_emp();
clean_emp
-----------
(1 row)
함수 내용으로 지정된 각 명령은 그 명령이 실행 되는 시점에
구문 분석을 한다. 함수의 내용으로 시스템 카탈로그 정보를 변경하는
작업 (예, CREATE TABLE)이 있다면,
이 작업의 최종 반영은 해당 함수 내용이 모두 정상적으로 진행 되었을 때
이루워진다. 예를 들어,
CREATE TABLE foo (...); INSERT INTO foo VALUES(...);
이런 형태로 함수가 만들어졌다면, INSERT 작업을 할 때,
foo 테이블이 없다며 오류를 낼 것이다. 이런 형태의
함수를 원한다면, PL/pgSQL 함수로 작성하는 것이 좋다.
CREATE FUNCTION 명령에서 함수 내용은 하나의
문자열로 정의한다. 즉, 그 문자열 안에 어떤 문자열 정의가 필요하다면,
그것을 위해 문자열 지정 문자(' 작은 따옴표)를 하나 더 써야 한다.
(4.1.2.1절 참고)
일반적으로 이런 번거로움을 피하기 위해서, 달러 문자를 이용한
문자열 표기 규칙을 사용한다(이것에 대한 자세한 이야기는 4.1.2.4절을 참고).
SQL 함수의 인자들은 함수 내용에서 그 이름 기반이나, 번호 기반으로 참조 될 수 있다. 두 방법에 대한 예제는 아래에 나온다.
함수 인자로 이름을 사용하려면, 그냥 함수 내용 안에서 그 이름을 적당한
위치에서 사용하면 된다. 만일 그 이름이 칼럼 이름과 똑 같다면,
해당 칼럼 값이 먼저 사용된다. 그래도 같은 이름을 사용하고 싶다면,
함수이름.인자이름
오래된 번호 기반 함수 인자 사용법은 $
형태다: n$1은 그 함수의 첫번째 인자를 뜻하며,
$2는 두번째 인자를 뜻한다. 이런 함수 인자 참조는
그 함수 인자를 이름 기반으로 지정했을 때도 그대로 사용할 수 있다.
인자가 복합 자료형이라면, 함수 내용에서
argname.fieldname$1.
형태로 사용한다. 물론
인자 이름에서 충돌이 생긴다면, 함수 이름을 함께 사용해서 그 충돌을
피한다.
fieldname
SQL 함수 인자는 식별자로 사용될 수 없다. 오직 자료의 값으로만 사용될 수 있다. 다음 예제는 그 대표적인 예다:
INSERT INTO mytable VALUES ($1);
윗 구문은 정상적이지만, 아래 구문은 오류를 낸다.
INSERT INTO $1 VALUES (42);
이름 기반 함수 인자 사용은 PostgreSQL 9.2
버전부터 제공한다. 그 이전 버전에서는
$ 형태의 번호 기반 인자만 사용할 수 있다.
n
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 함수의 대표적인 예다:
CREATE FUNCTION add_em(x integer, y integer) RETURNS integer AS $$
SELECT x + y;
$$ LANGUAGE SQL;
SELECT add_em(1, 2) AS answer;
answer
--------
3
이 함수를 다시 번호 기반 인자를 사용하면 다음과 같다:
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 (accountno integer, debit numeric) RETURNS numeric AS $$
UPDATE bank
SET balance = balance - debit
WHERE accountno = tf1.accountno;
SELECT 1;
$$ LANGUAGE SQL;
이 함수를 이용해 17번 고객의 통장에서 100 달러를 출금하는 작업:
SELECT tf1(17, 100.0);
이 예제를 보면, 함수의 첫번째 인자인 accountno 인자 이름이
bank 테이블의 칼럼 이름으로도 사용하고 있다면,
충돌이 일어난다. UPDATE
명령에서 인자 값을 전달하기 위해서는
bank.accountno, tf1.accountno 같이
각각 구분해서 사용해서 사용해야 한다.
좀 더 유연한 함수로 반환값을 무조건 1로 넘기는 것이 아니라, 잔액을 반환하도록 하려면 다음과 같이 만들 수 있다:
CREATE FUNCTION tf1 (accountno integer, debit numeric) RETURNS numeric AS $$
UPDATE bank
SET balance = balance - debit
WHERE accountno = tf1.accountno;
SELECT balance FROM bank WHERE accountno = tf1.accountno;
$$ LANGUAGE SQL;
윗 함수는 다시 아래와 같이
RETURNING 구문을 이용해서 보다 깔끔하게 만들 수도 있다:
CREATE FUNCTION tf1 (accountno integer, debit numeric) RETURNS numeric AS $$
UPDATE bank
SET balance = balance - debit
WHERE accountno = tf1.accountno
RETURNING balance;
$$ LANGUAGE SQL;
If the final SELECT or RETURNING
clause in a SQL function does not return exactly
the function's declared result
type, PostgreSQL will automatically cast
the value to the required type, if that is possible with an implicit
or assignment cast. Otherwise, you must write an explicit cast.
For example, suppose we wanted the
previous add_em function to return
type float8 instead. It's sufficient to write
CREATE FUNCTION add_em(integer, integer) RETURNS float8 AS $$
SELECT $1 + $2;
$$ LANGUAGE SQL;
since the integer sum can be implicitly cast
to float8.
(See 10장 or CREATE CAST
for more about casts.)
복합 자료형을 함수의 인자로 사용 할 때, 그 인자는 필요한 인자값 뿐만
아니라, 해당 인자에 따른 모든 속성(필드)을 포함 한다는 것을
기억해 둘 필요가 있다. 예를 들어, 사원 자료가 emp
테이블에 있고 그 테이블 자체를 함수의 인자로 사용한다면,
그 함수는 해당 테이블의 한 로우를 전체를 인자로 사용한다.
아래에서 누군가의 매출에 대한 그 두 배를 구하는
double_salary 함수를 이 예제로 소개하고 있다:
CREATE TABLE emp (
name text,
salary numeric,
age integer,
cubicle point
);
INSERT INTO emp VALUES ('Bill', 4200, 45, '(2,1)');
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 형태로 사용한다.
또한 이 함수를 사용할 때, 입력 인자는
SELECT 명령에서 테이블이름.*
문자를 사용해서 해당 로우 전체를 지정한다.
물론 위 예제와 같은 경우라면, 아래와 같이 참조하는
테이블 이름만을 인자로 사용할 수 있다.
SELECT name, double_salary(emp) AS dream
FROM emp
WHERE emp.cubicle ~= point '(2,1)';
하지만 이 방식은 읽는데 혼란을 초래해 그리 권장하지는 않는다. (테이블 로우의 복합 자료형 값을 표시하는 두가지 방법에 대한 자세한 설명은 8.16.5절에 있다.)
가끔 즉석에서 복합 자료형 형태의 인자를 만들어서 함수의
인자로 사용할 때도 있다. 이럴 때는 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;
이 예제에서 처럼 반환하는 자료형은 emp 테이블의
속성과 일치해야 정상 작동하며, 물론 그 값은 위와 같이
각 칼럼의 값으로 임의 상수를 지정할 수도 있다.
여기서 이런 함수의 두 가지 속성이 있음을 기억해야 한다:
쿼리에서 지정한 칼럼 순서는 복합 자료형의 칼럼 순서와 정확하게 같아야 한다. (칼럼 이름은 위에서 설명한 대로 실재 반환할 때의 반환 자료형을 이름을 따른다.)
그 칼럼의 자료형 또한 복합 자료형의 각 칼럼 자료형과 같아야 한다. 자료형이 다르면 다음과 같은 오류 메시지를 보여준다:
ERROR: return type mismatch in function declared to return emp
DETAIL: Final statement returns text instead of point at column 4.
기본 자료형을 사용할 때와 마찬가지로, 함수에서는 묵시적 형변화과 형변환 지정만 허용하지, 자동으로 명시적 형변환을 하지 않는다.
윗 예제와 똑같은 기능을 하는 함수를 다음과 같이 만들 수도 있다:
CREATE FUNCTION new_emp() RETURNS emp AS $$
SELECT ROW('None', 1000.0, 25, '(2,2)')::emp;
$$ LANGUAGE SQL;
SELECT 명령으로 즉석으로 반환할 자료형에 맞춰
한 번에 만든다. 하지만, 실무에서는 그리 좋은 방법은 아니지만,
로우 전체를 또 다시 다른 함수로 넘겨주는 형태로 사용할 때는
유용하게 쓰이기도 한다.
Another example is that if we are trying to write a function that
returns a domain over composite, rather than a plain composite type,
it is always necessary to write it as returning a single column,
since there is no way to cause a coercion of the whole row result.
이 함수는 아래와 같이 스칼라 함수를 호출 하듯이 사용할 수 있고:
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)
두 번째 사용법에 대해서는 37.5.7절에서 자세히 다룬다.
복합 자료형을 반환하는 함수를 사용할 때, 그 결과의 한 필드만 사용하려면, 다음과 같이 사용한다:
SELECT (new_emp()).name; name ------ None
조심해야 할 것은 해당 함수에 대한 결과에 대해서 그 안에 있는 속성을 뜻하기 때문에, 위와 같이 괄호가 필요하다. 만일 괄호가 빠지면, 다음과 같은 오류가 발생한다:
SELECT new_emp().name;
ERROR: syntax error at or near "."
LINE 1: SELECT new_emp().name;
^
또 다른 방법으로는 뽑으려는 속성을 마치 함수처럼 사용하는 방법도 있다:
SELECT name(new_emp()); name ------ None
8.16.5절에서 설명하고 있는 것처럼, 필드 지정방식이나, 함수 지정 방식은 같다.
복합 자료형을 반환하는 함수의 또 다른 사용법은 다음과 같이 그 반환값을 또다른 함수의 입력 인자로 사용하는 것이다:
CREATE FUNCTION getname(emp) RETURNS text AS $$
SELECT $1.name;
$$ LANGUAGE SQL;
SELECT getname(new_emp());
getname
---------
None
(1 row)
함수의 반환값을 출력 매개 변수로 지정할 수도 있다. 다음은 그 예제다:
CREATE FUNCTION add_em (IN x int, IN y int, OUT sum int)
AS 'SELECT x + y'
LANGUAGE SQL;
SELECT add_em(3,7);
add_em
--------
10
(1 row)
이 함수는 37.5.2절에서 소개한
add_em 함수와 똑 같은 함수다.
이렇게 사용하는 이유는 여러 칼럼을 한 번에 반환하려고 할 때
편하다. 예를 들어,
CREATE FUNCTION sum_n_product (x int, y int, OUT sum int, OUT product int) AS 'SELECT x + y, x * y' 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;
하지만 위와 같이 반환할 복합 자료형을 미리 만들어 그것을 반환하는 함수를 만드는 것이 좀 더 명확하다. 출력 매개 변수를 지정할 때 사용했던 그 변수 이름은 그 함수 반환값의 칼럼 이름으로 사용된다는 점도 기억해야 한다. 이 이름을 생략하면, 서버가 자동으로 부여한다.
또 하나 알고 있어야 할 점은 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, VARIADIC 네 종류다.
INOUT 매개 변수는 입출력이 같은 이름과 같은 자료형 임을
뜻하고, VARIADIC 매개 변수는 여러 인자를 하나의 인자로
입력하겠다는 것을 뜻한다. 이 부분에 대해서는 다음 절에서 자세히 소개한다.
SQL 함수는 인자의 개수가 가변적인 함수를
만들 수 있고, 인자 수가 가변적인 인자는 그 모든 인자가
같은 자료형이어야 하며, 모든 인자가 “선택적”일 수
있다. 이 인자는 통상 인자들 가운데, 맨 뒤에 오고,
배열형이어야 하고, 인자를 지정할 때, 앞에
VARIADIC 예약어를 사용한다. 사용 예:
CREATE FUNCTION mleast(VARIADIC arr numeric[]) RETURNS numeric AS $$
SELECT min($1[i]) FROM generate_subscripts($1, 1) g(i);
$$ LANGUAGE SQL;
SELECT mleast(10, -1, 5, 4.4);
mleast
--------
-1
(1 row)
위와 같이 VARIADIC 예약어로 지정한 인자 수가 가변적인
인자는 개별 인자처럼 입력하고, 내부적으로는 일차원 배열형으로 처리된다.
만일 아래와 같이 사용했다면, 오류를 낸다:
SELECT mleast(ARRAY[10, -1, 5, 4.4]); -- 작동 안함
위와 같이 사용하는 것은 하나의 인자인데, 그 인자가 숫자형 요소들로 구성된 배열형 인자임을 뜻한다. 이 경우 해당 자료형으로 하는 함수가 있는지 확인하고, 없으면 오류를 낸다. 위에서 만든 함수라면, 해당 인자의 자료형이 숫자형임으로 함수의 인자로는 함수 내용 안에서 사용할 배열의 요소를 각각 입력한다.
가끔 해당 인자가 이미 배열형으로 구성이 된 경우라면, 그것을 다시
개별 인자로 분리하지 않고 그냥 앞에 VARIADIC 예약어를
지정해서 이런 인자수가 가변적인 함수의 인자로 바로 수 있다.
Also,
this is the only secure way to call a variadic function found in a schema
that permits untrusted users to create objects; see
10.3절. You can do this by
specifying VARIADIC in the call:
SELECT mleast(VARIADIC ARRAY[10, -1, 5, 4.4]);
이렇게 하면, 이 예약어에 따라 배열의 요소를 각각 함수의 인자로 변환해서
넘겨주며, VARIADIC 인자는 함수의 맨 마지막 인자로 사용
되어야 한다.
VARIADIC 인자를 쓰는 경우 빈 배열을 넘기고 싶다면,
다음과 같이 빈 배열을 만들어 넘겨야 한다.
SELECT mleast(VARIADIC ARRAY[]::numeric[]);
단순히 SELECT mleast() 형태로 사용한다면,
인자가 없는 같은 이름의 또 다른 함수가 없다면, 오류를 낸다.
(만일 어떤 함수의 두 번째 인자가 이런 형태라고 하더라도,
두 번째 인자는 지정해 주어야 한다. - 인자 개수가 가변인 것이지
그 인자를 생략할 수는 없다.)
이런 처리 방식은 인자 이름 기반 호출 방식(4.3절 참조)에도 그대로 적용된다. 바른 사용법 예:
SELECT mleast(VARIADIC arr => ARRAY[10, -1, 5, 4.4]);
하지만, 아래 구문을 오류를 낸다:
SELECT mleast(arr => 10); SELECT mleast(arr => ARRAY[10, -1, 5, 4.4]);
함수에서 지정하는 인자들의 기본값을 지정할 수 있다. 기본값은 해당 함수를 호출 할 때, 그 함수에서 사용하는 인자를 모두 정확하게 입력하지 않았을 때, 그 기본값으로 함수 내용에서 사용한다. 함수 인자의 생략은 항상 마지막 인자만 생략할 수 있다. 즉 생략된 인자 앞의 인자 또한 기본값이 지정되어 있다면, 그 인자가 마지막 인자로 처리되어 또 생략할 수 있다. 하지만, 여러 인자 가운데, 맨 마지막이 아닌 다른 위치의 인자를 생략하고, 다음 인자는 사용자 그 값을 지정했다면 오류를 낸다. (물론 인자 이름 기반 지정이라면 상관 없다.) Whether or not you use it, this capability creates a need for precautions when calling functions in databases where some users mistrust other users; see 10.3절.
사용 예:
CREATE FUNCTION foo(a int, b int DEFAULT 2, c int DEFAULT 3)
RETURNS int
LANGUAGE SQL
AS $$
SELECT $1 + $2 + $3;
$$;
SELECT foo(10, 20, 30);
foo
-----
60
(1 row)
SELECT foo(10, 20);
foo
-----
33
(1 row)
SELECT foo(10);
foo
-----
15
(1 row)
SELECT foo(); -- fails since there is no default for the first argument
ERROR: function foo() does not exist
기본값을 지정하는 DEFAULT 예약어는
= 문자로 바꿔 쓸 수 있다.
모든 SQL 함수는 FROM절에서도 사용할 수 있지만,
특히 이 특성은 반환되는 자료형이 복합 자료형인 경우 유용하다.
함수가 기본 자료형을 반환 한다면, 하나의 칼럼을 가진 테이블을
조회 한 것과 같은 형식으로 처리되며, 복합 자료형을 반환한다면,
그 자료형 각각의 속성이 하나의 칼럼으로 구성된 테이블을 조회하는
것처럼 처리된다.
사용 예:
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)
예제처럼 함수 변환값은 마치 일반 테이블처럼 처리된다.
주의 해야 할 부분은 윗 예제처럼 함수를 만들 때, SETOF
예약어를 사용하지 않았다면, 하나의 로우만 반환한다.
이것에 대한 설명은 아래에서 자세히 다룬다.
SQL 함수를 만들 때, 그 반환값을 지정하는 부분에서,
SETOF 형태로
만들면, 함수 내용에 있는 쿼리 결과가 여러 로우인 경우
해당 되는 그 모든 로우를 반환한다.
어떤자료형
이런 함수는 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 TABLE tab (y int, z int);
INSERT INTO tab VALUES (1, 2), (3, 4), (5, 6), (7, 8);
CREATE FUNCTION sum_n_product_with_tab (x int, OUT sum int, OUT product int)
RETURNS SETOF record
AS $$
SELECT $1 + tab.y, $1 * tab.y FROM tab;
$$ LANGUAGE SQL;
SELECT * FROM sum_n_product_with_tab(10);
sum | product
-----+---------
11 | 10
13 | 30
15 | 50
17 | 70
(4 rows)
함수의 반환값이 단일 로우가 아닌, 다중 로우 형태가 되도록 하려면,
그 함수의 반환값을 정의할 때, RETURNS SETOF record
형태로 함수를 만든다는 것이 핵심이다.
윗 예제에서 단일 칼럼만 반환하려면, record 자료형 대신에,
그 칼럼의 자료형을 지정하면 된다.
이런 함수는 하나의 집합 반환 함수를 여러 번 호출해서
쿼리 결과를 만들어 내는데 흔하게 사용된다.
이 때, 여러 번 호출 때의 함수 인자가 또 다른 집합이 되어
하나의 쿼리로 여러 작업을 한 번에 할 수 있는 효과를 낸다.
7.2.1.5절에서 설명하고
있는 LATERAL 예약어를 사용하는 것이
권장하는 방법이다.
다음 예제는 집합 반환 함수를 사용해서 계층형 구조를 표현하는 것이다:
SELECT * FROM nodes;
name | parent
-----------+--------
Top |
Child1 | Top
Child2 | Top
Child3 | Top
SubChild1 | Child1
SubChild2 | Child1
(6 rows)
CREATE FUNCTION listchildren(text) RETURNS SETOF text AS $$
SELECT name FROM nodes WHERE parent = $1
$$ LANGUAGE SQL STABLE;
SELECT * FROM listchildren('Top');
listchildren
--------------
Child1
Child2
Child3
(3 rows)
SELECT name, child FROM nodes, LATERAL listchildren(name) AS child;
name | child
--------+-----------
Top | Child1
Top | Child2
Top | Child3
Child1 | SubChild1
Child1 | SubChild2
(5 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)
이 예제에서 주의 깊게 살펴 볼 부분은 Child2, Child3
로우가 보이지 않는 것이다. 왜냐하면, listchildren 함수의
인자로 이 값이 입력되었을 때 반환값이 없기 때문이다.
이는 LATERAL 예약어를 사용하는 구문에서도 마찬가지다.
PostgreSQL's behavior for a set-returning function in a
query's select list is almost exactly the same as if the set-returning
function had been written in a LATERAL FROM-clause item
instead. For example,
SELECT x, generate_series(1,5) AS g FROM tab;
is almost equivalent to
SELECT x, g FROM tab, LATERAL generate_series(1,5) AS g;
It would be exactly the same, except that in this specific example,
the planner could choose to put g on the outside of the
nested-loop join, since g has no actual lateral dependency
on tab. That would result in a different output row
order. Set-returning functions in the select list are always evaluated
as though they are on the inside of a nested-loop join with the rest of
the FROM clause, so that the function(s) are run to
completion before the next row from the FROM clause is
considered.
If there is more than one set-returning function in the query's select
list, the behavior is similar to what you get from putting the functions
into a single LATERAL ROWS FROM( ... ) FROM-clause
item. For each row from the underlying query, there is an output row
using the first result from each function, then an output row using the
second result, and so on. If some of the set-returning functions
produce fewer outputs than others, null values are substituted for the
missing data, so that the total number of rows emitted for one
underlying row is the same as for the set-returning function that
produced the most outputs. Thus the set-returning functions
run “in lockstep” until they are all exhausted, and then
execution continues with the next underlying row.
Set-returning functions can be nested in a select list, although that is
not allowed in FROM-clause items. In such cases, each level
of nesting is treated separately, as though it were
a separate LATERAL ROWS FROM( ... ) item. For example, in
SELECT srf1(srf2(x), srf3(y)), srf4(srf5(z)) FROM tab;
the set-returning functions srf2, srf3,
and srf5 would be run in lockstep for each row
of tab, and then srf1 and srf4
would be applied in lockstep to each row produced by the lower
functions.
Set-returning functions cannot be used within conditional-evaluation
constructs, such as CASE or COALESCE. For
example, consider
SELECT x, CASE WHEN x > 0 THEN generate_series(1, 5) ELSE 0 END FROM tab;
It might seem that this should produce five repetitions of input rows
that have x > 0, and a single repetition of those that do
not; but actually, because generate_series(1, 5) would be
run in an implicit LATERAL FROM item before
the CASE expression is ever evaluated, it would produce five
repetitions of every input row. To reduce confusion, such cases produce
a parse-time error instead.
함수 내용 가운데, 마지막 명령이 RETURNING 구문이 있는
INSERT, UPDATE, DELETE 명령인 경우,
그 구문에 오류가 없을 경우, 무조건 해당 자료 변경 작업은 진행을 완료
한다. 그 함수가 SETOF 예약어를 이용한 자료 반환이 아니어도,
또 반환 로우가 모두 출력되지 않았다 하더라도, 변경 작업은 완료 된다.
SETOF 없는 함수인 경우는
RETURNING 작업으로 출력해야 할 부가적인 로우는
내부적으로 무시되고, 마지막 로우만 리턴하겠지만, 테이블 변경 작업은
모두 수행한다. (그리고, 그 함수가 반환 작업을 하기 전에 그 작업은
완료된다.)
Before PostgreSQL 10, putting more than one
set-returning function in the same select list did not behave very
sensibly unless they always produced equal numbers of rows. Otherwise,
what you got was a number of output rows equal to the least common
multiple of the numbers of rows produced by the set-returning
functions. Also, nested set-returning functions did not work as
described above; instead, a set-returning function could have at most
one set-returning argument, and each nest of set-returning functions
was run independently. Also, conditional execution (set-returning
functions inside CASE etc) was previously allowed,
complicating things even more.
Use of the LATERAL syntax is recommended when writing
queries that need to work in older PostgreSQL versions,
because that will give consistent results across different versions.
If you have a query that is relying on conditional execution of a
set-returning function, you may be able to fix it by moving the
conditional test into a custom set-returning function. For example,
SELECT x, CASE WHEN y > 0 THEN generate_series(1, z) ELSE 5 END FROM tab;
could become
CREATE FUNCTION case_generate_series(cond bool, start int, fin int, els int)
RETURNS SETOF int AS $$
BEGIN
IF cond THEN
RETURN QUERY SELECT generate_series(start, fin);
ELSE
RETURN QUERY SELECT els;
END IF;
END$$ LANGUAGE plpgsql;
SELECT x, case_generate_series(y > 0, 1, z, 5) FROM tab;
This formulation will work the same in all versions of PostgreSQL.
테이블 반환하기
함수가 집합을 반환하도록 작성하는 방법 가운데 또 다른 방법은
RETURNS TABLE( 구문을 사용하는 것이다.
이 방법은 하나 이상의 칼럼들)OUT 인자를 지정하고,
SETOF record 구문을 쓰는 방법과 똑같은 결과를 반환한다.
이 구문은 최근 SQL 표준을 준수하기 위해서 만들어졌다. 그래서
기존 방식으로 작성된 구문을 이 구문으로 바꾸는 것이
쿼리 호환성을 높이는 방법이 될 것이다.
다음 예제는 더하기와 곱하기에 대한 것이다:
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;
이 RETURNS TABLE 구문을 사용하면,
OUT, INOUT 인자는 사용할 수 없다 — 즉,
출력에 관련된 모든 칼럼 정의는 TABLE 다음에 오는
칼럼 목록에서 정의해야 한다.
SQL 함수는
37.2.5절에서 소개하고
있는 다형 자료형을 반환할 수 있다.
다음은 두 개의 인자를 입력 받아, 그것을 각 요소로
하는 배열을 반환하는 make_array 함수를
만들고 사용한 예다:
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 polymorphic type because input has type unknown
With make_array declared as above, you must
provide two arguments that are of exactly the same data type; the
system will not attempt to resolve any type differences. Thus for
example this does not work:
SELECT make_array(1, 2.5) AS numericarray; ERROR: function make_array(integer, numeric) does not exist
An alternative approach is to use the “common” family of polymorphic types, which allows the system to try to identify a suitable common type:
CREATE FUNCTION make_array2(anycompatible, anycompatible)
RETURNS anycompatiblearray AS $$
SELECT ARRAY[$1, $2];
$$ LANGUAGE SQL;
SELECT make_array2(1, 2.5) AS numericarray;
numericarray
--------------
{1,2.5}
(1 row)
Because the rules for common type resolution default to choosing
type text when all inputs are of unknown types, this
also works:
SELECT make_array2('a', 'b') AS textarray;
textarray
-----------
{a,b}
(1 row)
다형 인자에 대한 반환 자료형이 고정된 형태는 지원하지만, 그 반대인 경우는 지원하지 않는다. 사용 예:
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 result of type anyelement requires at least one input of type anyelement, anyarray, anynonarray, anyenum, or anyrange.
이 다형 기법은 출력 매개 변수에서도 사용될 수 있다. 사용 예:
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)
이 다형 기법은 인자 개수가 가변적인 경우도 사용될 수 있다: 사용 예:
CREATE FUNCTION anyleast (VARIADIC anyarray) RETURNS anyelement AS $$
SELECT min($1[i]) FROM generate_subscripts($1, 1) g(i);
$$ LANGUAGE SQL;
SELECT anyleast(10, -1, 5, 4);
anyleast
----------
-1
(1 row)
SELECT anyleast('abc'::text, 'def');
anyleast
----------
abc
(1 row)
CREATE FUNCTION concat_values(text, VARIADIC anyarray) RETURNS text AS $$
SELECT array_to_string($2, $1);
$$ LANGUAGE SQL;
SELECT concat_values('|', 1, 4, 2);
concat_values
---------------
1|4|2
(1 row)
SQL 함수에서 하나 이상의 자국어 정렬이 가능한 매개 변수를 사용할 경우,
자국어 기반 문자열 정렬은 각 개별 인자에서 지정한 자국어 정렬 방법에
따른다 (23.2절 참조).
자국어 정렬이 바르게 지정되면,
(예를 들어, 각각의 정렬 기준이 서로 충돌하지 않는다면,)
모든 자국어 정렬이 가능한 매개 변수 값들은 그 정렬 기준이 암묵적으로
적용된다. 이 기능은 함수 내에 자국어 정렬과 관계된 작업이 있어 날 때
모두 적용된다. 앞에서 만들었던
anyleast 함수를 가지고 이 부분에 대한 예제를 살펴보면:
SELECT anyleast('abc'::text, 'ABC');
윗 결과는 데이터베이스 기본 lc_collate 값에 따라 다르다. C
로케일인 경우는 ABC를 보여주지만, 대부분의 다른
로케일에서는 abc를 보여준다. 인자에 대한 자국어 정렬
기준을 강제로 지정하려면, COLLATE 절을 다음과 같이
추가 한다:
SELECT anyleast('abc'::text, 'ABC' COLLATE "C");
다른 방법으로, 이 자국어 정렬 방식을 함수 내용 안에,
COLLATE 절을 포함 해서 입력 인자 쪽에서
개별적으로 지정하는 번거로움을 피할 수도 있다.
아래 예제는 anyleast 함수에서 입력 받은 자료를
함수 내부에서 강제로 en_US 문자 정렬로 사용하는 경우다:
CREATE FUNCTION anyleast (VARIADIC anyarray) RETURNS anyelement AS $$
SELECT min($1[i] COLLATE "en_US") FROM generate_subscripts($1, 1) g(i);
$$ LANGUAGE SQL;
하지만, 윗 함수에 자국어 정렬 불가능한 자료형이 입력되면, 오류를 낸다는 것에 주의해야 한다.
실재 인자에 일반적이지 않은 자국어 정렬 방식을 지정했고, 함수의 실 작업에서 사용할 정렬 방식을 결정 할 수 없다면, 데이터베이스 기본 정렬 방식을 사용한다. (이 값은 데이터베이스 lc_collate 값을 말한다.)
이 함수 인자에 대한 자국어 정렬 처리 기능은 문자열 자료, 그 문자열 을 다루는 배열에 대해서만 적용된다.