Announcing pl/dotnet, version 0.99 (beta)

Announcing pl/dotnet, version 0.99 (beta)

pl/dotnet adds full support for C# and F# to PostgreSQL. 0.99 is our public beta release; we wish to share its amazingness with the world.

• We support all PL operations: functions, procedures, DO, SPI, triggers, records, SRF, OUT/INOUT, table functions, etc
• We natively support 40 out of 46 standard user types, the most of any external PL
• Fully NPGSQL-compatible, and SPI is exposed through the NPGSQL API for maximum compatibility
• In our benchmarks, C# and F# are the fastest Procedural Languages in PostrgreSQL
• All features are fully tested for both C# and F#, with 1013 unit tests
• 100% free software under the PostgreSQL license

This is a beta release; we invite usage and welcome feedback.

Accessing it:

• Installation instructions: dpkg, docker
• Our GitHub
• Our discussion forum
• Our WebPage
• Our FAQ
• Our Wiki
• Our whitepaper

More details below.

Usage example

pl/dotnet gives you the full power of C# and F# in your PostgreSQL procedures, functions, and triggers.

CREATE OR REPLACE FUNCTION dynamic_record_generator_srf(lim INT8)  
RETURNS SETOF record  
AS $$  
    upperLimit = lim.HasValue ? lim : System.Int32.MaxValue;  
    for(long i=0;i<upperLimit;i++){ yield return new object?[] { i, $"Number is {i}" }; }  
$$ LANGUAGE plcsharp;

CREATE OR REPLACE FUNCTION dynamic_record_generator_srf_fsharp(lim INT8)  
RETURNS SETOF record  
AS $$  
    let upperLimit = Option.defaultValue (int64 System.Int32.MaxValue) lim  
    seq { for i in 0L .. upperLimit - 1L do yield [| box i; $"Number is {i}" |] }  
$$ LANGUAGE plfsharp;

SQL features

We support all SQL function modes:

• normal procedures and functions
• trigger functions, will full trigger support: trigger arguments, old/new row, row rewriting (where allowed), and all the standard trigger information
• set-returning functions, nicely mapped to iterators in C# and sequences in F#
• table functions, as well as functions returning records or sets of records
• full support for IN/OUT/INOUT functions

Data type support

We support 40 PostgreSQL types, with all mapped to their NPGSQL-standard dotnet types. The only notable exceptions are multirange, enum, and struct types, which we hope to add in the future. All datatypes are nullable, have full array support, and are fully unit-tested for C# and F#. (Formatted as a list of (PostgreSQL type: Dotnet type) instead of a table for technical reasons.)

• bit: BitArray
• bool: Bool
• box: NpgsqlBox
• bpchar: String
• bytea: Byte[]
• cidr: (IPAddress Address, Int Netmask)
• circle: NpgsqlCircle
• date: DateOnly
• daterange: NpgsqlRange<DateOnly>
• float4: Float
• float8: Double
• inet: (IPAddress Address, Int Netmask)
• int2: Short
• int4: Int
• int4range: NpgsqlRange<Int>
• int8: Long
• int8range: NpgsqlRange<Long>
• interval: NpgsqlInterval
• json: String
• line: NpgsqlLine
• lseg: NpgsqlLSeg
• macaddr8: PhysicalAddress
• macaddr: PhysicalAddress
• money: Decimal
• path: NpgsqlPath
• point: NpgsqlPoint
• polygon: NpgsqlPolygon
• record: Object?[]
• text: String
• timestamp: DateTime
• timestamptz: DateTime
• time: TimeOnly
• timetz: DateTimeOffset
• tsrange: NpgsqlRange<DateTime>
• tstzrange: NpgsqlRange<DateTime>
• uuid: Guid
• varbit: BitArray
• varchar: String
• void: Void
• xml: String

SPI

Our SPI leverages the NPGSQL client library to provide a native dotnet implementation which is maximally compatible with existing client code. We intercepted the NPGSQL calls at a very low level to replace the client protocol handling with SPI calls; otherwise, NPGSQL was unmodified. We imported the NPGSQL test suite as stored procedures and are using it for our testing, giving us very good understanding of our compatiblity level.

Work remains to improve the compatibility and add features. Our biggest category of NPGSQL tests that continue to fail is error mapping, because SPI throws exceptions differently than NPGSQL does. Such incompatibilities are minor but numerous; we continue working to improve them.

Here are our currently tested SPI operations:

• Data Manipulation Language (DML) Operations
  • Select
  • Insert
  • Update
  • Delete
• Data Definition Language (DDL) Operations
  • Create Table
  • Alter Table
  • Drop Table
  • Truncate
  • Create Index
  • Drop Index
  • Create View
  • Drop View
  • Create Function
  • Call Function
  • Drop Function
  • Create Procedure
  • Call Procedure
  • Drop Procedure
• Transaction Control
  • Begin Transaction
  • Commit
  • Rollback
• Supported Data Types: all pl/dotnet types
  • Basic types (including ranges)
  • Array types
  • Record

What we don't have

We lack support for multirange, enum, and composite/table types. We intend to add them.

Our SPI implementation lacks some minor features like sub-transactions, and we sometimes raise errors in a different (and therefore slightly incompatible) way from NPGSQL.

Our build system, with both dpkg and binary output, is functional but not as tidy as we would like.

We welcome code submissions to address any of these issues, and we hope to improve them all in time.

Where to get it

Our operating system support:

• We fully support Linux and provide dpkg's for Debian and Ubuntu. (Details on our installation page)
• We have built and tested the system on OSX (arm and x86), but we have not packaged it yet.
• We look forward to having Windows support soon; ping us if you would like to help.

Getting in touch

We welcome hearing from the community. You can reach us at our GitHub discussion forum or email us at pldotnet@brickabode.com.