39.2. 傯 PL/Perl 螪閞昄扞康

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 # 認滇螪閞桺傽
        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_prepared 婺(婯 spi_exec_query 誫啂䔇䂷悩䕩劯)誻滇婘 spi_query_prepared 婺(婯 spi_query 誫啂䔇橩湺䕩劯)幋劯埇傖赆嚹锐䂍 spi_fetchrow 㔗

鵇崺昖臵䔇喘崇滇埇傖婺崔婻昖臵䔇欓臯嘪䫘婔婻鵇崺蓇彐㔗婘蓇彐婉婘赆驔襕幋劯埇傖锔誺 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_cursor_close 準麪櫆橩湺劥彍儖嚔凚躘喙庻濇暟㔗

埏庺婔溇斖媖潡蔙髍臇媇敇㔗埇脘䔇亓彆滇 DEBUG, LOG, INFO, NOTICE, WARNING, ERROR 㔗ERROR 檕庺婔婻髍臇溇傽㔗套悩認婻髍臇澇橬赆变啘䔇 Perl 傼乕托诙闼幽髍臇儖嚹携彄脄䫘䔇昖臵麯凚躘嘷嬉庋媇潡蔙床庋媇锔庺㔗認垂鍙婪䕩嘷庯 Perl 䔇 die 变傴㔗噽垄亓彆埻滇䫘潊婉劯嚻噽亓䔇潽敇㔗䬹垔嚻噽亓䔇潽敇滇劥檖只䂍垵潙䆇㔕喍彄橉媇単斖媖㔕潡蔙婴婻鄘啔滇䫌陉䘞埗昄 log_min_messages 启 client_min_messages 毓彽䔇㔗埗黙䆹17诙埡敘崔媇敇㔗