It's also possible to run Perl scripts within the database (using the extension 'plperl' or 'plperlu'. This is quite useful for performance optimization, writing complex triggers and such. But it's also quite nice for debugging and administration, if you can run certain scripts just by selecting from them. A basic data generator could look like this:
-- Create the extension
CREATE EXTENSION plperlu;
-- define how the table row returned by the perl function looks like
CREATE TYPE poemrow AS (
poemrowid bigint,
verserowid bigint,
verseline text
);
-- Automatic poem generator
CREATE OR REPLACE FUNCTION bottlesofbeer(bigint) RETURNS SETOF poemrow
+ AS $$
my ($bottlecount) = @_;
if($bottlecount < 0) {
$bottlecount = 0;
};
my $rowid = 0;
my @verselines = (
'COUNT bottles of beer on the wall, COUNT bottles of beer.',
'Take one down, pass it around!',
'REDUCED bottles of beer on the wall',
''
);
my $vcount;
while($bottlecount) {
my $nextcount = $bottlecount - 1;
$vcount = 1;
foreach my $rawline (@verselines) {
my $line = '' . $rawline; # COPY the line
$line =~ s/COUNT/$bottlecount/g;
$line =~ s/REDUCED/$nextcount/g;
return_next({poemrowid => $rowid, verserowid => $vcount, v
+erseline => $line });
$vcount++;
$rowid++;
}
$bottlecount--;
}
return_next({poemrowid => $rowid, verserowid => $vcount, verseline
+ => 'No more beer, buy some more!' });
return undef; # We are done here
$$ LANGUAGE plperlu;
Now you can use the function like a read-only table:
ER_DEV_DB=# SELECT * FROM bottlesofbeer(3);
poemrowid | verserowid | verseline
+
-----------+------------+---------------------------------------------
+------
0 | 1 | 3 bottles of beer on the wall, 3 bottles of
+beer.
1 | 2 | Take one down, pass it around!
2 | 3 | 2 bottles of beer on the wall
3 | 4 |
4 | 1 | 2 bottles of beer on the wall, 2 bottles of
+beer.
5 | 2 | Take one down, pass it around!
6 | 3 | 1 bottles of beer on the wall
7 | 4 |
8 | 1 | 1 bottles of beer on the wall, 1 bottles of
+beer.
9 | 2 | Take one down, pass it around!
10 | 3 | 0 bottles of beer on the wall
11 | 4 |
12 | 5 | No more beer, buy some more!
(13 rows)
ER_DEV_DB=# SELECT verseline FROM bottlesofbeer(8) WHERE verserowid =
+3 ORDER BY poemrowid DESC;
verseline
-------------------------------
0 bottles of beer on the wall
1 bottles of beer on the wall
2 bottles of beer on the wall
3 bottles of beer on the wall
4 bottles of beer on the wall
5 bottles of beer on the wall
6 bottles of beer on the wall
7 bottles of beer on the wall
(8 rows)
Of course, plperl can also run SQL statements, access results and more, including to be used as a INSERT/UPDATE/DELETE trigger function.
A simple example would be to make sure every customer entry has a customer number, avoiding the pitfalls of database sequences (which may leave big holes for cancelled transactions etc), something like this:
-- Create a perl trigger function in database schema "pos"
CREATE OR REPLACE FUNCTION pos.customersinserttrigger() RETURNS trigge
+r AS $$
use strict;
if(defined($_TD->{new}->{'customer_number'}) && $_TD->{new}->{'cus
+tomer_number'} ne '') {
return;
}
my $selsth = spi_prepare("SELECT max(customer_number) AS max_custo
+mer_number FROM pos.customers WHERE customer_number IS NOT NULL");
my $nextnum = 1_000_000;
my @lines;
{
my $retval = spi_exec_prepared($selsth);
if($retval->{status} ne 'SPI_OK_SELECT') {
elog(ERROR, "Failed to select all rows in pos.customers");
}
if($retval->{processed}) {
@lines = @{$retval->{rows}};
}
spi_freeplan $selsth;
}
foreach my $line (@lines) {
my $tmp = $line->{max_customer_number};
$tmp = 0 + $tmp;
if($tmp > $nextnum) {
$nextnum = $tmp;
}
}
$nextnum++;
$_TD->{new}->{'customer_number'} = $nextnum;
return "MODIFY"; # EXECUTE, all updates sucessfull
$$ LANGUAGE plperlu;
-- Add a trigger to table "customers" in schema "pos" to call our trig
+ger function on every inserted or updated row
CREATE TRIGGER insertupdate_customers_trigger
BEFORE INSERT OR UPDATE ON pos.customers
FOR EACH ROW EXECUTE FUNCTION pos.customersinserttrigger();
|