plisql

Overview

from src/pl/plisql/src/plisql.control and IvorySQL package metadata

Version

Install

You can install ivorysql directly. First, make sure the PGDG and PIGSTY repositories are added and enabled:

pig repo add pgsql -u          # Add repo and update cache

Install the extension using pig or apt/yum/dnf:

• Install
• pig
• dnf
• apt

pig install ivorysql;          # Install for current active PG version

pig ext install -y ivorysql -v 18  # PG 18

dnf install -y ivorysql5       # PG 18

apt install -y ivorysql-5   # PG 18

Create Extension:

CREATE EXTENSION plisql;

Usage

plisql: PL/iSQL procedural language

PL/iSQL is an Oracle-compatible procedural language for PostgreSQL, provided by the IvorySQL project. It extends PL/pgSQL with Oracle PL/SQL syntax and semantics.

Enabling

CREATE EXTENSION plisql;

Creating Functions

CREATE OR REPLACE FUNCTION hello_world
RETURN VARCHAR2
AS
BEGIN
    RETURN 'Hello, World!';
END;
/

Oracle-Style Procedures

CREATE OR REPLACE PROCEDURE update_salary(
    p_emp_id IN NUMBER,
    p_amount IN NUMBER
)
AS
BEGIN
    UPDATE employees SET salary = salary + p_amount WHERE emp_id = p_emp_id;
END;
/

CALL update_salary(100, 5000);

Key Features

• Oracle-style BEGIN...END blocks
• IN, OUT, IN OUT parameter modes
• Oracle-style exception handling with named exceptions
• %TYPE and %ROWTYPE attribute references
• Oracle-compatible cursor syntax (CURSOR...IS, OPEN, FETCH, CLOSE)
• RETURN instead of RETURNS in function declarations
• Package-like variable scoping

Differences from PL/pgSQL

• Uses AS keyword instead of $$ delimiters
• Supports Oracle-style / as statement terminator
• VARCHAR2, NUMBER, and other Oracle types natively supported
• Oracle-compatible DBMS_OUTPUT integration