39.1. PL/Perl 庘昄启埗昄

襕䫘 PL/Perl 臺蘔录傺婔婻庘昄埇傖嘪䫘湺庖䔇 CREATE FUNCTION 臺濘

CREATE FUNCTION funcname (argument-types) RETURNS return-type AS $$
    # PL/Perl function body
$$ LANGUAGE plperl;

庘昄嘷滇捞锔 Perl 傼乕㔗垂鍙婪PL/Perl 脽愘傼乕儖噽儕輙婘婔婻 Perl 床誺䘋麯㔗婔婻 PL/Perl 庘昄媙釂攂滇誫啂湺麟昄唚㔗嘹埇傖償婋麵柟誄䔇闼湙䫘誫啂嚘䫘䔇桹濘誫啂敘崉溗䔇䂷悇嘖䂺凹婉襕誫啂婔婻彖臘㔗

CREATE FUNCTION 变傴䔇臺濘襕挗檪庘昄嘷喍潊庖严婾婩麟㔗锔婩崇䊖庖严婾桺橸䫘䆯噄严䘯垔敘桹冪(埗黙誗4.1.2.2)套悩嘹愿嘪䫘嚹䂘䔇 E'' 锄锩臺濘媙釂埯喍庘昄嘷麯嘪䫘䔇剘嚘埙(')启埉桩溹(\)(埗蓕誗4.1.2.1)㔗

埗昄启䂷悩鄘滇启傂嘘噽垄 Perl 床誺䘋麯闼湙崇䊖䔇埗昄滇櫆婘 @_ 麯嚹锐䔇䂷悩唚滇䫘 return 誫啂潡蔙嘩婺庘昄婺橔劯螇䞖䔇臘膆嚟䔇唚誫啂㔗

懫套婔婻誫啂婴婻昘昄婺膄崓唚䔇庘昄埇傖認幽喍

CREATE FUNCTION perl_max (integer, integer) RETURNS integer AS $$
    if ($_[0] > $_[1]) { return $_[0]; }
    return $_[1];
$$ LANGUAGE plperl;

套悩䂍庘昄嚹锐婔婻 NULL 闼幽噽埗昄唚儖傖 Perl 婺"undefined"䔇嘵嚟庺䯄㔗婪麵䔇庘昄垔幬婘膷噖婺 NULL 施䔇臯婺婉滇冽溼婩(垂鍙婪垄儖臘䯄冖喘償垄傸鄘滇镽婔湙)㔗埇傖䂍庘昄垔幬嵂媹 STRICT 螷 PostgreSQL 啔婔底敘劽䊖䔇庋愙套悩嚹锐誕準婔婻 NULL 闼幽臖庘昄彍湹橸婉嚔赆脄䫘蔯埻滇躻媘誫啂婔婻 NULL 䂷悩㔗埥崡埇傖婘庘昄嘷麯演昖橻垔幬䔇膷噖㔗懫套啺螆愿櫽彄婔婻 NULL 启婔婻麂 NULL 埗昄䔇 perl_max 誫啂麂 NULL 䔇埗昄蔯婉滇 NULL 

CREATE FUNCTION perl_max (integer, integer) RETURNS integer AS $$
    my ($x,$y) = @_;
    if (! defined $x) {
        if (! defined $y) { return undef; }
        return $y;
    }
    if (! defined $y) { return $x; }
    if ($x > $y) { return $x; }
    return $y;
$$ LANGUAGE plperl;

套婪欔誄襕傯 PL/Perl 庘昄婺誫啂婔婻 NULL 埇傖誫啂婔婻橻垔幬䔇昄唚㔗婉䞇臖庘昄滇劥婖湚鄘埇傖認幽啔㔗

Perl 埇傖䫘 Perl 昄䂇嚘䫘䔇桹嚟誫啂 PostgreSQL 昄䂇㔗婋麵滇婔婻冋床

CREATE OR REPLACE function returns_array()
RETURNS text[][] AS $$
    return [['a"b','c,d'],['e\\f','g']];
$$ LANGUAGE plperl;

select returns_array();

崉劽䌂傋䔇埗昄滇嘷啔毺劏昼彖䔇嚘䫘嚹锐䂍庘昄䔇㔗昼彖䔇髞庖滇崉劽䌂傋䔇匂攓劉㔗婋麵滇婔婻冋床

CREATE TABLE employee (
    name text,
    basesalary integer,
    bonus integer
);

CREATE FUNCTION empcomp(employee) RETURNS integer AS $$
    my ($emp) = @_;
    return $emp->{basesalary} + $emp->{bonus};
$$ LANGUAGE plperl;

SELECT name, empcomp(employee.*) FROM employee;

嘪䫘劯湙䔇媂濘婔婻 PL/Perl 庘昄埇傖誫啂婔婻崉劽䌂傋䔇䂷悩誫啂婔婻寙劆欔驔襕䔇匂攓䔇昼彖䔇嚘䫘㔗懫套

CREATE TYPE testrowperl AS (f1 integer, f2 text, f3 text);

CREATE OR REPLACE FUNCTION perl_row() RETURNS testrowperl AS $$
    return {f2 => 'hello', f1 => 1, f3 => 'world'};
$$ LANGUAGE plperl;

SELECT * FROM perl_row();

婘弄滯䔇䂷悩昄扞䌂傋麯䔇傂嘘庖枕套悩婘昼彖麯麵澇橬庺䯄闼幽鄘嚔嘷嘩 NULL 誫啂㔗

PL/Perl 庘昄幘脘誫啂湺麟潡蔙崉劽䌂傋䔇镖劽㔗锔婩嘹婯橕婔渇誫啂婔臯婔桹麵媹锘庘昄劇媘施閘埥崡婔桹麵黾溵婘喙庻麯幖䓇昘婻䂷悩镖㔗埇傖䫘婋麵臘滯䔇庘昄 return_next 㔗臙濘懟婘橔劯䔇 return_next 嘹媙釂櫆婔婻 return 潡蔙(橔喘滇) return undef 㔗

CREATE OR REPLACE FUNCTION perl_set_int(int)
RETURNS SETOF INTEGER AS $$
    foreach (0..$_[0]) {
        return_next($_);
    }
    return undef;
$$ LANGUAGE plperl;

SELECT * FROM perl_set_int(5);

CREATE OR REPLACE FUNCTION perl_set()
RETURNS SETOF testrowperl AS $$
    return_next({ f1 => 1, f2 => 'Hello', f3 => 'World' });
    return_next({ f1 => 2, f2 => 'Hello', f3 => 'PostgreSQL' });
    return_next({ f1 => 3, f2 => 'Hello', f3 => 'PL/Perl' });
    return undef;
$$ LANGUAGE plperl;

凹庯償䔇䂷悩镖嘹埇傖誫啂婔婻毺劏婔婻昄䂇䔇嚘䫘認婻昄䂇埇傖寙劆湺麟毺劏昄䂇䔇嚘䫘潡蔙毺劏䞔剘䌂傋昄䂇䌂傋傖埪崉劽䌂傋京䔇昼彖䔇嚘䫘㔗認麯滇婔婻䞔剘䔇冋床垄檪昘婻䂷悩镖嘷嘩婔婻昄䂇嚘䫘誫啂

CREATE OR REPLACE FUNCTION perl_set_int(int) RETURNS SETOF INTEGER AS $$
    return [0..$_[0]];
$$ LANGUAGE plperl;

SELECT * FROM perl_set_int(5);

CREATE OR REPLACE FUNCTION perl_set() RETURNS SETOF testrowperl AS $$
    return [
        { f1 => 1, f2 => 'Hello', f3 => 'World' },
        { f1 => 2, f2 => 'Hello', f3 => 'PostgreSQL' },
        { f1 => 3, f2 => 'Hello', f3 => 'PL/Perl' }
    ];
$$ LANGUAGE plperl;

SELECT * FROM perl_set();

套悩嘹愿婘躻噌䔇傼乕麯嘪䫘 strict 䫘濘橔䞔剘䔇桹濘滇 SET plperl.use_strict 婺䩘㔗認婻埗昄嘌巉锟劯䔇 PL/Perl 庘昄䔇䚡臏嘖滇婉嘌巉婘嘷嬉嚔臺麯噾䂟䚡臏庖䔇庘昄㔗襕愿婘 PL/Perl 媹蘘幋嬉螆䘞認婻埗昄媙釂檪"plperl"時媹彄 postgresql.conf 䔇 custom_variable_classes 彖臘麯㔗

埥崡婔婻嘪䫘 strict 䫘濘䔇桹濘滇檪

use strict;

櫆婘庘昄嘷麯㔗嘖滇認婻埻脘婘 PL/PerlU 庘昄麯橬䫘啹婺 use 婉滇埇媇傂䔇淉嘩㔗婘 PL/Perl 麯嘹埇傖認幽䫘

BEGIN { strict->import(); }