pkg:db/postgres

PostgreSQL wire protocol v3 client.

import "pkg:db/postgres"

Overview

Minimal PostgreSQL driver for CRUD operations using the simple query protocol. Connects via TCP, authenticates with cleartext password, executes SQL queries, and returns structured result sets. Hostnames are resolved via vera:net/dns, so both IP addresses and domain names are supported.

Connection string format: postgres://user:password@host:port/database

Limitations (v1):

Cleartext password authentication only (no MD5/SCRAM)
No TLS/SSL
No prepared statements (simple query protocol only)
All values returned as text strings

Dependencies: vera:net, vera:net/dns, vera:memory, vera:string, vera:binary

Functions

`pg_connect`

function pg_connect(url: ptr, url_len: i64) -> ptr

Connect to a PostgreSQL server. Parses the connection string, resolves the hostname via DNS, opens a TCP connection, sends a StartupMessage, and handles password authentication.

Parameters:

url (ptr) — pointer to the connection string (e.g., "postgres://user:pass@host:5432/dbname")
url_len (i64) — length of the connection string in bytes

Returns: an opaque connection handle (ptr), or null on failure.

`pg_close`

function pg_close(conn: ptr)

Close a PostgreSQL connection. Sends a Terminate message to the server, closes the TCP socket, and frees all connection memory.

Parameters:

conn (ptr) — the connection handle returned by pg_connect

`pg_exec`

function pg_exec(conn: ptr, sql: ptr, sql_len: i64) -> i64

Execute a non-SELECT SQL statement (INSERT, UPDATE, DELETE, CREATE, DROP, etc.) using the simple query protocol.

Parameters:

conn (ptr) — the connection handle
sql (ptr) — pointer to the SQL statement text
sql_len (i64) — length of the SQL statement in bytes

Returns: the number of affected rows, or -1 on error. For DDL statements (CREATE, DROP), returns 0 on success.

`pg_query`

function pg_query(conn: ptr, sql: ptr, sql_len: i64) -> ptr

Execute a SELECT statement and return a result set. The caller must call pg_result_free when done with the result.

Parameters:

conn (ptr) — the connection handle
sql (ptr) — pointer to the SQL query text
sql_len (i64) — length of the SQL query in bytes

Returns: an opaque result set handle (ptr), or null on error.

`pg_result_rows`

function pg_result_rows(result: ptr) -> i64

Get the number of rows in a result set.

Parameters:

result (ptr) — the result set handle returned by pg_query

Returns: the number of rows.

`pg_result_columns`

function pg_result_columns(result: ptr) -> i64

Get the number of columns in a result set.

Parameters:

result (ptr) — the result set handle returned by pg_query

Returns: the number of columns.

`pg_result_column_name`

function pg_result_column_name(result: ptr, col: i64, out_buf: ptr, out_size: i64) -> i64

Get a column name by index. Copies the name into the caller-provided buffer.

Parameters:

result (ptr) — the result set handle
col (i64) — zero-based column index
out_buf (ptr) — caller-allocated buffer for the column name
out_size (i64) — size of out_buf in bytes

Returns: the number of bytes written to out_buf, or -1 if col is out of range.

`pg_result_get`

function pg_result_get(result: ptr, row: i64, col: i64, out_buf: ptr, out_size: i64) -> i64

Get a cell value as a string. Copies the value into the caller-provided buffer.

Parameters:

result (ptr) — the result set handle
row (i64) — zero-based row index
col (i64) — zero-based column index
out_buf (ptr) — caller-allocated buffer for the cell value
out_size (i64) — size of out_buf in bytes

Returns: bytes written to out_buf, 0 for an empty string, or -1 for SQL NULL.

`pg_result_is_null`

function pg_result_is_null(result: ptr, row: i64, col: i64) -> i32

Check if a cell value is SQL NULL.

Parameters:

result (ptr) — the result set handle
row (i64) — zero-based row index
col (i64) — zero-based column index

Returns: 1 if the value is NULL, 0 if not NULL, or -1 if the indices are out of range.

`pg_result_free`

function pg_result_free(result: ptr)

Free a result set and all associated memory. Must be called when you are done reading query results.

Parameters:

result (ptr) — the result set handle to free

Example

import "pkg:db/postgres"
import "vera:memory"
import "vera:io"

data conn_str: "postgres://myuser:mypass@localhost:5432/mydb"
data select_sql: "SELECT id, name FROM users"
data insert_sql: "INSERT INTO users (name) VALUES ('alice')"

function main() -> i32 {
    ; Connect
    constant.ptr p_url, conn_str
    constant.i64 url_len, 44
    call conn, pg_connect, p_url, url_len

    ; Execute a write statement
    constant.ptr p_insert, insert_sql
    constant.i64 insert_len, 41
    call affected, pg_exec, conn, p_insert, insert_len

    ; Execute a query
    constant.ptr p_select, select_sql
    constant.i64 select_len, 26
    call result, pg_query, conn, p_select, select_len

    ; Read results
    call num_rows, pg_result_rows, result
    call num_cols, pg_result_columns, result

    ; Iterate rows
    constant.i64 zero, 0
    constant.i64 one, 1
    copy.i64 row, zero
    stack_allocate val_buf, 256, 8
    constant.i64 val_size, 256

.row_loop:
    compare_signed_greater_equal.i64 done, row, num_rows
    branch_if done, .cleanup, .read_row

.read_row:
    call len, pg_result_get, result, row, zero, val_buf, val_size
    call _, print, val_buf, len
    add.i64 row, row, one
    jump .row_loop

.cleanup:
    ; Free result set and close connection
    call _, pg_result_free, result
    call _, pg_close, conn
    constant.i32 exit_ok, 0
    return exit_ok
}

The VERA Book