PostgreSQL functions and libpq
Today at work my boss said "You've worked with Postgres before, right? Can you show us how to call a stored procedure from the C calling interface?" I've done this before, but not for a long time, so I immediately dived into Google to refresh my memory. And came up totally blank.
As is far too often the case, I found lots of hits that weren't very closely related to the topic at hand, and a few that were. The few that were have been repeated hundreds of times, by various sites "syndicating" (i.e. duplicating) other sites posting, and/or numerous email or news group archives, to the point where the signal/noise ratio was vanishingly near zero.
The few posts that were topical had the usual responses of "it's in the doco, read here where here is linked to some 10-year-old PostgreSQL documentation archive that no longer exists, and wouldn't be correct for up to date versions if it did exist. Besides, I searched the doco, and if I didn't find it on the first or second pass, some newbie has no hope of finding it buried in there. So I started hacking.
The world of PostgreSQL functions has changed quite a bit in the 8.x world. In fact, one of the big differences in porting database access code from Oracle stored proecedures to Postgres functions is to understand the differences between functions and stored procs. So here's my two-cent summary of the differences:
Stored procs are called via the verb exec. Functions are accessed as part of a SELECT statement. That's right, in Postgres a function works pretty much like a view, you trigger it with a SELECT statement.
Functions take zero or more arguments, to provide input from your program to the function. Arguments have a type, which is a normal Postgres column type, but they also have an access type, one of IN, INOUT, or OUT. The meanings of these should be obvious to the most casual observer.
When accessing a function in a SELECT statement, you only specify the IN and INOUT arguments in the SELECT. The INOUT and OUT arguments create the output result set you will get back. (Simple functions that only return a single value can explicitly declare and return a specific value, but I wasn't working on such functions.)
In the SELECT statement you will run, you specify the IN and INOUT arguments to the select as $1, $2, etc. You'll bind your actual data values to these arguments by passing an array of argument descriptors to the PQexecParams call, as shown in the code below.
So without any further ballyhoo, here is my simple function that fetches a customer name from a database and a libpq program that calls this function.
COPY customers (id, firstname, lastname) FROM stdin;
1 John Smith
2 Jay Johnson
3 Jason Crawford
4 Jeph Jacques
\.
Isn't pg_dump a wonderful way to splat the contents of a tiny database into a blog? As you can see, we have a simple database with a numerical id, first, and last name per customer. Our function will retrieve the first and last name for a customer by customer id, after we've loaded the language to be used:
CREATE OR REPLACE FUNCTION getCustomerName
(IN customers.id%TYPE,
OUT customers.firstname%TYPE,
OUT customers.lastname%TYPE)
AS $$
DECLARE
customerId ALIAS FOR $1;
customerFirst ALIAS FOR $2;
customerLast ALIAS FOR $3;
BEGIN
SELECT INTO customerFirst, customerLast
firstname, lastname
FROM customers WHERE id = customerId;
END;
$$ LANGUAGE 'plpgsql';
This is just about the simplest useful function you can create in a database. To simplify the example program to the extreme, we will use text data for input, I'll work on a slightly more comprehensive example with multiple input data types later. Since our output is TEXT only, the output type doesn't really matter. So, here is the C program:
/*
* testsql.c
* Test libpq vs. database functions
* Wes Peters; Wed Jul 21 16:12:32 PDT 2010
*/
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sys/types.h>
#include "libpq-fe.h"
static void
exit_nicely(PGconn *conn)
{
PQfinish(conn);
exit(1);
}
int
main(int argc, char **argv)
{
PGconn *conn;
PGresult *res;
const char *paramValues[1];
char *fptr;
char *lptr;
/*
* Argument is the connection info, if specified.
*/
const char *conninfo = (argc > 1) ? argv[1] : "dbname=wes";
/* Make a connection to the database */
conn = PQconnectdb(conninfo);
if (PQstatus(conn) != CONNECTION_OK)
{
fprintf(stderr, "Connection to database failed: %s",
PQerrorMessage(conn));
exit_nicely(conn);
}
/* Here is our out-of-line parameter value */
paramValues[0] = "2";
res = PQexecParams(conn,
"SELECT * FROM getCustomerName($1)",
1, /* one param */
NULL, /* let the backend deduce param type */
paramValues,
NULL, /* don't need param lengths since text */
NULL, /* default to all text params */
1); /* ask for binary results */
if (PQresultStatus(res) != PGRES_TUPLES_OK)
{
fprintf(stderr, "SELECT failed: %s", PQerrorMessage(conn));
PQclear(res);
exit_nicely(conn);
}
/*
* The output from a function is always row=0 unless it returns a cursor.
*/
fptr = PQgetvalue(res, 0, 0);
lptr = PQgetvalue(res, 0, 1);
printf("Customer 2 is: %s %s\n", fptr, lptr);
/* close the connection to the database and cleanup */
PQclear(res);
PQfinish(conn);
return 0;
}
That's it. Compile as you would any libpq program and test the results (compilation on FreeBSD shown here):
$ cc -g -I/usr/local/include -L/usr/local/lib testsql2.c -lpq
$ ./a.out
Customer 2 is: Jay Johnson
With the above simple rules, you should be able to get data into and out of your first function easily. Stay tuned for a slightly more comprehensive example that uses binary data for input and output.
As is far too often the case, I found lots of hits that weren't very closely related to the topic at hand, and a few that were. The few that were have been repeated hundreds of times, by various sites "syndicating" (i.e. duplicating) other sites posting, and/or numerous email or news group archives, to the point where the signal/noise ratio was vanishingly near zero.
The few posts that were topical had the usual responses of "it's in the doco, read here where here is linked to some 10-year-old PostgreSQL documentation archive that no longer exists, and wouldn't be correct for up to date versions if it did exist. Besides, I searched the doco, and if I didn't find it on the first or second pass, some newbie has no hope of finding it buried in there. So I started hacking.
The world of PostgreSQL functions has changed quite a bit in the 8.x world. In fact, one of the big differences in porting database access code from Oracle stored proecedures to Postgres functions is to understand the differences between functions and stored procs. So here's my two-cent summary of the differences:
Stored procs are called via the verb exec. Functions are accessed as part of a SELECT statement. That's right, in Postgres a function works pretty much like a view, you trigger it with a SELECT statement.
Functions take zero or more arguments, to provide input from your program to the function. Arguments have a type, which is a normal Postgres column type, but they also have an access type, one of IN, INOUT, or OUT. The meanings of these should be obvious to the most casual observer.
When accessing a function in a SELECT statement, you only specify the IN and INOUT arguments in the SELECT. The INOUT and OUT arguments create the output result set you will get back. (Simple functions that only return a single value can explicitly declare and return a specific value, but I wasn't working on such functions.)
In the SELECT statement you will run, you specify the IN and INOUT arguments to the select as $1, $2, etc. You'll bind your actual data values to these arguments by passing an array of argument descriptors to the PQexecParams call, as shown in the code below.
So without any further ballyhoo, here is my simple function that fetches a customer name from a database and a libpq program that calls this function.
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
firstname TEXT,
lastname TEXT
);
id INTEGER PRIMARY KEY,
firstname TEXT,
lastname TEXT
);
1 John Smith
2 Jay Johnson
3 Jason Crawford
4 Jeph Jacques
\.
Isn't pg_dump a wonderful way to splat the contents of a tiny database into a blog? As you can see, we have a simple database with a numerical id, first, and last name per customer. Our function will retrieve the first and last name for a customer by customer id, after we've loaded the language to be used:
CREATE PROCEDURAL LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION getCustomerName
(IN customers.id%TYPE,
OUT customers.firstname%TYPE,
OUT customers.lastname%TYPE)
AS $$
DECLARE
customerId ALIAS FOR $1;
customerFirst ALIAS FOR $2;
customerLast ALIAS FOR $3;
BEGIN
SELECT INTO customerFirst, customerLast
firstname, lastname
FROM customers WHERE id = customerId;
END;
$$ LANGUAGE 'plpgsql';
This is just about the simplest useful function you can create in a database. To simplify the example program to the extreme, we will use text data for input, I'll work on a slightly more comprehensive example with multiple input data types later. Since our output is TEXT only, the output type doesn't really matter. So, here is the C program:
/*
* testsql.c
* Test libpq vs. database functions
* Wes Peters; Wed Jul 21 16:12:32 PDT 2010
*/
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sys/types.h>
#include "libpq-fe.h"
static void
exit_nicely(PGconn *conn)
{
PQfinish(conn);
exit(1);
}
int
main(int argc, char **argv)
{
PGconn *conn;
PGresult *res;
const char *paramValues[1];
char *fptr;
char *lptr;
/*
* Argument is the connection info, if specified.
*/
const char *conninfo = (argc > 1) ? argv[1] : "dbname=wes";
/* Make a connection to the database */
conn = PQconnectdb(conninfo);
if (PQstatus(conn) != CONNECTION_OK)
{
fprintf(stderr, "Connection to database failed: %s",
PQerrorMessage(conn));
exit_nicely(conn);
}
/* Here is our out-of-line parameter value */
paramValues[0] = "2";
res = PQexecParams(conn,
"SELECT * FROM getCustomerName($1)",
1, /* one param */
NULL, /* let the backend deduce param type */
paramValues,
NULL, /* don't need param lengths since text */
NULL, /* default to all text params */
1); /* ask for binary results */
if (PQresultStatus(res) != PGRES_TUPLES_OK)
{
fprintf(stderr, "SELECT failed: %s", PQerrorMessage(conn));
PQclear(res);
exit_nicely(conn);
}
/*
* The output from a function is always row=0 unless it returns a cursor.
*/
fptr = PQgetvalue(res, 0, 0);
lptr = PQgetvalue(res, 0, 1);
printf("Customer 2 is: %s %s\n", fptr, lptr);
/* close the connection to the database and cleanup */
PQclear(res);
PQfinish(conn);
return 0;
}
That's it. Compile as you would any libpq program and test the results (compilation on FreeBSD shown here):
$ cc -g -I/usr/local/include -L/usr/local/lib testsql2.c -lpq
$ ./a.out
Customer 2 is: Jay Johnson
With the above simple rules, you should be able to get data into and out of your first function easily. Stay tuned for a slightly more comprehensive example that uses binary data for input and output.
Comments
Post a Comment