2ndQuadrant is now part of EDB

Bringing together some of the world's top PostgreSQL experts.

2ndQuadrant | PostgreSQL
Mission Critical Databases
  • Contact us
  • EN
    • FR
    • IT
    • ES
    • DE
    • PT
  • Support & Services
  • Products
  • Downloads
    • Installers
      • Postgres Installer
      • 2UDA – Unified Data Analytics
    • Whitepapers
      • Business Case for PostgreSQL Support
      • Security Best Practices for PostgreSQL
    • Case Studies
      • Performance Tuning
        • BenchPrep
        • tastyworks
      • Distributed Clusters
        • ClickUp
        • European Space Agency (ESA)
        • Telefónica del Sur
        • Animal Logic
      • Database Administration
        • Agilis Systems
      • Professional Training
        • Met Office
        • London & Partners
      • Database Upgrades
        • Alfred Wegener Institute (AWI)
      • Database Migration
        • International Game Technology (IGT)
        • Healthcare Software Solutions (HSS)
        • Navionics
  • Postgres Learning Center
    • Webinars
      • Upcoming Webinars
      • Webinar Library
    • Whitepapers
      • Business Case for PostgreSQL Support
      • Security Best Practices for PostgreSQL
    • Blog
    • Training
      • Course Catalogue
    • Case Studies
      • Performance Tuning
        • BenchPrep
        • tastyworks
      • Distributed Clusters
        • ClickUp
        • European Space Agency (ESA)
        • Telefónica del Sur
        • Animal Logic
      • Database Administration
        • Agilis Systems
      • Professional Training
        • Met Office
        • London & Partners
      • Database Upgrades
        • Alfred Wegener Institute (AWI)
      • Database Migration
        • International Game Technology (IGT)
        • Healthcare Software Solutions (HSS)
        • Navionics
    • Books
      • PostgreSQL 11 Administration Cookbook
      • PostgreSQL 10 Administration Cookbook
      • PostgreSQL High Availability Cookbook – 2nd Edition
      • PostgreSQL 9 Administration Cookbook – 3rd Edition
      • PostgreSQL Server Programming Cookbook – 2nd Edition
      • PostgreSQL 9 Cookbook – Chinese Edition
    • Videos
    • Events
    • PostgreSQL
      • PostgreSQL – History
      • Who uses PostgreSQL?
      • PostgreSQL FAQ
      • PostgreSQL vs MySQL
      • The Business Case for PostgreSQL
      • Security Information
      • Documentation
  • About Us
    • About 2ndQuadrant
    • 2ndQuadrant’s Passion for PostgreSQL
    • News
    • Careers
    • Team Profile
  • Blog
  • Menu Menu
You are here: Home1 / Blog2 / Mark's PlanetPostgreSQL3 / Creating a PostgreSQL procedural language – Part 1 – Setup
Creating a PostgreSQL procedural language - Part 1 - Setup
Mark Wong

Creating a PostgreSQL procedural language – Part 1 – Setup

February 5, 2020/2 Comments/in Mark's PlanetPostgreSQL /by Mark Wong

PostgreSQL supports many procedural languages, which can be used to write user defined functions or stored procedures.  There are four that are readily available as part of the standard PostgreSQL distribution: PL/pgSQL, PL/Tcl, PL/Perl, PL/Python.  Yet procedural languages don’t have to be created as part of the core project.  There are a number more that are available as a result of PostgreSQL being so highly extensible.

The following is only the beginning of an example to create a new procedural language for PostgreSQL. At some point later we will have something that enables user defined functions and stored procedures written in the Julia programming language to actually execute.

The PostgreSQL documentation has a very light example for what is required to handle a new procedural language.  It is recommended to create this new feature as an extension when creating a procedural language outside of the core project.  All that is needed to get started the extension started are:

  • a control file that defines the basic properties of the extension
  • a SQL file that creates the extension’s objects
  • a C file for the extension itself
  • a Makefile to build the extension

More detailed information on these individual files are in the PostgreSQL documentation.  But here are brief examples of each file that gets us an extension that can be built and loaded into PostgreSQL.

pljulia.control:

comment = 'PL/Julia procedural language'
default_version = '0.1'
module_pathname = '$libdir/pljulia'
relocatable = false
schema = pg_catalog
superuser = false

pljulia–0.1.sql:

CREATE FUNCTION pljulia_call_handler()
RETURNS language_handler
AS 'MODULE_PATHNAME'
LANGUAGE C;

CREATE LANGUAGE pljulia
HANDLER pljulia_call_handler;

COMMENT ON LANGUAGE pljulia IS 'PL/Julia procedural language';

pljulia.c:

#include <postgres.h>
#include <fmgr.h>

PG_MODULE_MAGIC;

PG_FUNCTION_INFO_V1(pljulia_call_handler);

/*
 * Handle function, procedure, and trigger calls.
 */

Datum
pljulia_call_handler(PG_FUNCTION_ARGS)
{
    return 0;
}

Makefile:

PGFILEDESC = "PL/Julia - procedural language"

EXTENSION = pljulia
EXTVERSION = 0.1

MODULE_big = pljulia

OBJS = pljulia.o

DATA = pljulia.control pljulia--0.1.sql

pljulia.o: pljulia.c

PG_CONFIG = pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)

And that’s all that is needed for the first round of instant gratification.  The full code is available on GitHub.

This is enough to build, install, and create a PL/Julia extension.  While you can now create user defined functions and stored procedures with PL/Julia, these functions and procedures still won’t be able to do anything particularly useful so we will save those examples for later.

Tags: julia, pl/julia
Share this entry
  • Share on Facebook
  • Share on Twitter
  • Share on WhatsApp
  • Share on LinkedIn
2 replies
  1. eggyknap
    eggyknap says:
    February 5, 2020 at 10:01 pm

    PL/LOLCODE (https://github.com/eggyknap/pllolcode) was intended, among other things, as this sort of pedagogical example. It has remained untouched for a long time, and it’s nice to see someone doing the same thing with a modern PostgreSQL version. It’s also nice that this time, it’s a language that’s actually useful for something.

    Reply
  2. ImreSamu
    ImreSamu says:
    February 6, 2020 at 1:22 am

    I have shared the good news! https://discourse.julialang.org/t/pl-julia-extension-minimal/34232

    Reply

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Search

Get in touch with us!

Recent Posts

  • Random Data December 3, 2020
  • Webinar: COMMIT Without Fear – The Beauty of CAMO [Follow Up] November 13, 2020
  • Full-text search since PostgreSQL 8.3 November 5, 2020
  • Random numbers November 3, 2020
  • Webinar: Best Practices for Bulk Data Loading in PostgreSQL [Follow Up] November 2, 2020

Featured External Blogs

Tomas Vondra's Blog

Our Bloggers

  • Simon Riggs
  • Alvaro Herrera
  • Andrew Dunstan
  • Craig Ringer
  • Francesco Canovai
  • Gabriele Bartolini
  • Giulio Calacoci
  • Ian Barwick
  • Marco Nenciarini
  • Mark Wong
  • Pavan Deolasee
  • Petr Jelinek
  • Shaun Thomas
  • Tomas Vondra
  • Umair Shahid

PostgreSQL Cloud

2QLovesPG 2UDA 9.6 backup Barman BDR Business Continuity community conference database DBA development devops disaster recovery greenplum Hot Standby JSON JSONB logical replication monitoring OmniDB open source Orange performance PG12 pgbarman pglogical PG Phriday postgres Postgres-BDR postgres-xl PostgreSQL PostgreSQL 9.6 PostgreSQL10 PostgreSQL11 PostgreSQL 11 PostgreSQL 11 New Features postgresql repmgr Recovery replication security sql wal webinar webinars

Support & Services

24/7 Production Support

Developer Support

Remote DBA for PostgreSQL

PostgreSQL Database Monitoring

PostgreSQL Health Check

PostgreSQL Performance Tuning

Database Security Audit

Upgrade PostgreSQL

PostgreSQL Migration Assessment

Migrate from Oracle to PostgreSQL

Products

HA Postgres Clusters

Postgres-BDR®

2ndQPostgres

pglogical

repmgr

Barman

Postgres Cloud Manager

SQL Firewall

Postgres-XL

OmniDB

Postgres Installer

2UDA

Postgres Learning Center

Introducing Postgres

Blog

Webinars

Books

Videos

Training

Case Studies

Events

About Us

About 2ndQuadrant

What does 2ndQuadrant Mean?

News

Careers 

Team Profile

© 2ndQuadrant Ltd. All rights reserved. | Privacy Policy
  • Twitter
  • LinkedIn
  • Facebook
  • Youtube
  • Mail
Releasing Cloud Native BDR Operator for Kubernetes Releasing Cloud Native BDR Operator for Kubernetes Application Schema Webinar Webinar: Application Schema Migration [Follow Up]
Scroll to top
×