PostgreSQL C 语言编程

2016-08-20 Saturday     database , postgresql , linux

对于 PostgreSQL 的 C 语言编程,可以直接使用 libpq 库,这里简单介绍其使用方法。

libpq

在 CentOS 中,处理通过源码编译安装之外,还可以安装 postgresqlXX-devel 包,然后可以通过如下的方式进行编译。

$ pg_config --includedir
$ pg_config --libdir
$ gcc -I/usr/pgsql-9.2/include/ -L/usr/pgsql-9.2/lib/ -lpq -o version version.c

如下是一个常见操作的示例集合。

#include <stdio.h>
#include <stdlib.h>
#include <libpq-fe.h>

int main()
{
	int ver, i;
	PGconn *conn;
	PGresult *res;

	/* Get the libpq library version. */
	ver = PQlibVersion();
	printf("Libpq version: %d\n", ver);

	/* Connect to PostgreSQL with default user and database. */
	conn = PQconnectdb("user=postgres dbname=postgres");
	if (PQstatus(conn) == CONNECTION_BAD) {
		fprintf(stderr, "Connection to database failed: %s\n", PQerrorMessage(conn));
		PQfinish(conn);
		exit(EXIT_FAILURE);
	}

	/* Get the connection infomation */
	printf("User: %s\n", PQuser(conn));
	printf("Database name: %s\n", PQdb(conn));
	printf("Password: %s\n", PQpass(conn));

	/* Get the server version */
	ver = PQserverVersion(conn);
	printf("Server version: %d\n", ver);

	/* Retrieve the server version again with different method */
	res = PQexec(conn, "SELECT VERSION()");
	if (PQresultStatus(res) != PGRES_TUPLES_OK) {
		fprintf(stderr, "No data retrieved\n");
		PQclear(res);
		PQfinish(conn);
		exit(EXIT_FAILURE);
	}
	printf("Current version: %s\n", PQgetvalue(res, 0, 0));
	PQclear(res);

	/* Create a table, and insert some data */
	res = PQexec(conn, "DROP TABLE IF EXISTS foobar");
	if (PQresultStatus(res) != PGRES_COMMAND_OK) {
		fprintf(stderr, "Drop table failed: %s\n", PQerrorMessage(conn));
		PQclear(res);
		PQfinish(conn);
		exit(EXIT_FAILURE);
	}
	PQclear(res); /* Free the result */

	res = PQexec(conn, "CREATE TABLE foobar(id INTEGER PRIMARY KEY, name VARCHAR(32))");
	if (PQresultStatus(res) != PGRES_COMMAND_OK) {
		fprintf(stderr, "Create table failed: %s\n", PQerrorMessage(conn));
		PQclear(res);
		PQfinish(conn);
		exit(EXIT_FAILURE);
	}
	PQclear(res);

	res = PQexec(conn, "INSERT INTO foobar VALUES(1, 'Audi'),(2, 'Volvo')");
	if (PQresultStatus(res) != PGRES_COMMAND_OK) {
		fprintf(stderr, "Insert into table failed: %s\n", PQerrorMessage(conn));
		PQclear(res);
		PQfinish(conn);
		exit(EXIT_FAILURE);
	}
	PQclear(res);

	/* Retrieve multiple rows of data and also it's metadata */
	res = PQexec(conn, "SELECT * FROM foobar LIMIT 5");
	if (PQresultStatus(res) != PGRES_TUPLES_OK) {
		fprintf(stderr, "No data retrieved\n");
		PQclear(res);
		PQfinish(conn);
		exit(EXIT_FAILURE);
	}
	int ncols = PQnfields(res);
	printf("There are %d columns:\n", ncols);
	for (i = 0; i < ncols; i++)
		printf("col #%d: %s\n", i, PQfname(res, i));
	for(i = 0; i < PQntuples(res); i++) /* another way */
		printf("line #%d: %s %s\n", i, PQgetvalue(res, i, 0), PQgetvalue(res, i, 1));
	PQclear(res);


	/* Prepared statements */
	const char *params[1] = { "1" };
	char *stm = "SELECT * FROM foobar WHERE Id=$1";
	res = PQexecParams(conn, stm, 1, NULL, params, NULL, NULL, 0);
	if (PQresultStatus(res) != PGRES_TUPLES_OK) {
		fprintf(stderr, "No data retrieved\n");
		PQclear(res);
		PQfinish(conn);
		exit(EXIT_FAILURE);
	}
	printf("Data: %s %s\n", PQgetvalue(res, 0, 0), PQgetvalue(res, 0, 1));
	PQclear(res);

	/* Transaction */
	res = PQexec(conn, "BEGIN");
	if (PQresultStatus(res) != PGRES_COMMAND_OK) {
		fprintf(stderr, "BEGIN command failed\n");
		PQclear(res);
		PQfinish(conn);
		exit(EXIT_FAILURE);
	}
	PQclear(res);

	res = PQexec(conn, "UPDATE foobar SET name='Mercedes' WHERE id=1");
	if (PQresultStatus(res) != PGRES_COMMAND_OK) {
		fprintf(stderr, "UPDATE command failed\n");
		PQclear(res);
		PQfinish(conn);
		exit(EXIT_FAILURE);
	}
	/* Maybe more statements */
	res = PQexec(conn, "COMMIT");
	if (PQresultStatus(res) != PGRES_COMMAND_OK) {
		fprintf(stderr, "COMMIT command failed\n");
		PQclear(res);
		PQfinish(conn);
		exit(EXIT_FAILURE);
	}
	PQclear(res);

	PQfinish(conn);

	return 0;
}

pgcenter

pgcenter 是一个 PG 终端的监控工具,但又不仅仅是监控工具,还提供了一些快速访问功能,例如编辑配置文件、重载配置、查看日志文件等。

可以直接查看 github.com/lesovsky/pgcenter,实际上很多的查询 SQL 是在 pg-utils

----- 安装依赖库
# yum install ncurses-devel postgresql-devel
----- 直接编译、安装
# make && make install

----- 登陆查看当前的状态
$ pgcenter -h <host> -p <port> -U <user> -d <dbname>
$ pgcenter -h 127.1 -p 5432 -U postgres -d postgres

参考

另外常用的 C 语言组件还有 libzdb、pgpool (提供连接池+负载均衡)、pgbouncer (只有连接池)。

关于 C 语言编程,可以参考 libpqlibpq-example



如果喜欢这里的文章,而且又不差钱的话,欢迎打赏个早餐 ^_^


About This Blog

Recent Posts

Categories

Related Links

  • RTEMS
    RTEMS
  • GNU
  • Linux Kernel
  • Arduino

Search


This Site was built by Jin Yang, generated with Jekyll, and hosted on GitHub Pages
©2013-2019 – Jin Yang