Skip to content

Instantly share code, notes, and snippets.

@ictlyh

ictlyh/libpq-demo.cc

Last active June 10, 2024 02:46
Show Gist options
  • Save ictlyh/12fe787ec265b33fd7e4b0bd08bc27cb to your computer and use it in GitHub Desktop.
Save ictlyh/12fe787ec265b33fd7e4b0bd08bc27cb to your computer and use it in GitHub Desktop.
Download ZIP
libpq examples.
Raw
libpq-demo.cc
/*
* Demo of libpq.
* Build: g++ libpq-demo.cc -o libpq-demo -lpq
* Run: ./libpq-demo
*/
#include <arpa/inet.h>
#include <iostream>
#include <libpq-fe.h>
#include <sstream>
#include <stdint.h>
#include <stdlib.h>
#include <string.h>
using namespace std;
/*
* Turn host byte sequence to network byte sequence.
*/
char *myhton(char *src, int size) {
char *dest = (char *)malloc(sizeof(char) * size);
switch (size) {
case 1:
*dest = *src;
break;
case 2:
*(uint16_t *)dest = htobe16(*(uint16_t *)src);
break;
case 4:
*(uint32_t *)dest = htobe32(*(uint32_t *)src);
break;
case 8:
*(uint64_t *)dest = htobe64(*(uint64_t *)src);
break;
default:
*dest = *src;
break;
}
memcpy(src, dest, size);
free(dest);
return src;
}
void printPGresult(PGresult *res) {
std::cout << PQntuples(res) << "tuples, " << PQnfields(res) << " fields"
<< std::endl;
// print column name
for (int i = 0; i < PQnfields(res); i++) {
std::cout << PQfname(res, i) << "\t";
}
std::cout << std::endl;
// print column values
for (int i = 0; i < PQntuples(res); i++) {
for (int j = 0; j < PQnfields(res); j++) {
std::cout << PQgetvalue(res, i, j) << "\t";
}
std::cout << std::endl;
}
}
int main() {
const char conninfo[] =
"postgresql://postgres@localhost?port=5432&dbname=libpq_demo";
PGconn *conn = PQconnectdb(conninfo);
/* Check to see that the backend connection was successfully made */
if (PQstatus(conn) != CONNECTION_OK) {
std::cout << "Connection to database failed: " << PQerrorMessage(conn)
<< std::endl;
PQfinish(conn);
return 1;
} else {
std::cout << "Connection to database succeed." << std::endl;
}
PGresult *res = NULL;
/* create table demo */
res = PQexec(conn, "create table if not exists t(id int, name text);");
if (PQresultStatus(res) != PGRES_COMMAND_OK) {
std::cout << "Create table failed: " << PQresultErrorMessage(res)
<< std::endl;
PQclear(res);
return 1;
}
PQclear(res);
/* INSERT demo */
res = PQexec(conn,
"insert into t values(1, 'hello'), (2, 'world'),(3, '....');");
if (PQresultStatus(res) != PGRES_COMMAND_OK) {
std::cout << "Insert into table failed: " << PQresultErrorMessage(res)
<< std::endl;
}
PQclear(res);
/* UPDATE demo */
res = PQexec(conn, "update t set id = 0 where id = 1;");
if (PQresultStatus(res) != PGRES_COMMAND_OK) {
std::cout << "upate failed: " << PQresultErrorMessage(res) << std::endl;
} else {
std::cout << "Update counts: " << PQcmdTuples(res) << std::endl;
}
PQclear(res);
const char command[] = "insert into t values($1, $2);";
char cid[] = "10";
char name[20] = "helloworld2";
int nParams = 2;
const char *const paramValues[] = {cid, name};
const int paramLengths[] = {sizeof(cid), sizeof(name)};
const int paramFormats[] = {0, 0};
int resultFormat = 0;
/* PQexecParams demo */
res = PQexecParams(conn, command, nParams, NULL, paramValues, paramLengths,
paramFormats, resultFormat);
if (PQresultStatus(res) != PGRES_COMMAND_OK) {
std::cout << "PQexecParams failed: " << PQresultErrorMessage(res)
<< std::endl;
}
PQclear(res);
/* PREPARE INSERT demo */
res = PQprepare(conn, "insertStmt", command, nParams, NULL);
if (PQresultStatus(res) != PGRES_COMMAND_OK) {
std::cout << "PQprepare failed:" << PQresultErrorMessage(res) << std::endl;
PQclear(res);
} else {
PQclear(res);
res = PQexecPrepared(conn, "insertStmt", nParams, paramValues, paramLengths,
paramFormats, resultFormat);
if (PQresultStatus(res) != PGRES_COMMAND_OK) {
std::cout << "PQexecPrepared failed: " << PQresultErrorMessage(res)
<< std::endl;
}
PQclear(res);
}
/* SELECT demo */
res = PQexec(conn, "select * from t;");
if (PQresultStatus(res) != PGRES_TUPLES_OK) {
std::cout << "Select failed: " << PQresultErrorMessage(res) << std::endl;
} else {
std::cout << "Get " << PQntuples(res) << "tuples, each tuple has "
<< PQnfields(res) << "fields" << std::endl;
// print column name
for (int i = 0; i < PQnfields(res); i++) {
std::cout << PQfname(res, i) << " ";
}
std::cout << std::endl;
// print column values
for (int i = 0; i < PQntuples(res); i++) {
for (int j = 0; j < PQnfields(res); j++) {
std::cout << PQgetvalue(res, i, j) << " ";
}
std::cout << std::endl;
}
}
PQclear(res);
/* Binary COPY demo */
char header[12] = "PGCOPY\n\377\r\n\0";
int flag = 0;
int extension = 0;
char buffer[100];
memcpy(buffer, header, 11);
memcpy(&buffer[11], (void *)&flag, 4);
memcpy(&buffer[15], (void *)&extension, 4);
short fieldnum = 2;
memcpy(&buffer[19], myhton((char *)&fieldnum, 2), 2);
int size = 4;
memcpy(&buffer[21], myhton((char *)&size, 4), 4);
int id = 10;
memcpy(&buffer[25], (void *)&id, 4);
size = 10;
memcpy(&buffer[29], myhton((char *)&size, 4), 4);
memcpy(&buffer[33], "bbbbbccccc", 10);
short negative = -1;
memcpy(&buffer[43], myhton((char *)&negative, 2), 2);
res = PQexec(conn, "COPY t FROM STDIN (FORMAT binary);");
if (PQresultStatus(res) != PGRES_COPY_IN) {
cout << "Not in COPY_IN mode";
PQclear(res);
} else {
PQclear(res);
cout << "Enter COPY_IN mode" << endl;
int copyRes = PQputCopyData(conn, buffer, 45);
if (copyRes == 1) {
if (PQputCopyEnd(conn, NULL) == 1) {
res = PQgetResult(conn);
if (PQresultStatus(res) == PGRES_COMMAND_OK) {
cout << "Insert a record successfully" << endl;
} else {
cout << PQerrorMessage(conn) << endl;
}
PQclear(res);
} else {
cout << PQerrorMessage(conn) << endl;
}
} else if (copyRes == 0) {
cout << "Send no data, connection is in nonblocking mode" << endl;
} else if (copyRes == -1) {
cout << "Error occur: " << PQerrorMessage(conn) << endl;
}
}
/* Text format COPY demo */
res = PQexec(conn, "COPY t FROM STDIN with(delimiter ',');");
if (PQresultStatus(res) != PGRES_COPY_IN) {
cout << "Not in COPY_IN mode";
PQclear(res);
} else {
PQclear(res);
cout << "Enter COPY_IN mode" << endl;
string str("1,luo haha\n");
if (PQputCopyData(conn, str.c_str(), str.length()) == 1) {
if (PQputCopyEnd(conn, NULL) == 1) {
res = PQgetResult(conn);
if (PQresultStatus(res) == PGRES_COMMAND_OK) {
cout << "Copy " << PQcmdTuples(res) << endl;
} else {
cout << PQresultErrorMessage(res) << endl;
}
printPGresult(res);
PQclear(res);
} else {
cout << PQerrorMessage(conn) << endl;
}
} else {
cout << PQerrorMessage(conn) << endl;
}
}
res = PQexec(conn, "drop table t;");
PQclear(res);
/* PQbackendPID demo */
int pid = PQbackendPID(conn);
if (pid == 0)
cout << "Can't get conections's backend PID" << endl;
else {
// 当前连接调用pg_terminate_backend(pid)总是返回PGRES_FATAL_ERROR
// 要用别的连接调用该函数，使用新连接的pid来终结这个connection
stringstream ss;
ss << "select pg_cancel_backend(" << pid << ")";
PGresult *res = PQexec(conn, ss.str().c_str());
ExecStatusType est = PQresultStatus(res);
cout << PQresStatus(est) << endl;
cout << PQresultErrorMessage(res);
PQclear(res);
}
PQfinish(conn);
return 0;
}
@EauContraire
Copy link

EauContraire commented Feb 1, 2019 •
edited
Loading

Hello Yuanhao,

Thanks for this, I have morphed your work into a 'C' version for those of us out there still writing 'C'.

I have added a section implementing COPY with an insert statement returning values to stdout, of this format:

COPY (INSERT INTO t VALUES (1, 'cc'), (2, 'dd') RETURNING id, name) TO stdout;

I hope it is useful.

With my best regards, Paul.

/*
 * Demo of libpq.
 * Build: g++ libpq-demo.cc -o libpq-demo -lpq
 * Run: ./libpq-demo
 */

#include <arpa/inet.h>
#include <libpq-fe.h>
#include <stdio.h>
#include <stdlib.h>
#include <string.h>

/*
 * Turn host byte sequence to network byte sequence.
 */
char *myhton(char *src, int size)
{
	char *dest = (char *)malloc(sizeof(char) * size);
	switch (size) {
	case 1:
		*dest = *src;
		break;
	case 2:
		*(uint16_t *)dest = htobe16(*(uint16_t *)src);
		break;
	case 4:
		*(uint32_t *)dest = htobe32(*(uint32_t *)src);
		break;
	case 8:
		*(uint64_t *)dest = htobe64(*(uint64_t *)src);
		break;
	default:
		*dest = *src;
		break;
	}
	memcpy(src, dest, size);
	free(dest);
	return src;
}

void printPGresult(PGresult *res)
{
	int i, j;
	printf("printPGresult: %d tuples, %d fields\n", PQntuples(res), PQnfields(res));

	/* print column name */
	for (i = 0; i < PQnfields(res); i++)
		printf("%s\t", PQfname(res, i));

	printf("\n");

	/* print column values */
	for (i = 0; i < PQntuples(res); i++) {
		for (j = 0; j < PQnfields(res); j++) 
			printf("%s\t", PQgetvalue(res, i, j));
		printf("\n");
	}
}

int main() {
	const char conninfo[] =
			"host=127.0.0.1 dbname=hydra_db user=hydra_dev";
	PGconn *conn = PQconnectdb(conninfo);

	/* Check to see that the backend connection was successfully made */
	if (PQstatus(conn) != CONNECTION_OK) {
		fprintf(stderr, "%s[%d]: Connection to database failed: %s\n",
			__FILE__, __LINE__, PQerrorMessage(conn));
		PQfinish(conn);
		return 1;
	} else {
		printf("Connection to database succeed.\n");
	}

	PGresult *res = NULL;

	/* create table demo */
	res = PQexec(conn, "create table if not exists t(id int, name text);");
	if (PQresultStatus(res) != PGRES_COMMAND_OK) {
		fprintf(stderr, "%s[%d]: Create table failed: %s\n",
			__FILE__, __LINE__, PQresultErrorMessage(res));
		PQclear(res);
		return 1;
	}
	PQclear(res);

	/* INSERT demo */
	res = PQexec(conn,
		"insert into t values(1, 'hello'), (2, 'world'),(3, '....');");
	if (PQresultStatus(res) != PGRES_COMMAND_OK)
		fprintf(stderr, "%s[%d]: Insert into failed: %s\n",
			__FILE__, __LINE__, PQresultErrorMessage(res));

	PQclear(res);

	/* UPDATE demo */
	res = PQexec(conn, "update t set id = 0 where id = 1;");
	if (PQresultStatus(res) != PGRES_COMMAND_OK) {
		fprintf(stderr, "%s[%d]: Update failed: %s\n",
			__FILE__, __LINE__, PQresultErrorMessage(res));
	} else {
		printf("Update counts: %d\n", PQcmdTuples(res));
	}
	PQclear(res);

	const char command[] = "insert into t values($1, $2);";
	char cid[] = "10";
	char name[20] = "helloworld2";
	int nParams = 2;
	const char *const paramValues[] = {cid, name};
	const int paramLengths[] = {sizeof(cid), sizeof(name)};
	const int paramFormats[] = {0, 0};
	int resultFormat = 0;
	int i, j;

	/* PQexecParams demo */
	res = PQexecParams(conn, command, nParams, NULL, paramValues, paramLengths,
			 paramFormats, resultFormat);
	if (PQresultStatus(res) != PGRES_COMMAND_OK) {
		fprintf(stderr, "%s[%d]: PQexecParams failed: %s\n",
			__FILE__, __LINE__, PQresultErrorMessage(res));
	}
	PQclear(res);

	/* PREPARE INSERT demo */
	res = PQprepare(conn, "insertStmt", command, nParams, NULL);
	if (PQresultStatus(res) != PGRES_COMMAND_OK) {
		fprintf(stderr, "%s[%d]: PQprepare failed: %s\n",
			__FILE__, __LINE__, PQresultErrorMessage(res));
		PQclear(res);
	} else {
		PQclear(res);
		res = PQexecPrepared(conn, "insertStmt", nParams, paramValues, paramLengths,
			 paramFormats, resultFormat);
		if (PQresultStatus(res) != PGRES_COMMAND_OK) {
			fprintf(stderr, "%s[%d]: PQexecPrepared failed: %s\n",
				__FILE__, __LINE__, PQresultErrorMessage(res));
		}
		PQclear(res);
	}

	/* SELECT demo */
	res = PQexec(conn, "select * from t;");
	if (PQresultStatus(res) != PGRES_TUPLES_OK) {
		fprintf(stderr, "%s[%d]: Select failed: %s\n",
			__FILE__, __LINE__, PQresultErrorMessage(res));
	} else {
		printf("Get %d has %d fields\n", PQntuples(res), PQnfields(res));
		/* print column name */
		for (i = 0; i < PQnfields(res); i++) {
			printf("%s    ", PQfname(res, i));
		}
		putchar('\n');
		/* print column values */
		for (i = 0; i < PQntuples(res); i++) {
			for (j = 0; j < PQnfields(res); j++) {
				printf("%s    ", PQgetvalue(res, i, j));
			}
			putchar('\n');
		}
	}
	PQclear(res);

	/* Binary COPY demo */
	char header[12] = "PGCOPY\n\377\r\n\0";
	int flag = 0;
	int extension = 0;
	char buffer[100];
	memcpy(buffer, header, 11);
	memcpy(&buffer[11], (void *)&flag, 4);
	memcpy(&buffer[15], (void *)&extension, 4);
	short fieldnum = 2;
	memcpy(&buffer[19], myhton((char *)&fieldnum, 2), 2);
	int size = 4;
	memcpy(&buffer[21], myhton((char *)&size, 4), 4);
	int id = 10;
	memcpy(&buffer[25], myhton((char *)&id, 4), 4);
	size = 10;
	memcpy(&buffer[29], myhton((char *)&size, 4), 4);
	memcpy(&buffer[33], "bbbbbccccc", 10);
	short negative = -1;
	memcpy(&buffer[43], myhton((char *)&negative, 2), 2);


	res = PQexec(conn, "COPY t FROM STDIN (FORMAT binary);");
	if (PQresultStatus(res) != PGRES_COPY_IN) {
		fprintf(stderr, "%s[%d]: Not in COPY_IN mode\n",
			__FILE__, __LINE__);
		PQclear(res);
	} else {
		PQclear(res);
		printf("Enter binary COPY_IN mode\n");
		int copyRes = PQputCopyData(conn, buffer, 45);
		if (copyRes == 1) {
			if (PQputCopyEnd(conn, NULL) == 1) {
				res = PQgetResult(conn);
				if (PQresultStatus(res) == PGRES_COMMAND_OK) {
					printf("Inserted a record successfully\n");
				} else {
					fprintf(stderr, "%s[%d]: PQresultStatus failed: %s\n",
						__FILE__, __LINE__, PQresultErrorMessage(res));
				}
				PQclear(res);
			} else {
				fprintf(stderr, "%s[%d]: PQputCopyEnd failed: %s\n",
					__FILE__, __LINE__, PQresultErrorMessage(res));
			}
		} else if (copyRes == 0) {
			printf("Send no data, connection is in nonblocking mode\n");
		} else if (copyRes == -1) {
			fprintf(stderr, "%s[%d]: PQputCopyData failed: %s\n",
				__FILE__, __LINE__, PQresultErrorMessage(res));
		}
	}

	/* Text format COPY demo */
	res = PQexec(conn, "COPY t FROM STDIN with(delimiter ',');");
	if (PQresultStatus(res) != PGRES_COPY_IN) {
		fprintf(stderr, "%s[%d]: Not in COPY_IN mode\n",
			__FILE__, __LINE__);
		PQclear(res);
	} else {
		PQclear(res);
		printf("Enter text COPY_IN mode\n");
		const char *str = "1,luo haha\n";
		if (PQputCopyData(conn, str, strlen(str)) == 1) {
			if (PQputCopyEnd(conn, NULL) == 1) {
				res = PQgetResult(conn);
				if (PQresultStatus(res) == PGRES_COMMAND_OK) {
					printf("Copy %s\n", PQcmdTuples(res));
				} else {
					fprintf(stderr, "%s[%d]: PQresultStatus failed: %s\n",
						__FILE__, __LINE__, PQresultErrorMessage(res));
				}
				printPGresult(res);
				PQclear(res);
			} else {
				fprintf(stderr, "%s[%d]: PQputCopyEnd failed: %s\n",
					__FILE__, __LINE__, PQerrorMessage(conn));
			}
		} else {
			fprintf(stderr, "%s[%d]: PQputCopyData failed: %s\n",
				__FILE__, __LINE__, PQerrorMessage(conn));
		}
	}

	/* Added Paul Ingram 20190204 */
	/* Insert using COPY OUT returning values */
	printf("Enter COPY_OUT with INSERT returning values mode\n");
	res = PQexec(conn, "COPY (INSERT INTO t VALUES (1, 'aa'), (2, 'bb') RETURNING id, name) TO stdout;");
	if ( PQresultStatus(res) != PGRES_COPY_OUT ) {
		fprintf(stderr, "%s[%d]: Not in COPY_OUT mode, %s\n",
			__FILE__, __LINE__, PQresultErrorMessage(res));
		PQclear(res);
	} else {
		char *copybuf = NULL;

		PQclear(res);
		/* Note we are not asynchronous */
		while ( (i = PQgetCopyData(conn, &copybuf, 0)) > 0 ) {
			printf("PQgetCopyData: read %d bytes, %s", i, copybuf);
		}
		if ( i == -2 )
			fprintf(stderr, "%s[%d]: PQgetCopyData error, %s\n",
				__FILE__, __LINE__, PQerrorMessage(conn));

		if (copybuf != NULL )
			PQfreemem(copybuf);
	}

	res = PQexec(conn, "drop table t;");
	PQclear(res);

	/* PQbackendPID demo */
	int pid = PQbackendPID(conn);
	if (pid == 0) {
		fprintf(stderr, "%s[%d]: Can't get connection's backend PID\n",
			__FILE__, __LINE__);
	} else {
	
		/* 当前连接调用pg_terminate_backend(pid)总是返回PGRES_FATAL_ERROR
		 * 要用别的连接调用该函数，使用新连接的pid来终结这个connection */
		char ss[1024];
		snprintf(ss, sizeof ss - 1,"select pg_cancel_backend(%d)", pid);
		res = PQexec(conn, ss);
		ExecStatusType est = PQresultStatus(res);
		printf("\nPQresStatus: %s\nPQresultErrorMessage: %s\n", PQresStatus(est), PQresultErrorMessage(res));
		PQclear(res);
	}
	PQfinish(conn);
	return 0;
}

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment