plxcsharp: the C# dialect

plxcsharp lets you write PostgreSQL functions with C# syntax. At CREATE FUNCTION time plx transpiles the body to plpgsql and stores the plpgsql in pg_proc.prosrc. The function then runs on the standard plpgsql interpreter.

The language name is plxcsharp.

C# is a brace-family language, but it differs from plpgsql in ways a keyword map cannot cover: declarations put the type first, strings come in several forms, + joins strings, switch sections end with break, and exceptions are classes. plxcsharp therefore reads the body with a C# parser that resolves types, then lowers the statements through the same engine the other dialects use. The generated plpgsql is ordinary and readable; pg_proc.prosrc shows exactly what runs.

Setup

CREATE EXTENSION IF NOT EXISTS plx;

Function basics

The body is the statement list of a C# method body: no method signature, no class, no using directives. Statements end with ; and blocks use braces. Both the K&R and the Allman brace styles work, and a single statement may follow if, else, while, for, foreach and do without braces.

CREATE FUNCTION add(a int, b int) RETURNS int LANGUAGE plxcsharp AS $$
return a + b;
$$;

CREATE FUNCTION grade(score int) RETURNS text LANGUAGE plxcsharp AS $$
if (score >= 90)
{
    return "A";
}
else if (score >= 80)
    return "B";
return "F";
$$;
SELECT add(2, 3), grade(85);

The signature uses PostgreSQL types

The parameter and return types in CREATE FUNCTION are parsed by PostgreSQL before plx sees the body, so they are PostgreSQL type names: write RETURNS bigint, not RETURNS long. Inside the body, C# type names in declarations are translated (see Declarations and types). Parameters are referenced by name. OUT and INOUT parameters are assigned by name.

CREATE FUNCTION split_name(full_name text, OUT first_name text, OUT last_name text)
LANGUAGE plxcsharp AS $$
int space = full_name.IndexOf(" ");
first_name = full_name.Substring(0, space);
last_name = full_name.Substring(space + 1);
$$;
SELECT * FROM split_name('Ada Lovelace');

Declarations and types

A local is declared with its type first, as in C#. Every local is hoisted into the plpgsql DECLARE block.

int count = 0;
string name;                    // declared, assigned later
var total = 0L;                 // var takes the type of its value: bigint
const int MaxItems = 100;       // a plpgsql CONSTANT
int? maybe = null;              // nullable: the same as int
decimal[] prices = {9.99m, 4.50m};
numeric(10,2) amount = 0;       // a PostgreSQL type name works as written
orders row;                     // so does a table row type

Several declarators may share one statement (int a = 1, b = 2;). A const is initialized before any statement runs, so its value must be a constant expression: literals, other constants, and mapped constants such as Math.PI.

Type mapping

C# PostgreSQL
int, Int32, ushort integer
long, Int64, uint bigint
short, byte, sbyte smallint
ulong, decimal numeric
double double precision
float real
bool boolean
string, char text
DateTime timestamp
DateTimeOffset timestamptz
DateOnly date
TimeOnly time
TimeSpan interval
Guid uuid
byte[] bytea
T[] the mapped T as a PostgreSQL array, so int[] is integer[]
T? T
StringBuilder text, built with the plx string builder (see Building strings)
any other name used as a PostgreSQL type name: jsonb, numeric(10,2), orders

A char is stored as one-character text, so it compares and concatenates like a string. Arithmetic on a char is rejected; convert with (int)c (which is ascii(c)) or (char)n (which is chr(n)).

var takes the type of its initializer, which must be known: a literal, a string expression, a cast, a mapped member, an array creation, another typed variable, or fetch_one(...) (a record). When the type cannot be worked out, for example var x = some_sql_function(y);, declare it explicitly. An unsuffixed real literal such as 1.5 is PostgreSQL numeric, as in the other dialects, so var x = 1.5; declares numeric; write 1.5d or double x = 1.5; for a double. The literal suffixes L, U, UL, F, D and M select bigint, bigint, numeric, real, double precision and numeric.

Names

PostgreSQL ignores case in unquoted names, so total and Total are the same variable. plxcsharp rejects two locals that differ only in case, and a local whose name matches a parameter. A local cannot be named after a word that PostgreSQL or PL/pgSQL reserves (user, order, limit, end, left, both, by, …); the error names the word.

Locals are function-scoped, as everywhere in plx. Declaring the same name again in a sibling block is allowed when the type is the same; a different type is an error.

C# to PostgreSQL translations

Statements

C# plpgsql
int x = e; x integer; in DECLARE, then x := e; (folded into the declaration at the top level)
x = e; x := e;
x += e; (also -= *= /= %= &= |= ^= <<= >>=) x := x + e;
s += e; on a string local the plx string builder (see Building strings)
x++; / ++x; / x--; x := x + 1; / x := x - 1;
x ??= e; x := COALESCE(x, e);
if (c) { } else if (c) { } else { } IF c THEN ... ELSIF c THEN ... ELSE ... END IF;
while (c) { } WHILE c LOOP ... END LOOP;
do { } while (c); LOOP ... EXIT WHEN (c) IS NOT TRUE; END LOOP;
for (int i = a; i < b; i++) FOR i IN a..(b) - 1 LOOP
for (int i = a; i >= b; i -= 2) FOR i IN REVERSE a..b BY 2 LOOP
other for (init; cond; iter) init, then a loop that tests cond and runs iter on every pass
for (;;) LOOP ... END LOOP;
foreach (var r in query(sql)) FOR r IN sql LOOP
foreach (int v in arr) FOREACH v IN ARRAY arr LOOP
switch (x) { case 1: ... break; default: ... } CASE x WHEN 1 THEN ... ELSE ... END CASE;
break; / continue; EXIT; / CONTINUE;
return e; RETURN e;
yield return e; / yield break; RETURN NEXT e; / RETURN;
throw new Exception(m); RAISE EXCEPTION '%', m;
throw; RAISE;
try { } catch (E e) { } finally { } a BEGIN ... EXCEPTION ... END block
Console.WriteLine(x); RAISE NOTICE '%', x;
Debug.Assert(c, m); ASSERT c, m;

Expressions

C# PostgreSQL
==, != =, <>
x == null, x is null x IS NULL
x != null, x is not null x IS NOT NULL
&&, ||, ! AND, OR, NOT
&, |, ^ on bool AND, OR, <>
&, |, ^, ~, <<, >> on integers &, |, #, ~, <<, >>
"a" + x (either side a string) 'a' || x
a + b (numbers) a + b
c ? a : b CASE WHEN c THEN a ELSE b END
a ?? b COALESCE(a, b)
x switch { 1 => "a", _ => "b" } CASE x WHEN 1 THEN 'a' ELSE 'b' END
(int)x (trunc(x))::integer (C# truncates); (x)::integer when x is already an integer
(double)x, (string)x, … (x)::double precision, (x)::text, …
a[i] a[i + 1] (C# arrays are 0-based, PostgreSQL arrays 1-based)
s[i] on a string substr(s, i + 1, 1)
row["col"] row.col
x?.Member, x! x.Member, x (a NULL already propagates through SQL functions)
new[] {1, 2}, [1, 2] ARRAY[1, 2]
new int[] {1, 2}, int[] a = {1, 2} ARRAY[1, 2]::integer[]
new int[n] array_fill(0::integer, ARRAY[n])
nameof(x) 'x'
null, true, false NULL, true, false

Any other name passes through to SQL: a call to a function plx does not map is a call to the PostgreSQL function of that name (upper(s), now(), my_schema.my_func(x)), and a bare name such as current_date is the SQL value. A C# named argument f(x: 1) becomes SQL named notation f(x => 1).

Strings

All the C# string forms work, and each becomes an ordinary SQL string literal:

string a = "tab\there, quote \" and \u00e9";   // regular: C# escapes
string b = @"C:\temp\file ""quoted""";           // verbatim: backslashes are literal
string c = """
    SELECT "raw" text
      keeps its relative indentation
    """;                                          // raw: indentation is stripped
char ch = 'x';

A regular string supports \', \", \\, \a, \b, \e, \f, \n, \r, \t, \v, \xH..HHHH, \uHHHH and \UHHHHHHHH. A NUL character (\0) cannot be stored in a PostgreSQL string and is rejected.

Interpolation

An interpolated string $"..." (also $@"..." and the raw $"""...""") puts each {expression} into the text. {{ and }} are literal braces; in a raw string with two dollar signs ($$"""...""") holes are written {{x}} and a single brace is literal.

CREATE FUNCTION greet(nm text, n int) RETURNS text LANGUAGE plxcsharp AS $$
return $"{nm} has {n} item{(n == 1 ? "" : "s")}, average {10.0 / n:F2}";
$$;
SELECT greet('Ann', 3);

The format specifier :F (and :F0 to :F99) rounds to that many decimal places. Other format specifiers and alignment ({x,10}) are rejected; format the value with to_char() inside the braces instead.

An interpolated NULL makes the whole string NULL, which is what SQL || does and what the other plx dialects do. In a message for throw or Console.WriteLine each NULL value becomes an empty string instead, so one NULL cannot swallow the message.

The + operator

+ is string concatenation when either operand is known to be a string: a string or character literal, an interpolated string, a string or char local or parameter, a mapped string member such as s.Trim(), a cast to string, or the result of a concatenation. Otherwise + is numeric addition. C# evaluates + from left to right, and so does the translation:

CREATE FUNCTION plus_demo(a int, b int) RETURNS text LANGUAGE plxcsharp AS $$
return ("x" + a + b) + " " + (a + b + "x");
$$;
SELECT plus_demo(1, 2);

The result is x12 3x, as in C#. A NULL operand makes the result NULL (C# treats it as an empty string); string.Concat(a, b) keeps the C# behaviour, because it is SQL concat(), which skips NULLs. When neither operand is known to be a string, for example two record fields, convert one: r.first + "" + r.last or $"{r.first}{r.last}".

Building strings

Appending to a string in a loop with s = s + x copies the whole string on every pass. Use s += x on a string local, or a StringBuilder: both lower to the plx string builder (plx_strbuild), which appends in amortized constant time on PostgreSQL 18.

CREATE FUNCTION csv(n int) RETURNS text LANGUAGE plxcsharp AS $$
var sb = new StringBuilder();
for (int i = 1; i <= n; i++)
{
    if (i > 1) sb.Append(",");
    sb.Append(i);
}
return sb.ToString();
$$;
SELECT csv(5);

StringBuilder supports Append(x), AppendLine(), AppendLine(x), AppendFormat(format, args), Clear(), ToString() and Length, and Append calls may be chained.

.NET members

plxcsharp maps a small set of .NET members to SQL. Anything else is rejected when the function is created, except a call on a name plx does not know, which is taken to be a schema-qualified SQL function.

C# PostgreSQL
s.Length length(s) (cardinality(a) on an array)
s.ToUpper(), s.ToLower() (and the Invariant forms) upper(s), lower(s)
s.Trim(), s.TrimStart(), s.TrimEnd() (optionally with characters) btrim, ltrim, rtrim; with no argument they remove white space as char.IsWhiteSpace defines it (in a database that is not UTF-8, only tab through carriage return and space)
s.Contains(x) strpos(s, x) > 0 (x = ANY(a) on an array)
s.StartsWith(x), s.EndsWith(x) starts_with(s, x), a suffix test
s.IndexOf(x) strpos(s, x) - 1 (0-based, -1 when absent)
s.Substring(i), s.Substring(i, n) substr(s, i + 1), substr(s, i + 1, n)
s.Replace(a, b) replace(s, a, b)
s.PadLeft(n), s.PadRight(n, c) lpad, rpad (never truncating, as in C#; s must be a variable, field or literal)
s.Split(sep) string_to_array(s, sep)
x.ToString(), x.ToString("F2") (x)::text, round(x, 2)
x.Equals(y) x = y
string.IsNullOrEmpty(s) COALESCE(s, '') = ''
string.IsNullOrWhiteSpace(s) true for NULL, empty or whitespace only
string.Join(sep, values) array_to_string(values, sep, '')
string.Concat(a, b, ...) concat(a, b, ...)
string.Format("{0} {1,5}", a, b) format('%1$s %2$5s', a, b)
string.Empty, Environment.NewLine '', a newline
Math.Abs, Max, Min, Floor, Ceiling, Truncate, Round, Sqrt, Pow, Exp, Log, Log10, Sign abs, GREATEST, LEAST, floor, ceil, trunc, round, sqrt, power, exp, ln, log, sign
Math.PI, Math.E pi(), exp(1)
Convert.ToInt32(x) and the other Convert.To... (x)::integer, …
int.Parse(s), decimal.Parse(s), DateTime.Parse(s), Guid.Parse(s), … (s)::integer, …
int.MaxValue, long.MinValue, short.MaxValue, … the limit as a literal
DateTime.Now, DateTime.UtcNow, DateTime.Today localtimestamp, timezone('UTC', now()), current_date::timestamp
DateTimeOffset.Now, DateTimeOffset.UtcNow now()
d.Year, Month, Day, Hour, Minute, Second, DayOfYear, DayOfWeek, Date extract(...), date_trunc('day', d)
d.AddDays(n), AddHours, AddMinutes, AddSeconds, AddMonths, AddYears d + n * interval '1 day', …
Guid.NewGuid(), Guid.Empty gen_random_uuid(), the zero UUID
char.IsDigit(c), IsLetter, IsWhiteSpace, IsLetterOrDigit, char.ToUpper(c), char.ToLower(c) a regular expression test, upper, lower

DateTime.Now and the other clock members read the transaction’s start time, as now() does, not the wall clock at the moment of the call.

Control flow

if, while and do

do { } while (c); runs its body at least once. A continue inside it goes to the test, as in C#.

for

A counting loop over an int or short variable, with a <, <=, > or >= test against a bound and a step of ++, --, += K or -= K (a positive literal K), becomes a plpgsql integer FOR. That needs the loop body to leave the variable and the bound unchanged, and the bound to make no function call: then evaluating the bound once, as plpgsql does, is the same as testing it on every pass, as C# does.

Any other for keeps C# semantics exactly: the initializers run once, the test runs before every pass, and the iterators run after every pass, also after a continue.

CREATE FUNCTION loops(n int) RETURNS text LANGUAGE plxcsharp AS $$
long sum = 0;
for (int i = 1; i <= n; i++) sum += i;              // integer FOR
string pairs = "";
for (int i = 0, j = n; i < j; i++, j--)             // general form
{
    if (i == 1) continue;
    pairs += $"({i},{j})";
}
return $"{sum} {pairs}";
$$;
SELECT loops(6);

foreach

foreach iterates a query (see Working with data), an array, or the characters of a string. Over an array the element type comes from the array’s type, or from the declared loop variable.

CREATE FUNCTION count_vowels(words text[]) RETURNS int LANGUAGE plxcsharp AS $$
int n = 0;
foreach (var w in words)
    foreach (char c in w.ToLower())
        if ("aeiou".Contains(c)) n++;
return n;
$$;
SELECT count_vowels(ARRAY['Education', 'sky']);

switch

A switch statement becomes a plpgsql CASE. Stacked labels share a section, default may appear anywhere, and a switch with no matching section and no default does nothing. Each section must end in break, return, throw or continue, as C# requires. A break inside a nested statement of a section leaves the switch, not an enclosing loop, as in C#.

CREATE FUNCTION day_kind(d int) RETURNS text LANGUAGE plxcsharp AS $$
string kind;
switch (d)
{
    case 0:
    case 6:
        kind = "weekend";
        break;
    case 1: case 2: case 3: case 4: case 5:
        kind = "weekday";
        break;
    default:
        throw new ArgumentOutOfRangeException($"no day {d}");
}
return kind;
$$;
SELECT day_kind(0), day_kind(3);

A switch expression (x switch { ... }) takes constant patterns, _, null, not null, the relational patterns < e, <= e, > e, >= e, and when guards. With anything but constants it needs a variable as its subject.

CREATE FUNCTION size_of(n int) RETURNS text LANGUAGE plxcsharp AS $$
return n switch { < 0 => "negative", 0 => "zero", < 10 => "small", _ => "large" };
$$;
SELECT size_of(-1), size_of(0), size_of(5), size_of(50);

Working with data

The SQL intrinsics are the same as in the other plx dialects and keep their plx names: query, fetch_one, fetch_one!, execute, perform, return_query, open_cursor, fetch_from, close_cursor, move_cursor, row_count(), found(), call, commit(), rollback(), raise, assert, emit and return_next.

SQL templates

When the SQL argument is an interpolated string, it is a template: each hole is a value bound as a parameter, never spliced into the SQL text, so it cannot inject SQL. ident(x) splices a quoted identifier, literal(x) a quoted literal, and raw(x) trusted SQL text. A plain string is static SQL. See DYNAMIC-SQL.md.

String literals and interpolated strings joined with + are one template, so a statement split across lines still binds its values:

long n = fetch_one("SELECT count(*) " +
                   "FROM orders " +
                   $"WHERE grp = {g}");

SQL built any other way (in a variable, with string.Format, or with + and a variable) is run as dynamic SQL text, exactly as written; pass values as bind arguments then: execute(sql, a, b).

CREATE TABLE orders (id int PRIMARY KEY, grp int, amount bigint);
INSERT INTO orders VALUES (1, 1, 100), (2, 1, 250), (3, 1, 75), (4, 2, 500);

CREATE FUNCTION order_total(g int) RETURNS bigint LANGUAGE plxcsharp AS $$
long total = 0;
foreach (var row in query($"SELECT amount FROM orders WHERE grp = {g}"))
{
    total += row.amount;
}
return total;
$$;
SELECT order_total(1);

The loop variable is a record, and a typed loop variable takes a single column: foreach (long amt in query("SELECT amount FROM orders")). A table row type works too: foreach (orders o in query("SELECT * FROM orders")). Bind arguments after the SQL are $1, $2, …: query("SELECT ... WHERE grp = $1", g).

Static SQL sees the function’s variables, so a local or parameter with the same name as a column the SQL uses makes that SQL ambiguous, and plpgsql reports column reference "amount" is ambiguous when it runs. Give locals names that differ from the columns, or qualify the column with its table (orders.amount).

A multi-line query reads well as a raw interpolated string:

foreach (var r in query($"""
    SELECT id, amount
    FROM orders
    WHERE grp = {g}
    ORDER BY id
    """))
{
    ...
}

Single rows, statements, and dynamic SQL

CREATE FUNCTION order_info(oid int) RETURNS text LANGUAGE plxcsharp AS $$
var o = fetch_one($"SELECT id, amount FROM orders WHERE id = {oid}");
long n = fetch_one("SELECT count(*) FROM orders");
perform($"UPDATE orders SET amount = amount WHERE id = {oid}");
int changed = row_count();
string col = "amount";
long biggest = fetch_one($"SELECT max({ident(col)}) FROM orders");
return $"order {o.id}: {o.amount} of {n}, changed {changed}, max {biggest}";
$$;
SELECT order_info(2);

fetch_one returns a row with all fields NULL when nothing matches; fetch_one! raises no_data_found or too_many_rows. With var (or a row type) the target is a record and its columns are read as o.col or o["col"]. With a scalar type such as long the variable takes the query’s single column. execute(sql, a, b) runs dynamic SQL with bind arguments.

Cursors

var c = open_cursor("SELECT amount FROM orders ORDER BY id");
var row = fetch_from(c);
while (found())
{
    total += row.amount;
    row = fetch_from(c);
}
close_cursor(c);

Set-returning functions

A function declared RETURNS SETOF produces rows with yield return and ends with yield break or return;. return_query(sql) appends a whole query.

CREATE FUNCTION squares(n int) RETURNS SETOF int LANGUAGE plxcsharp AS $$
for (int i = 1; i <= n; i++)
    yield return i * i;
$$;
SELECT * FROM squares(4);

With RETURNS TABLE(...) the columns are output parameters: assign them and call return_next().

CREATE FUNCTION order_lines(g int) RETURNS TABLE(id int, label text) LANGUAGE plxcsharp AS $$
foreach (orders o in query($"SELECT * FROM orders WHERE grp = {g} ORDER BY id"))
{
    id = o.id;
    label = $"#{o.id}: {o.amount}";
    return_next();
}
$$;
SELECT * FROM order_lines(1);

Procedures and DO blocks

A procedure can end the transaction with commit() or rollback(). A DO block takes C# too:

DO LANGUAGE plxcsharp $$
for (int i = 1; i <= 2; i++)
    Console.WriteLine($"step {i}");
$$;

Errors

Throwing

throw new SomeException(message) raises an error with that message. The class picks the SQLSTATE: one of the mapped classes below, otherwise P0001 (raise_exception). Any class name ending in Exception may be thrown. With no argument the message is the class name. Inside a catch, passing the caught exception as a second argument (new Exception("failed", e)) puts its message into the error’s detail.

To raise a specific SQLSTATE with a detail and a hint, use Npgsql’s PostgresException constructor, (messageText, severity, invariantSeverity, sqlState), with the optional detail and hint arguments (by position or by name):

CREATE FUNCTION check_amount(v int) RETURNS int LANGUAGE plxcsharp AS $$
if (v < 0)
    throw new PostgresException($"negative amount {v}", "ERROR", "ERROR", "22023",
                                detail: "amounts are never negative", hint: "pass zero or more");
return v;
$$;
SELECT check_amount(5);

throw; inside a catch rethrows the caught error unchanged; so does throw e; for the caught variable e.

Catching

try/catch/finally becomes a plpgsql exception block. The finally block runs on every exit: normal completion, an error (which is raised again after it), and a return, break or continue out of the try.

catch clause plpgsql condition
catch, catch (Exception e), catch (PostgresException e), catch (NpgsqlException e), catch (DbException e), catch (SystemException e) OTHERS
catch (DivideByZeroException e) division_by_zero
catch (OverflowException e) numeric_value_out_of_range
catch (FormatException e) invalid_text_representation
catch (ArgumentNullException e) null_value_not_allowed
catch (PostgresException e) when (e.SqlState == "23505") SQLSTATE '23505'
... when (e.SqlState == PostgresErrorCodes.UniqueViolation) unique_violation
... when (e.SqlState == "23502" || e.SqlState is "22003" or "22P02") SQLSTATE '23502' OR ...

A when filter compares e.SqlState with a five-character SQLSTATE string or a PostgresErrorCodes name, alone or joined with || or the is ... or ... pattern. A PostgresErrorCodes name becomes the plpgsql condition name in snake case (UniqueViolation is unique_violation); a name plpgsql does not know is reported when the function first runs. Catch clauses are tried in order, as in C#.

The caught exception exposes these properties:

C# plpgsql
e.Message, e.MessageText, e.ToString() SQLERRM
e.SqlState SQLSTATE
e.Detail, e.Hint PG_EXCEPTION_DETAIL, PG_EXCEPTION_HINT
e.ConstraintName, e.ColumnName, e.TableName, e.SchemaName, e.DataTypeName the GET STACKED DIAGNOSTICS field of that name
CREATE FUNCTION add_order(i int) RETURNS text LANGUAGE plxcsharp AS $$
try
{
    execute("INSERT INTO orders VALUES ($1, 9, 0)", i);
    return "inserted";
}
catch (PostgresException e) when (e.SqlState == PostgresErrorCodes.UniqueViolation)
{
    return $"duplicate: {e.ConstraintName}";
}
catch (Exception e)
{
    return $"failed ({e.SqlState}): {e.Message}";
}
$$;
SELECT add_order(1), add_order(NULL);

Messages and assertions

Console.WriteLine(x) (and Console.Write) raises a NOTICE. With a format string and arguments it formats the message like string.Format. The raise(level, message) intrinsic raises at any level (debug, log, info, notice, warning, exception). Debug.Assert(condition, message) and assert(condition, message) become plpgsql ASSERT.

Trigger functions

A function returning trigger reads and assigns NEW and OLD fields and returns NEW (or null to skip the row in a BEFORE trigger). TG_OP, TG_TABLE_NAME and the other TG_ variables are available; TG_ARGV keeps its plpgsql indexing, which already starts at 0.

CREATE TABLE products (id int, code text, label text, revisions int DEFAULT 0);

CREATE FUNCTION products_stamp() RETURNS trigger LANGUAGE plxcsharp AS $$
NEW.code = NEW.code.Trim().ToUpper();
NEW.label = TG_OP == "INSERT" ? $"new {NEW.code}" : $"was {OLD.code}";
NEW.revisions += 1;
return NEW;
$$;

CREATE TRIGGER products_stamp BEFORE INSERT OR UPDATE ON products
  FOR EACH ROW EXECUTE FUNCTION products_stamp();
INSERT INTO products(id, code) VALUES (1, ' ab ');
UPDATE products SET code = 'cd';
SELECT * FROM products;

Debugging

pg_proc.prosrc holds the generated plpgsql, and DEBUGGING.md shows how to recover the original C# from it. An error at CREATE FUNCTION names the line of the C# source. With SET client_min_messages = debug1, CREATE FUNCTION also logs the intermediate program that the C# parser hands to the plpgsql lowering, with every expression already in SQL, which shows how each construct was read.

Semantic differences

These are intentional. plx pins semantics to SQL and plpgsql rather than emulating the .NET runtime.

  • A local, parameter or result column may not be named sqlstate or sqlerrm (in any case): inside a catch, plpgsql’s variables of those names would hide it. CREATE FUNCTION fails and asks you to rename it.
  • A NULL takes part in SQL three-valued logic. A comparison with a NULL operand is unknown, so neither a == b nor a != b is true; only a comparison with the literal null becomes IS NULL. A positive if/while condition treats unknown as false.
  • + with a NULL string operand yields NULL, where C# treats null as an empty string. string.Concat keeps the C# behaviour.
  • Integer arithmetic raises numeric_value_out_of_range on overflow, where C# wraps silently (in an unchecked context). Integer division and % truncate toward zero in both.
  • An unsuffixed real literal is exact numeric, not a double.
  • A numeric (decimal) value rounds half away from zero, where .NET rounds half to even. That covers Math.Round on a decimal, Math.Round(x, digits), the Convert.To... conversions of a decimal, and the :F format. Math.Round of a double rounds half to even, as in C#, but the :F format and ToString("F2") of a double round its decimal form half away from zero: 2.675 with :F2 is 2.68 and 2.5 with :F0 is 3 (.NET: 2.67 and 2). A cast (int)x truncates toward zero, as in C#.
  • Decimal division follows C#’s scale for a quotient that ends: it keeps at least as many decimal places as the dividend has more than the divisor, and drops trailing zeros past that (7m / 2 is 3.5, 250.00m / 2 is 125.00). A quotient that does not end keeps PostgreSQL’s precision (1m / 3m is 0.33333333333333333333), where .NET keeps 28 significant digits.
  • Math.Round(x, digits) on a decimal gives exactly digits decimal places (Math.Round(5m, 2) is 5.00; C#: 5).
  • byte, sbyte, ushort and uint are the wider SQL types smallint, integer and bigint, so they neither wrap nor fail at their C# range: byte b = 255; b++ gives 256 (C#: 0) and (byte)300 is 300 (C#: 44).
  • Arrays are values: int[] b = a; copies a, so a later b[0] = 9 leaves a unchanged, and foreach walks the array as it was when the loop began. In C# both names refer to one array.
  • A double converts to text in PostgreSQL’s form (1e+15, where .NET writes 1E+15), and a float (real) combined with an integer becomes a double (1.1f + 1 is 2.100000023841858; C#: 2.1). % on double values, and dividing a double by zero, raise an error; C# gives a remainder and Infinity.
  • Substring past the end of the string returns what there is instead of throwing ("abc".Substring(5) is ""), and "".Split(",") has no parts (C#: one empty part).
  • Math.Log(x, b) with two arguments returns numeric (3.0000000000000000 for Math.Log(8, 2)), and fails for double arguments.
  • Convert.ToInt32 of a NULL string is NULL (C#: 0). int.Parse, bool.Parse and the other Parse methods read text as PostgreSQL does, so they also accept yes and, on PostgreSQL 16 and later, 0x1F and 1_000, where .NET throws.
  • Length counts characters, so a character outside the Basic Multilingual Plane counts once ("a😀b".Length is 3; C#: 4 UTF-16 code units).
  • bool values print as true and false, not True and False.
  • Reading an array past its end yields NULL and assigning past it extends the array, where C# throws IndexOutOfRangeException.
  • A switch expression with no _ arm yields NULL when nothing matches, where C# throws.
  • The bound of a counting for loop is evaluated once, only where that cannot be observed (see for).
  • DateTime.Now and the other clock members return the transaction start time.
  • Locals are function-scoped, and a local is NULL until assigned (C# would refuse to compile the read).
  • Strings compare with the database collation. Equality is exact; < and > between strings are not C# (which has no such operators) but pass through to SQL.

Not supported

Rejected at CREATE FUNCTION time with a source line number:

  • Methods, local functions, lambdas, delegates, classes, structs, records, interfaces, enums, namespaces, using, attributes, generics, and preprocessor directives.
  • object and dynamic, tuples and deconstruction, ref/out/in arguments, pointers and unsafe, fixed, stackalloc.
  • LINQ, List<T>, Dictionary<K,V> and the other collections; use arrays or SQL.
  • Multi-dimensional and jagged arrays, ranges and indexes from the end (a[1..3], ^1).
  • new other than array creation, new StringBuilder() in a declaration, and new ...Exception(...) after throw; object and collection initializers other than array initializers.
  • goto, labels, lock, checked/unchecked, async/await, and yield in anything but a set-returning function.
  • An assignment, ++ or -- inside an expression (x = y = 0, a[i++]), and a function call in the target of a compound assignment (a[f()] += 1).
  • case labels with patterns (case int n:, case > 5:, case null:, when guards, or/and); use stacked constant labels or if.
  • is and switch-expression patterns other than those listed above, and as.
  • Interpolation format specifiers other than :F, and alignment ({x,10}).
  • catch of exception classes other than those in the table above, and when filters other than e.SqlState comparisons.
  • default, typeof, sizeof, this, base.
  • Arithmetic on char values.
  • .NET members other than those listed in .NET members.
  • Calling a function for its side effects as a statement (DoWork(x);); use perform("SELECT do_work(...)").
  • A brace inside a string literal that is itself inside an interpolation hole, and string literals nested more than two interpolations deep; assign the inner string to a variable first.
  • Non-ASCII characters outside strings and comments, including in names.

See PARITY.md for the plpgsql construct matrix and LIMITATIONS.md for the constraints every dialect shares.