This text was automatically converted from troff me macros to HTML. Information may have been lost, added, or changed in the process. Lars Aronsson and Lysator do not guarantee the correctness of this HTML document.
"SECTION 5 *- LIBPQ"
libpq *- programmer's interface to POSTGRES
LIBPQ is the programmer's interface to POSTGRES. LIBPQ is a set of library routines which allow queries to pass to the POSTGRES back-end and instances to return through an IPC channel.
This version of the documentation is based on the C library.
The following five environment variables can be used to set up default values for an environment and to avoid hard-coding database names into an application program:
PGHOST sets the default server name. PGDATABASE sets the default POSTGRES database name. PGPORT sets the default communication port with the POSTGRES back-end. PGTTY sets the tty on the PQhost back-end on which debugging messages are displayed.
The following internal variables of libpq can be accessed by the programmer:
char *PQhost; /* the server on which POSTGRES
back-end is running. */
char *PQport = NULL; /* The communication port with the
POSTGRES back-end. */
char *PQtty; /* The tty on the PQhost back-end on
which back-end messages are
displayed. */
char *PQoption; /* Optional arguements to the back-end */
char *PQdatabase; /* Back-end database to access */
int PQportset = 0; /* 1 if communication with
back-end is established */
int PQxactid = 0; /* Transaction ID of the current
transaction */
int PQtracep = 0; /* 1 to print out front-end
debugging messages */
int PQAsyncNotifyWaiting = 0; /* 1 if one or more asynchronous
notifications have been
triggered */
The following routines control the execution of queries from a C program.
PQsetdb*- Make the specified database the current database, void PQsetdb ( dbname ) char *dbname; PQsetdb also resets communication via PQreset (see below). PQdb*- Return the current database being accessed. char * PQdb ()
Returns the name of the POSTGRES database being accessed, or NIL if no database is open. Only one database can be accessed at a time. The database name is a string limited to 16 characters.
PQreset*- Reset the communication port with the back-end in case of errors. void PQreset() This function will close the IPC socket connection to the backend thereby causing the next PQexec() call to ask for a new one from the postmaster. When the backend notices the socket was closed it will exit, and when the postmaster is asked for the new connection it will start a new back-end. PQfinish*- Close communication ports with the back-end. void PQfinish ()
Terminates communications and frees up the memory taken up by the libpq buffer.
PQfn*- Send a function call to the POSTGRES backend. char *PQfn(fnid, result_buf, result_len, result_is_int, args, nargs) int fnid; int *result_buf; /* can't use void, the */ int result_len; /* compiler complains */ int result_is_int; PQArgBlock *args; int nargs; PQfn provides access to the POSTGRES fastpath facility, a trapdoor into the system internals. See FASTPATH.PQexec*- Submit a query to POSTGRES. char * PQexec (query) char * query;This function returns a status indicator or an error message. If the query returns data (e.g. fetch), PQexec returns a string consisting of 'P' followed by the name of the portal buffer. When the query does not return instances, PQexec will return a string consisting of 'C' followed by the command tag (e.g. "CREPLACE"). If an error occured during the execution of the query PQexec will return (for historical reasons) an "R".
PORTAL FUNCTIONS
A portal is a POSTGRES buffer from which instances can be fetched. Each portal has a string name (currently limited to 16 bytes). A portal is initialized by submitting a retrieve statement using the PQexec function, for example:
retrieve portal foo ( EMP.all )The programmer can then move data from the portal into LIBPQ by executing a fetch statement, e.g:
fetch 10 in foo fetch all in fooIf no portal name is specified in a query, the default portal name is the string "blank", known as the "blank portal." All qualifying instances in a blank portal are fetched immediately, without the need for the programmer to issue a seperate fetch command.
Data fetched from a portal into LIBPQ is moved into a portal buffer. Portal names are mapped to portal buffers through an internal table. Each instance in a portal buffer has an index number locating its position in the buffer. In addition, each field in an instance has a name and a field number.
A single retrieve command can return multiple types of instances. This can happen if a POSTGRES function is executed in the evaluation of a query or if the query returns multiple instance types from an inheritance hierarchy. Consequently, the instances in a portal are set up in groups. Instances in the same group are guaranteed to have the same instance format.
Portals that are associated with normal user commands are called synchronous. In this case, the application program is expected to issue a retrieval followed by one or more fetch commands. The functions that follow can now be used to manipulate data in the portal.
PQnportals*- Return the number of open portals. int PQnportals ( rule_p ) int rule_p ; If rule_p is not 0, then only return the number of asynchronous portals. PQpnames*- Return all portal names. void PQpnames ( pnames, rule_p) char *pnames [MAXPORTALS]; int rule_p ; If rule_p is not 0, then only return the names of asynchronous portals. PQparray*- Return the portal buffer given a portal name. PortalBuffer * PQparray ( pname ) char *pname; PQclear*- free storage claimed by named portal. void PQclear ( pname ) char *pname; PQntuples*- Return the number of instances in a portal buffer. int PQntuples (portal) PortalBuffer *portal; PQngroups*- Return the number of instance groups in a portal buffer. int PQngroups (portal) PortalBuffer *portal PQntuplesGroup*- Return the number of instances in an instance group. int PQntuplesGroup (portal, group_index) PortalBuffer *portal; int group_index; PQnfieldsGroup*- Return the number of fields in an instance group. int PQnfieldsGroup ( portal, group_index) PortalBuffer *portal; int group_index; PQfnameGroup*- Return the field name given the group and field index. char * PQfnameGroup (portal, group_index, field_number ) PortalBuffer *portal; int group_index; int field_number; PQfnumberGroup*- Return the field number (index) given the group index and field name. int PQfnumberGroup (portal, group_index, field_name) PortalBuffer *portal; int group_index; char *field_name; PQgetgroup*- Returns the index of the group that a particular instance is in. int PQgetgroup ( portal, tuple_index ) PortalBuffer *portal; int tuple_index; PQnfields*- Return the number of fields in an instance. int PQnfields (portal, tuple_index ) PortalBuffer *portal; int tuple_index; PQfnumber*- Return the field index of a given field name within an instance. int PQfnumber ( portal, tuple_index, field_name) PortalBuffer *portal; int tuple_index; char *field_name; PQfname*- Return the name of a field. char * PQfname ( portal, tuple_index, field_number ) PortalBuffer *portal; int tuple_index; int field_number; PQftype*- Return the type of a field. int PQftype ( portal, tuple_index, field_number ) PortalBuffer *portal; int tuple_index; int field_number; The type returned is an internal coding of a type. PQsametype*- Return 1 if two instances have the same attributes. int PQsametype ( portal, tuple_index1, tuple_index2 ) PortalBuffer *portal; int tuple_index1, tuple_index2; PQgetvalue*- Return an attribute (field) value. char * PQgetvalue ( portal, tuple_index, field_number ) PortalBuffer *portal; int tuple_index; int field_number; PQgetlength*- Return the length of an attribute (field) value in bytes. If the field is a varlena, the length of the attribute returned here does not include the longword size field of the varlena, e.g. it is 4 bytes less. char * PQgetlength ( portal, tuple_index, field_number ) PortalBuffer *portal; int tuple_index; int field_number; PQNotifies*- Return the list of relations on which notification has occurred. PQNotifyList *PQNotifies() PQRemoveNotify*- Remove the notification from the list of unhandled notifications. PQNotifyList *PQRemoveNotify(pqNotify) PQNotifyList *pqNotify;If the portal is blank, or specified with the portal keyword, all values are returned as strings. It is the programmer's responsibility to convert them to the correct type. If the portal is specified with the iportal keyword, all values are returned in internal format, namely in the format generated by the input function specified through the definetype command. Again, it is the programmer's responsibility to convert the data to the correct type.
ASYNCHRONOUS PORTALS/NOTIFICATION
Asynchronous portals, query results of rules, are implemented using two mechanisms: relations and notification. The query result is transferred through a relation. The notification is done with special postquel commands and frontend/backend protocol. Referring to the second sample program, after executing "listen relation_name" in the frontend process, periodically check PQAsyncNotifyWaiting. If it is non-zero, then the "notify relation_name" command has been executed by some backend. Immediately clear PQAsyncNotifyWaiting, then do a NULL query, i.e. PQexec(" "), to retrieve the actual notification data. Then call PQNotifies() to get the list of relations on which notification has occurred. After handling the notification, do PQRemoveNotify on each element of the list that has been handled to prevent further handling by you.
FUNCTIONS ASSOCIATED WITH THE COPY COMMAND
The copy command in POSTGRES has options to read from or write to the network connection used by LIBPQ. Therefore, functions are necessary to access this network connection directly so applications may take full advantage of this capability.
For more information about the copy command, see copy(commands).
PQgetline(string, length)*- Reads a null-terminated line into string. char *string; int length PQputline(string)*- Sends a null-terminated string. char *string; int PQendcopy()*- Syncs with the back-end. This function waits until the backend has finished processing the copy. It should either be issued when the last string has been sent to the backend using PQputline() or when the last string has been received from the backend using PGgetline(). It must be issued or the backend may get "out of sync" with the frontend. Upon return from this function, the backend is ready to receive the next query. The return value is 0 on successful completion, nonzero otherwise. For Example: PQexec("create foo (a=int4, b=char16, d=float8)"); PQexec("copy foo from stdin"); PQputline("3<TAB>hello world<TAB>4.5n"); PQputline("4<TAB>goodbye world<TAB>7.11"); PQputline(".\n"); PQendcopy();TRACING FUNCTIONS
PQtrace*- Enable tracing. void PQtrace () The routine sets the PQtracep variable to 1 which causes debug messages to be printed. You should note that the messages will be printed to stdout by default. If you would like different behavior you must set the variable FILE *debug_port to the appropriate stream. PQuntrace*- Disable tracing. void PQuntrace ()BUGS
The query buffer is only 8192 bytes long, and queries over that length will be silently truncated.
SAMPLE PROGRAM
/* * testlibpq.c*- * Test the C version of Libpq, the POSTGRES frontend library. */ #include <stdio.h> #include "libpq.h" main () { int i, j, k, g, n, m, t; PortalBuffer *p; char pnames[MAXPORTALS][portal_name_length]; /* Specify the database to access. */ PQsetdb ("pic_demo"); /* Start a transaction block for eportal */ PQexec ("begin"); /* Fetch instances from the EMP class. */ PQexec ("retrieve portal eportal (EMP.all)"); PQexec ("fetch all in eportal"); /* Examine all the instances fetched. */ p = PQparray ("eportal"); g = PQngroups (p); t = 0; for (k = 0; k < g; k++) { printf ("\nA new instance group:\n"); n = PQntuplesGroup (p, k); m = PQnfieldsGroup (p, k); /* Print out the attribute names. */ for (i = 0; i < m; i++) printf ("%-15s", PQfnameGroup (p, k, i)); printf ("\n"); /* Print out the instances. */ for (i = 0; i < n; i++) { for (j = 0; j < m; j++) printf("%-15s", PQgetvalue(p, t+i, j)); printf ("\n"); } t += n; } /* Close the portal. */ PQexec ("close eportal"); /* End the transaction block */ PQexec("end"); /* Try out some other functions. */ /* Print out the number of portals. */ printf ("\nNumber of portals open: %d.\n", PQnportals ()); /* If any tuples are returned by rules, print out * the portal name. */ if (PQnportals (1)) { printf ("Tuples are returned by rules. \n"); PQpnames (pnames, 1); for (i = 0; i < MAXPORTALS; i++) if (pnames[i] != NULL) printf ("portal used by rules: %s\n", pnames[i]); } /* finish execution. */ PQfinish (); }SAMPLE PROGRAM 2
/* * Testing of asynchronous portal interface. * * Do the following at the monitor: * create test1 (i = int4) \g create test1a (i = int4) \g define rule r1 is on append to test1 do [append test1a (i = new.i) notify test1a] \g * Then start up this process. append test1 (i = 10) \g * The value i=10 should be printed by this process. */ #include "tmp/simplelists.h" #include "tmp/libpq.h" void main() { extern int PQAsyncNotifyWaiting; PQNotifyList *l; PortalBuffer *portalbuf; char *res; int ngroups,tupno, grpno, ntups, nflds; PQsetdb(getenv("USER")); PQexec("listen test1a"); while(1) { sleep(1); if (PQAsyncNotifyWaiting) { PQAsyncNotifyWaiting = 0; PQexec(" "); l = PQnotifies(); if (l != NULL) { printf("notification on relation %s\n", l->relname); res = PQexec("retrieve (test1a.i)"); if (*res == 'E') { fprintf(stderr,"%s\nfailed",++res); goto exit_error; } if (*res != 'P') { fprintf(stderr,"%s\nno portal",++res); } /* get tuples in relation */ portalbuf = PQparray(++res); ngroups = PQngroups(portalbuf); for (grpno = 0; grpno < ngroups; grpno++) { ntups = PQntuplesGroup(portalbuf,grpno); nflds = PQnfieldsGroup(portalbuf,grpno); if (nflds != 1) { fprintf(stderr, "expected 1 attributes, got %d\n", nflds); goto exit_error; } for (tupno = 0; tupno < ntups; tupno++) { printf("got i=%s\n", PQgetvalue(portalbuf,tupno,0)); } } break; } } } PQfinish(); exit(0); exit_error: PQfinish(); exit(1); }SAMPLE PROGRAM 3
/* * Testing of new binary portal interface. * * Do the following at the monitor: * create test1 (i = int4,d = float4,p = polygon) \g append test1 (i = 7, d=3.567,p="(1.0,2.0,3.0,4.0)"::polygon) \g -- Anything else you can think of. * Start up this program. * The correct contents of test1 should be printed */ #include "tmp/simplelists.h" #include "tmp/libpq.h" #include "utils/geo-decls.h" void main() { extern int PQAsyncNotifyWaiting; PQNotifyList *l; PortalBuffer *portalbuf; char *res; int ngroups,tupno, grpno, ntups, nflds; PQsetdb(getenv("USER")); PQexec("begin"); res = (char *)PQexec("retrieve iportal junk (test1.all)"); if (*res == 'E') { fprintf(stderr,"%s\nfailed",++res); goto exit_error; } res = (char *)PQexec("fetch all in junk"); if (*res != 'P') { fprintf(stderr,"\nno portal"); goto exit_error; } /* get tuples in relation */ portalbuf = PQparray(++res); ngroups = PQngroups(portalbuf); for (grpno = 0; grpno < ngroups; grpno++) { ntups = PQntuplesGroup(portalbuf, grpno); if ((nflds = PQnfieldsGroup(portalbuf, grpno)) != 3) { fprintf(stderr, "expected 3 attributes, got %d\n", nflds); goto exit_error; } for (tupno = 0; tupno < ntups; tupno++) { int *bla1; char *bla2; POLYGON *bla3; bla1 = (int *)PQgetvalue(portalbuf,tupno,0); bla2 = PQgetvalue(portalbuf,tupno,1); bla3 = PQgetvalue(portalbuf,tupno,2)-4; printf ("got i=%d(%d bytes), d=(%f)(%d bytes)|%x|%x|%x|%x\n\ Polygon(%d bytes)\ %d points (%f,%f,%f,%f)\n", *bla1,PQgetlength(portalbuf,tupno,0), *((float *)bla2), PQgetlength(portalbuf,tupno,1), *bla2,*(bla2+1),*(bla2+2),*(bla2+3), PQgetlength(portalbuf,tupno,2), bla3->npts, bla3->boundbox.xh,bla3->boundbox.yh, bla3->boundbox.xl,bla3->boundbox.yl); } } PQexec("end"); PQfinish(); exit(0); exit_error: PQexec("end"); PQfinish(); exit(1); }