PostgreSQL stands out from other databases with its extensive and extendable support for different languages to write database-stored functions. It allows you to call out to libraries native to that language. We will list the key language as well as some esoteric ones. The languages with an * are preinstalled with PostgreSQL and can be enabled. Some require further installs in addition to the language handler.
You can create set returning functions, simple scalar functions, triggers, and aggregate functions with most of these languages. This allows for languages that are highly optimized for a particular task to work directly with data without having to always copy it out to process as you normally would need with a simple database storage device. Language handlers can be of two flavors: trusted and untrusted. An untrusted language can access the filesystem directly.
From PostgreSQL 9.1+, languages not enabled by default in database or not built-in are installed using :
CREATE EXTENSION …;
CREATE EXTENSION 'plpythonu';
CREATE OR REPLACE somename(arg1 arg1type)
RETURNS result_argtype AS
$$
body goes here
$$
LANGUAGE ‘somelang’;
Language |
Description |
Req |
SQL* (trusted) |
Enabled in all databases. Allows you to write simple functions and set returning functions in just SQL. The function internals are visible to the planner, so in many cases it performs better than other functions since the planner can strategize how to navigate based on the bigger query. It is simple and fast, but limited in functionality. CREATE OR REPLACE FUNCTION prod_state(prev numeric, e1 numeric, e2 numeric).
RETURNS numeric AS
$$
SELECT COALESCE($1,0) + COALESCE($2*$3,0);
$$
LANGUAGE 'sql' IMMUTABLE;
|
none |
c* |
Built in and always enabled. Often used to extend PostgreSQL (e.g. postgis, pgsphere, tablefunc) or, for example, to introduce new windowing functions (introduced in PostgreSQL 8.4). Functions are referenced from a .so or .dll file. CREATE OR REPLACE FUNCTION st_summary(geometry)
RETURNS text AS
'$libdir/postgis-2.1’, 'LWGEOM_summary'
LANGUAGE 'c' IMMUTABLE STRICT;
|
none |
plpgsql* (trusted) |
Not always enabled, but packaged so it can be installed. CREATE FUNCTION cp_upd(p_key integer, p_value varchar)
RETURNS void AS
$$
BEGIN
IF EXISTS(SELECT test_id FROM testtable WHERE test_id = p_key) THEN
UPDATE testtable
SET test_stuff = p_value
WHERE test_id = p_key;
ELSE
INSERT INTO testtable (test_id,
test_stuff)
VALUES(p_key, p_value);
END IF;
RETURN;
END;
$$
LANGUAGE 'plpgsql' VOLATILE;
|
none |
plv8 (trusted) |
Good for manipulating JSON objects, reusing existing Javascript libraries, numeric processing. Comes packaged with 3 language bindings: Plv8 (aka PL/Javascript), plls (LiveScript), plcoffee (CoffeeScript). To install: CREATE EXTENSION plv8;
CREATE EXTENSION plls;
CREATE EXTENSION plcoffee;
|
Google v8 engine |
Example Functions
This next table demonstrates some examples of writing functions in various languages. For all functions you write, you can use the CREATE or REPLACE FUNCTION construction to overwrite existing functions that take same arguments. We use CREATE FUNCTION here.
.
Language |
Example |
plperl (trusted), plperlu (untrusted) |
CREATE FUNCTION use_quote(TEXT) RETURNS text AS $$
my $text_to_quote = shift;
my $qfunc = $_SHARED{myquote};
return &$qfunc($text_to_quote);
$$ LANGUAGE plperl;
|
plpythonu, plpython2u, plpython3u (untrusted) |
CREATE FUNCTION fnfileexists(IN fname text) RETURNS boolean AS
$$
import os
return os.path.exists(fname)
$$
LANGUAGE 'plpythonu' STRICT;
|
plr |
Good for doing advanced stats and plotting using the R statistical language. CREATE FUNCTION r_quantile(float8[])
RETURNS float8[] AS
$$
quantile(arg1, probs = seq(0, 1, 0.25),
names = FALSE)
$$ LANGUAGE 'plr' IMMUTABLE STRICT;
|
plv8 |
Allows you to write functions in JavaScript. CREATE FUNCTION
fib(n int) RETURNS int AS $$
function fib(n) {
return n<2 ? n : fib(n-1) + fib(n-2)
}
return fib(n)
$$ LANGUAGE plv8 IMMUTABLE STRICT;
|
Common Procedural Tasks
Create a table trigger and use in table
CREATE OR REPLACE FUNCTION mytable_ft_trigger()
RETURNS trigger AS $$
BEGIN
NEW.tsv :=
setweight(to_tsvector('pg_catalog.english',
coalesce(new.field1,'')), 'A') ||
setweight(to_tsvector('pg_catalog.english',
coalesce(NEW.field2,'')), 'B');
return NEW;
END
$$ LANGUAGE plpgsql;
CREATE TRIGGER mytable_trigiu
BEFORE INSERT OR UPDATE OF field1,field2
ON mytable
FOR EACH ROW EXECUTE PROCEDURE mytable_ft_trigger()
Return sets and use of out params
CREATE OR REPLACE FUNCTION
fn_sqltestmulti(param_subject varchar,
OUT test_id integer,
OUT test_stuff text)
RETURNS SETOF record
AS
$$
SELECT test_id, test_stuff
FROM testtable
WHERE test_stuff LIKE $1;
$$
LANGUAGE 'sql' STABLE;
--example
SELECT * FROM fn_sqltestmulti('%stuff%');
Return sets and use of table construct
CREATE OR REPLACE FUNCTION
fn_sqltestmulti(param_subject varchar)
RETURNS TABLE(test_id integer, test_stuff text)
AS
$$
SELECT test_id, test_stuff
FROM testtable
WHERE test_stuff LIKE $1;
$$
LANGUAGE 'sql' STABLE;
Extensions
Extensions extend the capabilities of PostgreSQL by providing additional data types, functions, index types, and more. After installing an extension, you need to run the following command to enable it:
CREATE EXTENSION extension_name;
Notable Extensions
Extension |
Description |
Link |
PostGIS |
Adds support for geographic objects allowing location queries to be run using SQL. |
http://postgis.net/ |
pg_shard |
Shards and replicates tables for horizontal scaling and high availability. |
https://github.com/citusdata/pg_shard |
pg_stat_statements |
Tracks execution statistics of all SQL statements. |
http://www.postgresql.org/docs/current/static/pgstatstatements.html |
cstore_fdw |
Columnar store for PostgreSQL. |
https://github.com/citusdata/cstore_fdw |
postgresql-hll |
Distinct value counting with tunable precision. |
https://github.com/aggregateknowledge/postgresql-hll |
pgcrypto |
Cryptographic functions. |
http://www.postgresql.org/docs/current/static/pgcrypto.html |
dblink |
Connections to other PostgreSQL databases from a database session. |
http://www.postgresql.org/docs/current/static/dblink.html |
For a full list of extensions shipped with PostgreSQL see: http://www.postgresql.org/docs/current/static/contrib.html To search for third party extensions see: http://pgxn.org/
{{ parent.title || parent.header.title}}
{{ parent.tldr }}
{{ parent.linkDescription }}
{{ parent.urlSource.name }}