Postgresql在Linux下的安装配置与C程序应用

(6)C程序应用

建立一个序列数s_id

CREATE SEQUENCE s_id
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 20
CACHE 1;
ALTER TABLE s_id OWNER TO postgresql;
和一个表test：
CREATE TABLE test

(
id integer NOT NULL,
name character varying NOT NULL,
age integer NOT NULL,
CONSTRAINT test_pkey PRIMARY KEY (id)
)
WITH OIDS;
ALTER TABLE test OWNER TO postgresql;
C代码例子如下：
QUOTE:
//test.cpp
#include "libpq-fe.h"
#include
#include
using namespace std;
static void exit_nicely(PGconn *conn)
{
PQfinish(conn);
exit(1);
}
int main()
{
int i, nFields, j;
struct pg_conn * conn = 0;
PGresult *res = 0;
conn = PQsetdbLogin("192.168.1.4", "5432", "", "", "postgresql", "postgresql", "postgresql");
if(PQstatus(conn) != CONNECTION_OK)
{
printf("connect fail \n");
}else
{
printf("connect success\n");
}
res = PQexec(conn, "BEGIN");
if(PQresultStatus(res) != PGRES_COMMAND_OK)
{
printf("execute sql fail %s", PQerrorMessage(conn));
PQclear(res);
exit_nicely(conn);
}
PQclear(res);
for(i = 0; i < 10; i++)
{
res = PQexec(conn, "insert into test(select nextval('s_id'), 'dog', 3)");
PQclear(res);
}
res = PQexec(conn, "DECLARE myportal CURSOR FOR select * from test where name = 'dog'");
//res = PQexec(conn, "select * from test where name = 'duanjigang'");
if (PQresultStatus(res) != PGRES_COMMAND_OK)
{
printf("DECLARE CURSOR failed: %s", PQerrorMessage(conn));
PQclear(res);
exit_nicely(conn);
}
PQclear(res);
res = PQexec(conn, "FETCH ALL in myportal");
if (PQresultStatus(res) != PGRES_TUPLES_OK)
{
printf("FETCH ALL failed: %s", PQerrorMessage(conn));
PQclear(res);
exit_nicely(conn);
}
nFields = PQnfields(res);
for (i = 0; i < nFields; i++)
printf("%-15s", PQfname(res, i));
printf("\n\n");
for (i = 0; i < PQntuples(res); i++)
{
for (j = 0; j < nFields; j++)
printf("%-15s", PQgetvalue(res, i, j));
printf("\n");
}
PQclear(res);
res = PQexec(conn, "delete from test where name = 'dog'");
PQclear(res);
res = PQexec(conn, "CLOSE myportal");
PQclear(res);
res = PQexec(conn, "END");
PQclear(res);
PQfinish(conn);
return 0;
}
Makefile
#!/bin/sh
default:
g++ -c test.cpp -I /usr/local/pgsql/include/
g++ -o test test.o -L/usr/local/pgsql/lib -lpq
clean:
rm -fr test test.o

可能在链接或者运行时提示不能打开libpq这个库，你需要修改/etc/ld.so.conf文件，并在中添加/usr/local/pgsql/lib，然后执行ldconfig命令，再次运行./test就能看到sql语句的数出了

具体的文档可以去CU的中文文档察看，postgresql安装包doc中也有详细的说明。