A Brief Introduction to PLPerl

PostgreSQL has been growing as the must-have database for the past few years, and one of its killer features is its procedural language system, which gives you (yes you) the ability to write stored database procedures in Perl.

This talk briefly introduces how to install PL/Perl (delightfully easy!), how to write your first function (takes seconds!) and the sort of cool, flexible, and crazy -- if you're into that -- things you can do with this powerful tool.

Initially prepared for the May 2012 meeting of the Baltimore Perl Mongers (http://baltimore.pm), May 17th 2012
Presented at the 2013 DC-Baltimore Perl Workshop (http://dcbpw.org/dcbpw2013), April 20th 2013

Transcript

1. PL/Perl You got your Perl in my database! (You got your database in my Perl)
2. Whooooo Are You? (㽈doot doot doot doot㽈) • Stephen Belcher/@sycobuny • Web application programmer for National Institute on Aging in Baltimore • Loves PostgreSQL almost enough to write fan fiction about it • Likes Perl too, I guess
3. What’s PL/Perl? • PostgreSQL server-side Procedural Language • Write functions in the database - in Perl! • Available in two flavors: Trusted/Untrusted • For all you who think of security first • (That’s everyone, right?!)
4. Why’s that awesome? • Uh, cause Perl is awesome? • Build comprehensive text constraints with Regular Expressions • Handle complex data...complex-ly...in the middle of a query • Avoid unnecessary data passing between server and client
5. Baby Steps Installation
6. Installation Notes • Must have administrative privileges over the database (usually access to user pgsql or postgres) if not pre-installed • Once installed, any database must CREATE the language: • CREATE LANGUAGE plperl; -- <= pg9.0 • CREATE EXTENSION plperl; -- >= pg9.1
7. Compiling From Source • Must be done at compile time for the entire server • Just a simple flag during configuration! • $ ./configure --with-perl $ make $ sudo make install
8. Various Package Managers • FreeBSD ports: databases/postgresqlXX- plperl • MacPorts: postgresqlXX +perl (also for Mac: http://postgresapp.com/) • RedHat/yum: postgresqlXX-plperl • Ubuntu/apt: postgresql-plperl-X.X • Others?
9. Hello, PL/Perl Basic Usage
10. Creating a Function CREATE FUNCTION hello(TEXT) RETURNS TEXT LANGUAGE PLPERL AS $BODY$ use warnings; # good habits... use strict; # die hard. my ($who) = @_; # recognize this? return “Hello, $who”; $BODY$;
11. Using a Function sycobuny=$ SELECT hello(‘PL/Perl’); hello ---------------- Hello, PL/Perl (1 row)
12. Caveats That’s cool, but...
13. PL/Perl is RESTRICTED • You can use warnings, you can use strict, but you can’t use Data::Dumper. • I/O? Right out. • This includes require and most use statements. • For these and more, use PL/PerlU.
14. PL/PerlU is DANGEROUS • Full access to OS • Full access to filesystem • Full access to PostgreSQL server process • DO $$ `rm -rf /` $$ LANGUAGE PLPERLU; • At least PG doesn’t run as root?
15. Datatypes Incoming and Outgoing
16. Scalars/Arrays/Hashes • All PostgreSQL atomic datatypes (INTEGER, CHAR, DATE, etc.) come in and go out as scalars. • PostgreSQL ARRAY datatypes come in as PostgreSQL::InServer::ARRAY, a basic arrayref wrapper object. They can be returned as this or a plain arrayref. • Composite datatypes (rows, etc.) come in and go out as hashrefs.
17. CREATE FUNCTION scalars(INTEGER, DATE, INET) RETURNS TEXT LANGUAGE PLPERLU AS $BODY$ use Data::Dumper; return Dumper(\@_); $BODY$; Incoming Scalars
18. Incoming Scalars sycobuny=$ SELECT scalars(42, ‘May 17, 2012’, ‘127.0.0.1’); scalars ------------------------- $VAR1 = [ + ‘42’, + ‘2012-05-17’,+ ‘127.0.0.1’ + ]; + (1 row)
19. Incoming Arrays CREATE FUNCTION arrays(TEXT[]) RETURNS TEXT LANGUAGE PLPERLU AS $BODY$ my ($array) = @_; use Data::Dumper; $Data::Dumper::Indent = 1; return join(‘, ’, @$array) . “\n” . Dumper($array); $BODY$;
20. Incoming Arrays sycobuny=$ SELECT arrays(ARRAY[‘Hello’, ‘Arrays’]); arrays ------------------------------------- Hello, Arrays + $VAR1 = bless( { + ‘array’ => [ + ‘Hello’, + ‘Arrays’ + ], + ‘typeoid’ => 1009 + }, ‘PostgreSQL::InServer::ARRAY’ );+ (1 row)
21. Incoming Composite Types CREATE TABLE cmp (i INTEGER, t TEXT); CREATE FUNCTION composites(cmp) RETURNS TEXT LANGUAGE PLPERLU AS $BODY$ my ($hash) = @_; use Data::Dumper; return Dumper($hash); $BODY$;
22. sycobuny=$ INSERT INTO cmp (i, t) VALUES sycobuny-$ (42, ‘Hello, Composite’); INSERT 0 1 sycobuny=$ SELECT composites(cmp) FROM cmp; composites ------------------------------ $VAR1 = { + ‘t’ => ‘Hello, Composite’,+ ‘i’ => ‘42’ + }; + (1 row) Incoming Composite Types
23. Querying Database Intra-action
24. Cursors, or No? • Non-Cursor Functions: • dataset = spi_exec_query(query[, maxrows]) • dataset = spi_exec_prepared(plan[, attributes], arguments) • Cursor Functions: • cursor = spi_query(query) • cursor = spi_query_prepared(plan, arguments) • row = spi_fetchrow(cursor) • spi_cursor_close(cursor)
25. Prepared, or No? • Non-Preparing functions: • dataset = spi_exec_query(query[, max_rows]) • cursor = spi_query(query) • Preparing functions: • plan = spi_prepare(query, argument_types) • dataset = spi_exec_prepared(plan, arguments) • cursor = spi_query_prepared(plan[, attributes], arguments) • spi_freeplan(plan)
26. Preparing • Parameters are similar to DBI • They use ‘$1’, ‘$2’, etc., not question marks • Care must be used to single-quote queries using parameters, or the sigils must be escaped • You can re-use the values ‘$1’ or ‘$2’ multiple times without redeclaring: • spi_prepare(‘SELECT $1, $1, $2’, ‘INT’, ‘INT’);
27. Full Contrived Example DO LANGUAGE PLPERL $PERL$ my ($i, $t) = (42, ‘Hello, SPI’); my ($q) = <<INS; INSERT INTO cmp (i, t) VALUES (\$1, \$2) RETURNING * INS my ($plan) = spi_prepare($q, ‘INTEGER’, ‘TEXT’); my ($ds) = spi_exec_prepared($plan, $i, $t); spi_freeplan($plan); my ($m) = ‘Just inserted i as ’ . $ds->{rows}[0]{i} . ‘ and t as ’ . $ds->{rows}[0]{t}; elog(NOTICE, $m); $PERL$
28. Even More Functions • elog(level, msg) • quote_literal(string) • quote_nullable(string) • quote_ident(string) • decode_bytea(string) • encode_bytea(string) • encode_array_literal(array [, delimiter]) • encode_typed_literal(value, typename) • encode_array_constructor(array) • looks_like_number(string) • is_array_ref(any_value) • Also a number of constants for logging/etc.
29. Custom Datatypes Constrained by nothing, constraining everything
30. Basic Email Function CREATE OR REPLACE FUNCTION emailish(TEXT) RETURNS BOOLEAN LANGUAGE PLPERL AS $BODY$ $_[0] =~ qr/^ [a-z0-9_\.\-\+]+\@ # username (?:[a-z0-9_\-\.])+\. # hostname [a-z]{2,4} # tld $/ix ? ‘t’ : ‘f’; $BODY$;
31. Basic Email Type CREATE DOMAIN email AS TEXT CONSTRAINT looks_like_email CHECK ( VALUE IS NULL OR emailish(VALUE) );
32. And Your Little Tables Too CREATE TABLE users ( id SERIAL PRIMARY KEY, login TEXT UNIQUE NOT NULL, first_name TEXT NOT NULL, last_name TEXT NOT NULL, -- ...and now introducing, thanks to PL/Perl... email_address EMAIL UNIQUE NOT NULL );
33. But Wait, There’s More! Sadly, it does not make Julienne Fries (yet)
34. Triggers CREATE FUNCTION trig_ins_cmp() RETURNS TRIGGER LANGUAGE PLPERL AS $BODY$ my ($new) = $_TD->{new}; my ($i, $t) = ($new->{i}, $new->{t}); my ($m) = “Just inserted ($i, $t) into cmp!”; elog(NOTICE, $m); $BODY$; CREATE TRIGGER aftins_cmp AFTER INSERT ON cmp FOR EACH ROW EXECUTE PROCEDURE trig_ins_cmp();
35. Complex Filters, Easily SELECT * FROM legacy_data.users INNER JOIN legacy_data.user_details ON users.user_id = user_details.user_id WHERE emailish(user_email);
36. Even More • Send emails from the database • Return processed templates from the database • Pull information from external sources (HTTP, FTP, etc.) to the database • Pull info from HTTP, format it in a template, and send it in an email! From the database! • [insert insane cackling]
37. That’s all, folks! @sycobuny sbelcher@gmail.com