Postgres

与 上一节 一样，这里我们直接介绍 libpq 接口，而不是 wrapper package。

本演示中使用的数据模型与 SQLite 章节 中使用的相同。

注意：在执行类似 PQexec 的函数查询后，如果有返回结果，请检查结果状态是否为 PGRES_TUPLES_OK，否则应使用 PGRES_COMMAND_OK。

• https://www.postgresql.org/docs/current/libpq-exec.html#LIBPQ-PQRESULTSTATUS

//! Libpq API example
//! https://gist.github.com/jiacai2050/00709b98ee69d73d022d2f293555f08f
//! https://www.postgresql.org/docs/16/libpq-example.html
//!
const std = @import("std");
const print = std.debug.print;
const c = @cImport({
    @cInclude("libpq-fe.h");
});

const DB = struct {
    conn: *c.PGconn,

    fn init(conn_info: [:0]const u8) !DB {
        const conn = c.PQconnectdb(conn_info);
        if (c.PQstatus(conn) != c.CONNECTION_OK) {
            print("Connect failed, err: {s}\n", .{c.PQerrorMessage(conn)});
            return error.connect;
        }
        return DB{ .conn = conn.? };
    }

    fn deinit(self: DB) void {
        c.PQfinish(self.conn);
    }

    // Execute a query without returning any data.
    fn exec(self: DB, query: [:0]const u8) !void {
        const result = c.PQexec(self.conn, query);
        defer c.PQclear(result);

        if (c.PQresultStatus(result) != c.PGRES_COMMAND_OK) {
            print("exec query failed, query:{s}, err: {s}\n", .{ query, c.PQerrorMessage(self.conn) });
            return error.Exec;
        }
    }

    fn insertTable(self: DB) !void {
        // 1. create two prepared statements.
        {
            // There is no `get_last_insert_rowid` in libpq, so we use RETURNING id to get the last insert id.
            const res = c.PQprepare(
                self.conn,
                "insert_cat_colors",
                "INSERT INTO cat_colors (name) VALUES ($1) returning id",
                1, // nParams, number of parameters supplied
                // Specifies, by OID, the data types to be assigned to the parameter symbols.
                // When null, the server infers a data type for the parameter symbol in the same way it would do for an untyped literal string.
                null, // paramTypes.
            );
            defer c.PQclear(res);
            if (c.PQresultStatus(res) != c.PGRES_COMMAND_OK) {
                print("prepare insert cat_colors failed, err: {s}\n", .{c.PQerrorMessage(self.conn)});
                return error.prepare;
            }
        }
        {
            const res = c.PQprepare(self.conn, "insert_cats", "INSERT INTO cats (name, color_id) VALUES ($1, $2)", 2, null);
            defer c.PQclear(res);
            if (c.PQresultStatus(res) != c.PGRES_COMMAND_OK) {
                print("prepare insert cats failed, err: {s}\n", .{c.PQerrorMessage(self.conn)});
                return error.prepare;
            }
        }
        const cat_colors = .{
            .{
                "Blue", .{
                    "Tigger",
                    "Sammy",
                },
            },
            .{
                "Black", .{
                    "Oreo",
                    "Biscuit",
                },
            },
        };

        // 2. Use prepared statements to insert data.
        inline for (cat_colors) |row| {
            const color = row.@"0";
            const cat_names = row.@"1";
            const color_id = blk: {
                const res = c.PQexecPrepared(
                    self.conn,
                    "insert_cat_colors",
                    1, // nParams
                    &[_][*c]const u8{color}, // paramValues
                    &[_]c_int{color.len}, // paramLengths
                    &[_]c_int{0}, // paramFormats
                    0, // resultFormat
                );
                defer c.PQclear(res);

                // Since this insert has returns, so we check res with PGRES_TUPLES_OK
                if (c.PQresultStatus(res) != c.PGRES_TUPLES_OK) {
                    print("exec insert cat_colors failed, err: {s}\n", .{c.PQresultErrorMessage(res)});
                    return error.InsertCatColors;
                }
                break :blk std.mem.span(c.PQgetvalue(res, 0, 0));
            };
            inline for (cat_names) |name| {
                const res = c.PQexecPrepared(
                    self.conn,
                    "insert_cats",
                    2, // nParams
                    &[_][*c]const u8{ name, color_id }, // paramValues
                    &[_]c_int{ name.len, @intCast(color_id.len) }, // paramLengths
                    &[_]c_int{ 0, 0 }, // paramFormats
                    0, // resultFormat, 0 means text, 1 means binary.
                );
                defer c.PQclear(res);

                // This insert has no returns, so we check res with PGRES_COMMAND_OK
                if (c.PQresultStatus(res) != c.PGRES_COMMAND_OK) {
                    print("exec insert cats failed, err: {s}\n", .{c.PQresultErrorMessage(res)});
                    return error.InsertCats;
                }
            }
        }
    }

    fn queryTable(self: DB) !void {
        const query =
            \\ SELECT
            \\     c.name,
            \\     cc.name
            \\ FROM
            \\     cats c
            \\     INNER JOIN cat_colors cc ON cc.id = c.color_id;
            \\
        ;

        const result = c.PQexec(self.conn, query);
        defer c.PQclear(result);

        if (c.PQresultStatus(result) != c.PGRES_TUPLES_OK) {
            print("exec query failed, query:{s}, err: {s}\n", .{ query, c.PQerrorMessage(self.conn) });
            return error.queryTable;
        }

        const num_rows = c.PQntuples(result);
        for (0..@intCast(num_rows)) |row| {
            const cat_name = std.mem.span(c.PQgetvalue(result, @intCast(row), 0));
            const color_name = std.mem.span(c.PQgetvalue(result, @intCast(row), 1));
            print("Cat {s} is in {s} color\n", .{ cat_name, color_name });
        }
    }
};

pub fn main() !void {
    const conn_info = "host=127.0.0.1 user=postgres password=postgres dbname=postgres";

    const db = try DB.init(conn_info);
    defer db.deinit();

    try db.exec(
        \\ create table if not exists cat_colors (
        \\   id integer primary key generated always as identity,
        \\   name text not null unique
        \\ );
        \\ create table if not exists cats (
        \\   id integer primary key generated always as identity,
        \\   name text not null,
        \\   color_id integer not null references cat_colors(id)
        \\ );
    );

    try db.insertTable();
    try db.queryTable();
}