postgresql의 function을 설명하기에 앞서 먼저 저장 프로시저를 설명하겠습니다. 타 데이터베이스에서 부르는 저장 프로시저는 PostgreSQL의 function (이하,. 함수)와 같은 개념이기 때문입니다.

저장 프로시저란?

저장 프로시저란 SQL 로 만든 함수입니다. 이 함수에 여러 SQL문을 작성해 사용합니다.

사용 이유

단위 하나로, 작업을 만들어 저장 할 수 있기 때문입니다. 모든 이유는 오로지 이 이유 때문입니다. 이 이유 때문에 얻게 되는 건 언어에서 함수를 사용 할 때 얻게 되는 이점과 같습니다. 예를 들어 작업이 단위로 구분되어 있어 디버깅이 쉬워지고 관리 또한 편해지고 반복작업을 없앨 수 있습니다.

여기에 SQL 만이 얻는 장점이 하나 추가 할 수 있는데, 바로 보안입니다. 특정 유저에게 UPDATE 나 DELETE 를 막고, 함수만 사용 할 수 있게 막음으로써, 기존 데이터 회손을 최대한 막을 수 있습니다.

PostgreSQL의 Function

위에서 말했던 것과 같이 타 데이터베이스에서 부르는 저장 프로시저가 postgresql에서는 function이라 칭합니다. PL/pgSQL은 기본적인 SQL만으로 프로시저를 만들지 않고 PL/pgSQL같은 절차지향식 언어를 지원합니다. (Oracle에서는 PL/SQL, SQL Server에서는 TSQL, Postgresql에서는 PL/pgSQL 로 부릅니다. 추가적으로 PL/pgSQL은 PL/SQL과 유사합니다.) 생성 문법은 Oracle과 같이 CREATE OR REPLACE 를 지원하며 변수선언도 유사하고, SQL Server와 같이 function body에 DDL(Date Define Language)을 사용해도 Compile이 Invalid 되지 않습니다.

장점

  • 응용 프로그램과 데이터베이스 서버 간의 왕복 횟수를 줄입니다. 모든 SQL 문은 PostgreSQL 데이터베이스 서버에 저장된 함수로 래핑되므로 응용 프로그램은 여러 SQL 문을 보내는 대신 결과를 얻기 위해 함수 호출을 실행하고 각 호출 사이에 결과를 기다려야합니다. 
  • 사용자 정의 함수가 PostgreSQL 데이터베이스 서버에 사전 컴파일되고 저장되기 때문에 응용 프로그램 성능을 향상시킵니다. 
  • 많은 응용 프로그램에서 재사용 할 수 있어야합니다. 함수를 개발하면 모든 응용 프로그램에서 재사용 할 수 있습니다.

단점

  • 소프트웨어 개발이 느려지므로 많은 개발자가 소유하지 않은 특수 기술이 필요합니다.
  • 버전 관리가 어렵고 디버그하기가 어렵습니다. 
  • MySQL이나 Microsoft SQL Server와 같은 다른 데이터베이스 관리 시스템으로 이식 할 수 없습니다.

기본 설정파일 그대로 대개 사용하지만 기본설정에서는 몇 가지 문제되는 부분이 있다고 합니다. 검색으로 찾아서 할 수도 있지만 아래 사이트에 필수 값만 입력하면 max connection, 버터 등이 나옵니다. 

(간편, 유용)

PgTune: http://pgtune.leopard.in.ua/


여기서는 WITH ... AS 문을 사용하여 SELECT 결과가 없을 시 INSERT하는 쿼리, 로우가 1개 이상일 경우 UPDATE를 하고 그렇지 않으면 INSERT를 하는 쿼리 등을 설명합니다. 그리고  INSERT INTO .... ON CONFLICT .. DO UPDATE SET .. 문을 사용하여 보다 간편하게 로우가 없으면 INSERT, 존재하면 UPDATE를 하는 기능을 설명합니다.

WITH ... AS

여기서는 WITH ... AS 문에 대해 설명하기 보다 이를 사용해 다른 데이터베이스에 존재하는 upsert기능이나 select 결과가 없으면 insert를 하는 쿼리 등을 만들어 설명하겠습니다.

upsert란 로우의 갯수가 1개 이상일 시, 사용자가 명시한 쿼리대로 레코드의 값을 update하고 로우가 하나도 존재하지 않으면 insert를 하게하는 기능입니다. 

WITH ... AS 는 PostgreSQL은 9.1 이상부터 해당 기능을 지원합니다.

WITH 사용자명 AS (update [테이블명] SET [업데이트할 컬럼] = [업데이트 값] RETURNING *) INSERT INTO [테이블명] (컬럼1, 컬럼2,...) SELECT [삽입할 값] WHERE NOT EXISTS (SELECT * FROM 앞에서 지정한 사용자명)

-- 1. update할 데이터가 존재하지 않으면 insert, 존재하면 update하는 예시
with upsert as (update test1 set id =40 returning *) insert into test1 (id) select 1 WHERE NOT EXISTS (SELECT * FROM upsert)
-- test1테이블의 로우 갯수가 0일 경우, id 컬럼에 1을 insert. 로우 갯수가 0이 아니면 id 컬럼의 값을 40으로 변경
 
-- 응용
with upsert as (update test1 set id =40 returning *) insert into test1 (id) select (SELECT PK FROM test where pk =1) as a WHERE NOT EXISTS (SELECT * FROM upsert) -- select 절에 subquery 사용가능 
 
-- 2. select 결과가 존재하지 않으면 insert 하는 예시
with selected as (select id from test1) insert into test1 (id) select 10 where not exists (select * from selected)
-- test1 의 select 결과가 존재하지 않으면 test1 테이블의 id 컬럼에 10인 로우 insert
 
-- 응용
with selected as (select id from test1), inserted as (insert into test1 (id) select 10 where not exists (select * from selected) returning id)
select id
from selected
union all
select id
from inserted 
-- test1의 select 결과가 존재하지 않으면 insert를 한 후 id를 반환. union all을 하는 부분을 생략하면 insert가 동작할 시, id에 insert한 값이 제대로 return되는 반면 select 결과가 존재해 insert가 되지 않으면 id 값이 return 되지 않음. 따라서 select와 insert 두 서브쿼리를 담고있는 selected 와 inserted를 union하여 id값 return


이와 같이 with ... as를 사용하여 사용자가 필요한 것들을 만들어 낼 수 있습니다.

INSERT INTO .... ON CONFLICT .. DO UPDATE SET ..

위의 upset과는 약간 다르게 로우의 갯수로 update할 지, insert할 지 판단하는 것이 아닌 사용자가 명시한 컬럼으로 중복체크를 한 다음 update 또는 insert를 결정합니다. PostgreSQL에는 9.5버전부터 이 기능을 지원합니다. (9.5 아래버전에는 지원을 하지않는걸로..)

INSERT INTO 테이블 VALUES(값,값1,...,값n) ON CONFLICT (중복체크할 컬럼) DO UPDATE SET 업데이트할 컬럼 = 업데이트할 값
 
-- 예시
insert into test (pk, id ,name) values(1,1,1) on conflict (pk) DO update set id = 10

위 예시를 설명하면 test라는 테이블의 pk, id, name 컬럼에 1이란 값을 각각 insert하려 합니다. 이때 pk값이 중복되면 id컬럼의 값을 10으로 업데이트합니다.


위와 같은 옵션들은 코드를 작성할 때 유용하게 사용할 수 있습니다. 



SELECT datname FROM pg_database -- 전체 데이터베이스 조회
SELECT datname FROM pg_database WHERE datistemplate = false -- 사용자가 생성한 데이터베이스만 조회
select nspname from pg_catalog.pg_namespace -- 현재 db의 전체 스키마 조회
select tablename from pg_tables -- 전체 테이블 조회



char_length  # 문자열의 문자 수
bit_length   # 문자열의 비트 수
octet_length # 문자열의 바이트 수
 
# 예시
 
select char_length('abcd')  # 4
select bit_length('1234')   # 32
select octet_length('1234') # 4


만약 text 타입의 a라는 컬럼을 smallint로 타입을 변경한다고 할 때 아래와 같은 에러가 납니다.

alter table test alter column a type smallint;
 
-- 에러 발생
You might need to specify "USING a::smallint


텍스트 또는 varchar에서 정수로의 자동 변환을 지원하지 않습니다. (즉, 정수로 예상하는 함수에 varchar를 전달하거나 varchar 필드를 정수로 할당 할 수 없습니다.) 따라서 ALTER TABLE ...을 사용하여 형변환에 대해 명시적으로 지정해야합니다.

alter table test alter column a type smallint using a::smallint;


+ Random Posts