Contents
- C# cookbook
- Scalar function with branching
- Accumulating loop
- Building a string in a loop
- Looping over a query
- Set-returning function
- Error handling with try / catch
- Trigger function
- Dynamic SQL with bind parameters
- C# idioms: interpolation, foreach, and switch
- Single-row lookup with a ternary
- Defaults, switch expressions, and formatting
C# cookbook
Practical recipes for the plxcsharp dialect. Every recipe here was run on PostgreSQL; plx transpiles the body to plpgsql and the standard interpreter executes it. See the plxcsharp chapter for the full language reference.
Scalar function with branching
Declare the result with its type, assign it through an if / else if / else chain, and return it. Single statements after if and else need no braces.
CREATE FUNCTION grade(score int) RETURNS text LANGUAGE plxcsharp AS $$
string g;
if (score >= 90) g = "A";
else if (score >= 80) g = "B";
else if (score >= 70) g = "C";
else g = "F";
return g;
$$;
SELECT grade(95) AS a, grade(85) AS b, grade(72) AS c, grade(40) AS f;
a | b | c | f
---+---+---+---
A | B | C | F
(1 row)
Accumulating loop
A counting for loop over an int becomes a plpgsql integer FOR. The accumulator is declared long, which is bigint.
CREATE FUNCTION factorial(n int) RETURNS bigint LANGUAGE plxcsharp AS $$
long result = 1;
for (int i = 2; i <= n; i++)
{
result *= i;
}
return result;
$$;
SELECT factorial(10);
factorial
-----------
3628800
(1 row)
Building a string in a loop
Use += on a string local. plx lowers it to its string builder, which is amortized O(1) per append on PostgreSQL 18 instead of the O(n2) copy that s = s + x would produce.
CREATE FUNCTION csv_repeat(word text, n int) RETURNS text LANGUAGE plxcsharp AS $$
string s = "";
for (int i = 1; i <= n; i++)
{
s += word;
if (i < n) s += ",";
}
return s;
$$;
SELECT csv_repeat('ab', 4);
csv_repeat
-------------
ab,ab,ab,ab
(1 row)
A StringBuilder lowers to the same builder, and its Append calls chain:
CREATE FUNCTION squares_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).Append("^2=").Append(i * i);
}
return sb.ToString();
$$;
SELECT squares_csv(4);
squares_csv
-----------------------------
1^2=1, 2^2=4, 3^2=9, 4^2=16
(1 row)
Looping over a query
foreach (var r in query(...)) iterates a result set. The row is a record and columns are read as r.col. An interpolated string passed to query is a SQL template: the value of {g} is bound as a parameter, not spliced into the SQL text.
CREATE FUNCTION order_total(g int) RETURNS bigint LANGUAGE plxcsharp AS $$
long total = 0;
foreach (var r in query($"SELECT amount FROM orders WHERE grp = {g}"))
{
total += r.amount;
}
return total;
$$;
Setup and call:
CREATE TABLE orders (id int, grp int, amount bigint);
INSERT INTO orders VALUES (1,1,100),(2,1,250),(3,1,75),(4,2,500);
SELECT order_total(1);
order_total
-------------
425
(1 row)
Set-returning function
A function declared RETURNS TABLE(...) streams rows with return_query. Qualify the column names with the table name so they are not read as the output parameters of the same name.
CREATE FUNCTION order_report(g int) RETURNS TABLE(id int, amount bigint) LANGUAGE plxcsharp AS $$
return_query($"SELECT orders.id, orders.amount FROM orders WHERE grp = {g} ORDER BY orders.amount DESC");
$$;
SELECT * FROM order_report(1);
id | amount
----+--------
2 | 250
1 | 100
3 | 75
(3 rows)
To produce computed rows one at a time, use yield return with RETURNS SETOF, as in a C# iterator:
CREATE FUNCTION evens(lo int, hi int) RETURNS SETOF int LANGUAGE plxcsharp AS $$
for (int i = lo; i <= hi; i++)
{
if (i % 2 == 0) yield return i;
}
$$;
SELECT array_agg(x) FROM evens(1, 10) AS x;
array_agg
--------------
{2,4,6,8,10}
(1 row)
Error handling with try / catch
try / catch maps to a plpgsql exception block. The exception class picks the condition: DivideByZeroException is division_by_zero, and Exception catches everything. e.Message reads SQLERRM, and Console.WriteLine raises a NOTICE.
CREATE FUNCTION safe_divide(a int, b int) RETURNS int LANGUAGE plxcsharp AS $$
try
{
return a / b;
}
catch (DivideByZeroException e)
{
Console.WriteLine($"caught: {e.Message}");
return -1;
}
$$;
SELECT safe_divide(10, 2) AS ok, safe_divide(10, 0) AS zero;
NOTICE: caught: division by zero
ok | zero
----+------
5 | -1
(1 row)
To catch a specific database error, filter on e.SqlState the way Npgsql code does, with a SQLSTATE string or a PostgresErrorCodes name. The diagnostic properties (ConstraintName, ColumnName, TableName, …) are available inside the handler.
CREATE FUNCTION open_account(i int, who text) RETURNS text LANGUAGE plxcsharp AS $$
try
{
execute("INSERT INTO accounts VALUES ($1, $2)", i, who);
return $"opened {i}";
}
catch (PostgresException e) when (e.SqlState == PostgresErrorCodes.UniqueViolation)
{
return $"account {i} exists ({e.ConstraintName})";
}
catch (PostgresException e) when (e.SqlState == "23502")
{
return $"missing {e.ColumnName}";
}
$$;
CREATE TABLE accounts (id int PRIMARY KEY, owner text NOT NULL);
SELECT open_account(1, 'ada') AS first, open_account(1, 'bob') AS again,
open_account(2, NULL) AS no_owner;
first | again | no_owner
----------+----------------------------------+---------------
opened 1 | account 1 exists (accounts_pkey) | missing owner
(1 row)
Trigger function
A function returning trigger can be attached to a table. Assign to NEW fields and return NEW. This one trims and upper-cases a code and derives a label before the row is written, on both insert and update.
CREATE FUNCTION normalize_product() RETURNS trigger LANGUAGE plxcsharp AS $$
NEW.code = NEW.code.Trim().ToUpper();
NEW.label = $"code:{NEW.code}";
return NEW;
$$;
CREATE TABLE products (id int, code text, label text);
CREATE TRIGGER trg_norm BEFORE INSERT OR UPDATE ON products
FOR EACH ROW EXECUTE FUNCTION normalize_product();
INSERT INTO products(id, code) VALUES (1, 'abc'), (2, ' xy ');
SELECT id, code, label FROM products ORDER BY id;
id | code | label
----+------+----------
1 | ABC | code:ABC
2 | XY | code:XY
(2 rows)
Dynamic SQL with bind parameters
Use execute(sql, arg, ...) and reference the arguments as $1, $2, and so on. Bind parameters keep untrusted values out of the SQL text, which avoids injection. row_count() reads how many rows the statement affected.
CREATE FUNCTION log_event(msg text) RETURNS bigint LANGUAGE plxcsharp AS $$
execute("INSERT INTO events(note) VALUES ($1)", msg);
long n = row_count();
return n;
$$;
CREATE TABLE events (id serial PRIMARY KEY, note text);
SELECT log_event('deploy started');
SELECT note FROM events ORDER BY id;
log_event
-----------
1
(1 row)
note
----------------
deploy started
(1 row)
When a table or column name is itself a value, interpolate it through ident(), which quotes it as an identifier. Plain values in the same template are still bound, so a hostile value matches nothing:
CREATE FUNCTION count_in(tbl text, col text, val text) RETURNS bigint LANGUAGE plxcsharp AS $$
long n = fetch_one($"SELECT count(*) FROM {ident(tbl)} WHERE {ident(col)} = {val}");
return n;
$$;
SELECT count_in('products', 'code', 'ABC') AS hits,
count_in('products', 'code', $x$ABC' OR 'x' = 'x$x$) AS injection_blocked;
hits | injection_blocked
------+-------------------
1 | 0
(1 row)
C# idioms: interpolation, foreach, and switch
This function combines the common C# idioms plx supports: a foreach over a query, ++ and +=, a switch statement that lowers to CASE, and an interpolated string that puts several values into the return string.
CREATE FUNCTION describe_grp(g int) RETURNS text LANGUAGE plxcsharp AS $$
int count = 0;
long total = 0;
foreach (var r in query($"SELECT amount FROM orders WHERE grp = {g}"))
{
count++;
total += r.amount;
}
string size;
switch (count)
{
case 0: size = "empty"; break;
case 1: size = "single"; break;
default: size = "many"; break;
}
return $"group {g}: {count} orders ({size}), total {total}";
$$;
SELECT describe_grp(1) AS g1, describe_grp(2) AS g2;
g1 | g2
-------------------------------------+---------------------------------------
group 1: 3 orders (many), total 425 | group 2: 1 orders (single), total 500
(1 row)
Single-row lookup with a ternary
fetch_one returns one row, or a row with all fields NULL when nothing matches. A ternary (c ? a : b, which lowers to CASE) plus an == null test turns the miss into a readable message. x == null becomes IS NULL.
CREATE FUNCTION user_greeting(uid int) RETURNS text LANGUAGE plxcsharp AS $$
var u = fetch_one($"SELECT name FROM users WHERE id = {uid}");
return u.name == null ? $"no user #{uid}" : $"hello, {u.name}";
$$;
CREATE TABLE users (id int, name text);
INSERT INTO users VALUES (1, 'Ada'), (2, 'Grace');
SELECT user_greeting(1) AS found, user_greeting(99) AS missing;
found | missing
------------+-------------
hello, Ada | no user #99
(1 row)
Defaults, switch expressions, and formatting
?? supplies a default for NULL (it is COALESCE), a switch expression with relational patterns picks a band, and string.Format with an alignment pads a column. The :F2 format in an interpolated string rounds to two decimal places.
CREATE FUNCTION price_tag(amount numeric, discount numeric) RETURNS text LANGUAGE plxcsharp AS $$
decimal net = amount * (1 - (discount ?? 0m) / 100);
string band = net switch { < 10 => "budget", < 100 => "standard", _ => "premium" };
return string.Format("{0,-9}|{1}", band, $"{net:F2}");
$$;
SELECT price_tag(19.99, 25) AS a, price_tag(250, NULL) AS b, price_tag(5, 10) AS c;
a | b | c
-----------------+------------------+----------------
standard |14.99 | premium |250.00 | budget |4.50
(1 row)