plperl

PL/Perl procedural language

https://www.postgresql.org/docs/current/plperl.html

Overview

Package	Version	Category	License	Language
`plperl`	`1.0`	LANG	PostgreSQL	C

ID	Extension	Bin	Lib	Load	Create	Trust	Reloc	Schema
3260	`plperl`	No	Yes	No	Yes	Yes	No	`pg_catalog`
3261	`bool_plperl`	No	Yes	No	Yes	Yes	No	-
3262	`hstore_plperl`	No	Yes	No	Yes	No	No	-
3263	`jsonb_plperl`	No	No	No	Yes	Yes	No	-

Related	`plperlu` `plpython3u` `pllua` `pltcl` `plsh` `pljava` `plr` `plxslt` `plpgsql` `plv8`
Depended By	`bool_plperl` `hstore_plperl` `jsonb_plperl` `sparql`

Version

PG18	PG17	PG16	PG15	PG14
1.0	1.0	1.0	1.0	1.0

Install

Note: This is a built-in contrib extension of PostgreSQL

CREATE EXTENSION plperl;

Usage

plperl: PL/Perl trusted procedural language

PL/Perl allows writing PostgreSQL functions in Perl. As a trusted language, it runs in a restricted environment without access to the filesystem or external modules.

CREATE EXTENSION plperl;

-- Simple scalar function
CREATE FUNCTION perl_hello(text) RETURNS text
LANGUAGE plperl AS $$
  my ($name) = @_;
  return "Hello, $name!";
$$;

SELECT perl_hello('world');

-- Using Perl regex for text processing
CREATE FUNCTION clean_whitespace(text) RETURNS text
LANGUAGE plperl AS $$
  my ($str) = @_;
  $str =~ s/^\s+|\s+$//g;   # trim
  $str =~ s/\s+/ /g;         # collapse internal whitespace
  return $str;
$$;

-- Returning a composite type
CREATE TYPE name_parts AS (first_name text, last_name text);

CREATE FUNCTION split_name(text) RETURNS name_parts
LANGUAGE plperl AS $$
  my ($full) = @_;
  my ($first, $last) = split(/\s+/, $full, 2);
  return { first_name => $first, last_name => $last };
$$;

-- Set-returning function
CREATE FUNCTION perl_generate_series(integer, integer) RETURNS SETOF integer
LANGUAGE plperl AS $$
  my ($start, $stop) = @_;
  for my $i ($start .. $stop) {
    return_next($i);
  }
  return undef;
$$;

-- Trigger function
CREATE FUNCTION perl_audit_trigger() RETURNS trigger
LANGUAGE plperl AS $$
  $_TD->{new}{modified_at} = localtime();
  return "MODIFY";
$$;

Database access uses spi_exec_query:

CREATE FUNCTION perl_row_count(text) RETURNS integer
LANGUAGE plperl AS $$
  my ($table) = @_;
  my $rv = spi_exec_query("SELECT count(*) AS cnt FROM $table");
  return $rv->{rows}[0]{cnt};
$$;

plperl

Overview

Version

Install

Usage

Was this page helpful?