PostgreSQL 8.3.3문서
Prev	Fast Backward	Chapter 39. PL/Perl - Perl 수속 언어	Fast Forward	Next

39.2. PL/Perl로부터의 데이터베이스 접근

Perl 함수로부터의 데이터베이스 접근을, 후술의 spi_exec_query 혹은 DBD::PgSPI ( CPAN mirror sites 로부터 입수 가능)라는 실험 단계의 모듈 경유로 실시할 수가 있습니다. 이 모듈에 의해, DBI호환의 $pg_dbh라는 데이터베이스 핸들을 사용할 수가 있습니다. 이 핸들을 사용해, 통상의 DBI구문을 사용한 쿼리를 실시할 수가 있습니다.

현재, PL/Perl는 이하의 보조적인 Perl 명령를 제공합니다.

spi_exec_query( query [, max-rows ]) spi_query( command ) spi_fetchrow( cursor ) spi_prepare( command , argument types ) spi_exec_prepared( plan ) spi_query_prepared( plan [, attributes ], arguments ) spi_cursor_close( cursor ) spi_freeplan( plan )

spi_exec_query는 SQL 명령을 실행해, 행 세트 전체를 해시에의 참조를 요소로 하는 배열에의 참조로서 돌려줍니다. 결과가 상대적으로 소규모인 것을 알고 있는 경우에게만 이 명령를 사용해 주세요. 이하에 최대행 옵션을 가진 쿼리(SELECT명령)의 예를 나타냅니다.

$rv = spi_exec_query('SELECT * FROM my_table', 5);

이것은 my_table테이블에서 5행까지를 돌려줍니다. my_table에 my_column열이 있는 경우, 결과의 $i행의 렬값를 이하와 같이 꺼낼 수가 있습니다.

$foo = $rv->{rows}[$i]->{my_column};

SELECT쿼리로부터 돌려주어지는 행의 총수는 이하와 같이 접근 할 수 있습니다.

$nrows = $rv->{processed}

이하는 다른 종류의 명령를 사용하는 예입니다.

$query = "INSERT INTO my_table VALUES (1, 'test')";
$rv = spi_exec_query($query);

이 후, 이하와 같이 명령 상태(예를 들면 SPI_OK_INSERT)에 접근 할 수가 있습니다.

$res = $rv->{status};

영향을 받은 행수를 꺼내려면 이하를 실시합니다.

$nrows = $rv->{processed};

이하에 완성된 예를 나타냅니다.

CREATE TABLE test (
    i int,
    v varchar
);

INSERT INTO test (i, v) VALUES (1, 'first line');
INSERT INTO test (i, v) VALUES (2, 'second line');
INSERT INTO test (i, v) VALUES (3, 'third line');
INSERT INTO test (i, v) VALUES (4, 'immortal');

CREATE OR REPLACE FUNCTION test_munge() RETURNS SETOF test AS $$
    my $rv = spi_exec_query('select i, v from test;');
    my $status = $rv->{status};
    my $nrows = $rv->{processed};
    foreach my $rn (0 .. $nrows - 1) {
        my $row = $rv->{rows}[$rn];
        $row->{i} += 200 if defined($row->{i});
        $row->{v} =~ tr/A-Za-z/a-zA-Z/ if (defined($row->{v}));
        return_next($row);
    }
    return undef;
$$ LANGUAGE plperl;

SELECT * FROM test_munge();

spi_query 및 spi_fetchrow는, 대규모로 될 가능성이 있는 행 세트용, 또는, 행을 차례 대로에 돌려주고 싶은 경우 전용으로 조합해 동작합니다. spi_fetchrow는 spi_query와 함께가 아니면 동작하지 않습니다. 조합해 사용하는 방법에 대해, 이하의 예로 가리킵니다.

CREATE TYPE foo_type AS (the_num INTEGER, the_text TEXT);

CREATE OR REPLACE FUNCTION lotsa_md5 (INTEGER) RETURNS SETOF foo_type AS $$
    use Digest::MD5 qw(md5_hex);
    my $file = '/usr/share/dict/words';
    my $t = localtime;
    elog(NOTICE, "opening file $file at $t" );
    open my $fh, '<', $file # ooh, it's a file access!
        or elog(ERROR, "can't open $file for reading: $!");
    my @words = <$fh>;
    close $fh;
    $t = localtime;
    elog(NOTICE, "closed file $file at $t");
    chomp(@words);
    my $row;
    my $sth = spi_query("SELECT * FROM generate_series(1,$_[0]) AS b(a)");
    while (defined ($row = spi_fetchrow($sth))) {
        return_next({
            the_num => $row->{a},
            the_text => md5_hex($words[rand @words])
        });
    }
    return;
$$ LANGUAGE plperlu;

SELECT * from lotsa_md5(500);

spi_prepare, spi_query_prepared, spi_exec_prepared, spi_freeplan는, 준비가 끝나 쿼리용으로 같은 기능을 구현합니다. spi_prepare를 호출해 쿼리 계획의 준비가 끝나면 spi_exec_query에 의해 반환된 것과 같은 결과가 되는 spi_exec_prepared이나 spi_query와 완전히 같은 커서가 반환된 spi_query_prepared를 사용해, 그 계획을 문자열 쿼리 대신에 사용할 수가 있습니다.

준비가 끝난 쿼리의 이점은, 1개의 준비된 계획을 여러 차례 사용해 쿼리를 실행할 수가 있다고 하는 점입니다. 계획이 불필요하게 된 후, spi_freeplan로 개방할 수 있습니다.

CREATE OR REPLACE FUNCTION init() RETURNS INTEGER AS $$
        $_SHARED{my_plan} = spi_prepare( 'SELECT (now() + $1)::date AS now', 'INTERVAL');
$$ LANGUAGE plperl;

CREATE OR REPLACE FUNCTION add_time( INTERVAL ) RETURNS TEXT AS $$
        return spi_exec_prepared( 
                $_SHARED{my_plan},
                $_[0],
        )->{rows}->[0]->{now};
$$ LANGUAGE plperl;

CREATE OR REPLACE FUNCTION done() RETURNS INTEGER AS $$
        spi_freeplan( $_SHARED{my_plan});
        undef $_SHARED{my_plan};
$$ LANGUAGE plperl;

SELECT init();
SELECT add_time('1 day'), add_time('2 days'), add_time('3 days');
SELECT done();

  add_time  |  add_time  |  add_time  
------------+------------+------------
 2005-12-10 | 2005-12-11 | 2005-12-12

spi_prepare내의 매개변수 첨자가 $1,$2,$3등을 개입시켜 정의되는 것에 주의해 주세요. 그 때문에, 잡기 힘든 버그가 쉽게 발생되는 이중 인용부호내에서의 쿼리 선언은 그만두어 주세요.

통상 spi_fetchrow는, 읽어내는 행이 이제 존재하지 않는 것을 의미하는 undef가 돌려주어질 때까지, 반복될 것입니다.spi_fetchrow가 undef 를 돌려줄 때, 커서는 자동적으로 개방됩니다. 돌아오는 모든 행을 읽어낼 필요가 없으면, spi_cursor_close를 호출해 커서를 개방해 주세요. 이것에 실패하면 메모리 리크가 발생합니다.

elog( level , msg )

로그 또는 에러 메세지를 발행합니다. 사용할 수 있는 레벨은, DEBUG, LOG, INFO, NOTICE, WARNING, 및 ERROR입니다. ERROR는 에러 상태를 발생합니다. 게다가 위의 Perl 코드로 이 에러를 포착하지 않는 경우, 에러는 쿼리의 호출원까지 전파 해, 그 결과, 현재의 트랜잭션 혹은 서브트랜잭션을 중단합니다. 이것은 실질 Perl의die명령와 같습니다. 다른 레벨은, 다른 중요도의 메세지를 생성할 뿐입니다. log_min_messages과 client_min_messages설정 매개변수는, 특정의 중요도의 메세지를 클라이언트에 보고하거나, 서버의 로그에 써내거나, 혹은 그 양쪽 모두를 제어합니다. 자세한 것은 Chapter 17을 참조해 주세요.

Prev	Home	Next
PL/Perl 함수와 인자	Up	Data Values in PL/Perl