PostgreSQL에서는 복잡한 문제를 해결하기 위해 사용자 정의 함수를 생성할 수 있습니다.

기본 PL/pgSQL 스크립트 언어를 사용하여 작성하거나 다른 스크립트 언어로 작성할 수 있습니다.

Python, Perl, Tcl 및 R은 지원되는 스크립팅 언어 중 일부입니다.

PL/pgSQL은 Postgres 설치와 함께 제공되지만 다른 언어를 사용하려면 몇 가지 설정이 필요합니다.

확장 설치

확장 기능을 사용하기 전에 확장 패키지를 설치해야 합니다.

우분투에서는 다음을 실행합니다:

펄

sudo apt-get -y install postgresql-plperl-14

패키지 이름 'postgresql-plperl-14'는 PostgreSQL 버전 14에만 해당됩니다. 다른 버전의 PostgreSQL을 사용하는 경우 설치된 PostgreSQL 버전과 일치하도록 패키지 이름의 버전 번호를 변경해야 합니다.

파이썬 3

sudo apt-get install postgresql-plpython3-14

확장 프로그램 활성화

PostgreSQL에서 확장을 활성화하려면 CREATE EXTENSION 문을 사용하여 확장을 정의해야 합니다.

펄

CREATE EXTENSION plperl;

파이썬

CREATE EXTENSION plpython3;

안녕하세요 세계의 예

확장 기능이 생성되면 해당 확장 기능을 사용하여 사용자 정의 기능을 생성할 수 있습니다.

펄

CREATE OR REPLACE FUNCTION hello(name text) 
RETURNS text AS $$
    my ($name) = @_;
    return "Hello, $name!";
$$ LANGUAGE plperl;

파이썬

CREATE OR REPLACE FUNCTION hello(name text)
RETURNS text AS $$
    return "Hello, "   name   "!"
$$ LANGUAGE plpython3;

한 줄씩 나누기

CREATE OR REPLACE FUNCTION hello(name text)

이 줄은 Postgres에서 함수가 생성되는 방법입니다. CREATE OR REPLACE를 사용하면 이미 hello라는 이름으로 정의된 모든 함수를 새 함수로 덮어씁니다.

CREATE FUNCTION hello(name text)를 사용하면 함수가 기존 함수를 덮어쓰는 것을 방지하고 함수가 이미 존재하는 경우 오류가 발생합니다.

RETURNS text AS $$

이것은 반환될 Postgres 데이터 유형을 정의합니다. 지정된 데이터 유형이 Postgres에서 인식되는 유형이라는 것이 중요합니다. 사용자 정의 유형이 이미 정의된 경우 사용자 정의 데이터 유형을 지정할 수 있습니다.

$$는 코드 블록의 시작과 끝을 표시하는 구분 기호입니다. 이 줄에서는 코드 블록의 시작을 표시합니다.

시작과 끝 $$ 사이의 모든 코드는 Postgres에 의해 실행됩니다.

$$ LANGUAGE plperl;

$$는 스크립트의 끝을 나타내며 Postgres에 스크립트를 구문 분석해야 하는 언어를 알려줍니다.

기능 사용

함수는 내장된 Postgres 함수처럼 사용할 수 있습니다.

SELECT hello('world');

이렇게 하면 Hello world!

함수는 더 복잡한 쿼리의 일부일 수 있습니다.

SELECT id, title, hello('world') greeting FROM table;

더 복잡한 예

다음은 필드에서 텍스트를 받아들이고 단어 수를 반환하는 예제 함수입니다.

CREATE OR REPLACE FUNCTION word_count(paragraph text)
RETURNS json AS $$
use strict;
use warnings;

my ($text) = @_;

my @words = $text =~ /\w /g;
my $word_count = scalar @words;

my $result = '{' .
    '"word_count":' . $word_count .
'}';
return $result;
$$ LANGUAGE plperl;

단어 수가 포함된 JSON 형식의 결과를 반환합니다.

함수에 더 자세한 통계를 추가할 수 있습니다.

CREATE OR REPLACE FUNCTION word_count(paragraph text)
RETURNS json AS $$
use strict;
use warnings;

my ($text) = @_;

my @words = $text =~ /\w /g;

my $word_count = scalar @words;

my $sentence_count = ( $text =~ tr/!?./!?./ ) || 0;

my $average_words_per_sentence =
  $sentence_count > 0 ? $word_count / $sentence_count : 0;

my $result = '{' .
    '"word_count":' . $word_count . ',' .
    '"sentence_count":' . $sentence_count . ',' .
    '"average_words_per_sentence":"' . sprintf("%.2f", $average_words_per_sentence) . '"' .
'}';

return $result;
$$ LANGUAGE plperl SECURITY DEFINER;

이제 쿼리에 사용할 때

SELECT word_count(text_field) word_count FROM table

다음과 같이 JSON을 반환합니다.

{"word_count":116,"sentence_count":15,"average_words_per_sentence":"7.73"}

보안 고려사항

사용자 정의 기능이나 외부 스크립트 언어를 사용할 때 고려해야 할 추가 보안 고려 사항이 있습니다. 유용성과 보안 사이의 적절한 균형을 맞추는 것은 저글링 행위일 수 있습니다.

보안 정의자 및 보안 호출자

이전 함수에서는 create function 문에 SECURITY DEFINER 옵션이 추가되었습니다.

보안 관점에서 기능이 어떻게 실행되기를 원하는지 생각하는 것이 중요합니다.

기본 동작은 SECURITY INVOKER를 사용하는 것입니다. 해당 기능을 실행하고 있는 사용자의 권한으로 해당 기능을 실행하게 됩니다.

SECURITY DEFINER는 함수에 부여된 권한에 대해 더 많은 제어를 제공합니다. 이 모드를 사용하면 해당 기능을 생성한 사용자의 권한으로 해당 기능이 실행됩니다.

이것은 좋을 수도 있고 나쁠 수도 있습니다. 제한된 권한을 가진 사용자가 함수를 생성하면 데이터베이스에 해를 끼칠 수 있는 것이 거의 없습니다.

높은 액세스 권한을 가진 사용자가 함수를 생성한 경우 해당 함수는 동일한 권한으로 실행됩니다. 기능 유형에 따라 사용자는 부여된 것보다 더 많은 공개 권한으로 기능을 실행할 수 있습니다.

이것이 유용한 경우가 있습니다. 예를 들어 사용자에게 테이블에 대한 읽기 권한이 없지만 함수 내에서 읽기가 필요한 경우 SECURITY DEFINER를 사용하면 함수 실행에 필요한 읽기 권한을 허용할 수 있습니다.

신뢰할 수 있는 확장과 신뢰할 수 없는 확장

위의 확장 기능을 만들 때 plperl과 plpython3이 사용되었습니다. 대부분의 상황에서 이는 사용하기에 적합한 확장입니다.

이러한 확장은 서버 파일 시스템 및 시스템 호출에 대한 액세스가 제한되어 있습니다.

확장 프로그램은 u (plpython3u, plperlu)

신뢰할 수 없는 확장 프로그램이며 서버 파일 시스템에 더 많은 액세스를 허용합니다.

Perl 모듈, Python 라이브러리를 사용하거나 시스템 호출을 사용하려는 경우와 같이 이것이 필요한 경우가 있을 수 있습니다.

위의 예에서 JSON 출력은 문자열로 생성되었습니다. 원하는 경우 Perl JSON 모듈을 사용하여 데이터를 JSON으로 인코딩할 수 있었습니다. 이렇게 하려면 신뢰할 수 없는 확장 프로그램을 사용하여 JSON 모듈에 액세스해야 합니다.

신뢰할 수 없는 확장 프로그램은 사용하지 않는 것이 좋지만, 필요한 경우 주의해서 사용하고 잠재적인 위험을 이해하세요.

Perl을 사용하는 경우 신뢰할 수 없는 확장 프로그램을 사용하면 Perl이 오염 모드에서 실행됩니다.

마지막 생각들

Perls의 고급 텍스트 처리 및 메모리 관리 또는 PostgreSQL 내 Python의 데이터 분석 라이브러리를 활용할 수 있다는 것은 정말 강력한 도구가 될 수 있습니다.

복잡한 작업을 작업 처리에 더 적합한 도구에 전달하면 데이터베이스의 오버헤드를 줄일 수 있습니다.

항상 그랬듯이, 맞춤 기능과 외부 스크립트 언어를 사용할 때는 안전한 사용을 위해 예방 조치를 취하세요.