2 32.3. Command Execution Functions #
5 32.3.2. Retrieving Query Result Information
6 32.3.3. Retrieving Other Result Information
7 32.3.4. Escaping Strings for Inclusion in SQL Commands
9 Once a connection to a database server has been successfully
10 established, the functions described here are used to perform SQL
13 32.3.1. Main Functions #
16 Submits a command to the server and waits for the result.
18 PGresult *PQexec(PGconn *conn, const char *command);
20 Returns a PGresult pointer or possibly a null pointer. A
21 non-null pointer will generally be returned except in
22 out-of-memory conditions or serious errors such as inability to
23 send the command to the server. The PQresultStatus function
24 should be called to check the return value for any errors
25 (including the value of a null pointer, in which case it will
26 return PGRES_FATAL_ERROR). Use PQerrorMessage to get more
27 information about such errors.
29 The command string can include multiple SQL commands (separated by
30 semicolons). Multiple queries sent in a single PQexec call are
31 processed in a single transaction, unless there are explicit
32 BEGIN/COMMIT commands included in the query string to divide it into
33 multiple transactions. (See Section 54.2.2.1 for more details about how
34 the server handles multi-query strings.) Note however that the returned
35 PGresult structure describes only the result of the last command
36 executed from the string. Should one of the commands fail, processing
37 of the string stops with it and the returned PGresult describes the
41 Submits a command to the server and waits for the result, with
42 the ability to pass parameters separately from the SQL command
45 PGresult *PQexecParams(PGconn *conn,
48 const Oid *paramTypes,
49 const char * const *paramValues,
50 const int *paramLengths,
51 const int *paramFormats,
54 PQexecParams is like PQexec, but offers additional
55 functionality: parameter values can be specified separately from
56 the command string proper, and query results can be requested in
57 either text or binary format.
59 The function arguments are:
62 The connection object to send the command through.
65 The SQL command string to be executed. If parameters are
66 used, they are referred to in the command string as $1,
70 The number of parameters supplied; it is the length of the
71 arrays paramTypes[], paramValues[], paramLengths[], and
72 paramFormats[]. (The array pointers can be NULL when
76 Specifies, by OID, the data types to be assigned to the
77 parameter symbols. If paramTypes is NULL, or any
78 particular element in the array is zero, the server infers
79 a data type for the parameter symbol in the same way it
80 would do for an untyped literal string.
83 Specifies the actual values of the parameters. A null
84 pointer in this array means the corresponding parameter is
85 null; otherwise the pointer points to a zero-terminated
86 text string (for text format) or binary data in the format
87 expected by the server (for binary format).
90 Specifies the actual data lengths of binary-format
91 parameters. It is ignored for null parameters and
92 text-format parameters. The array pointer can be null when
93 there are no binary parameters.
96 Specifies whether parameters are text (put a zero in the
97 array entry for the corresponding parameter) or binary
98 (put a one in the array entry for the corresponding
99 parameter). If the array pointer is null then all
100 parameters are presumed to be text strings.
102 Values passed in binary format require knowledge of the
103 internal representation expected by the backend. For
104 example, integers must be passed in network byte order.
105 Passing numeric values requires knowledge of the server
106 storage format, as implemented in
107 src/backend/utils/adt/numeric.c::numeric_send() and
108 src/backend/utils/adt/numeric.c::numeric_recv().
111 Specify zero to obtain results in text format, or one to
112 obtain results in binary format. (There is not currently a
113 provision to obtain different result columns in different
114 formats, although that is possible in the underlying
117 The primary advantage of PQexecParams over PQexec is that parameter
118 values can be separated from the command string, thus avoiding the need
119 for tedious and error-prone quoting and escaping.
121 Unlike PQexec, PQexecParams allows at most one SQL command in the given
122 string. (There can be semicolons in it, but not more than one nonempty
123 command.) This is a limitation of the underlying protocol, but has some
124 usefulness as an extra defense against SQL-injection attacks.
128 Specifying parameter types via OIDs is tedious, particularly if you
129 prefer not to hard-wire particular OID values into your program.
130 However, you can avoid doing so even in cases where the server by
131 itself cannot determine the type of the parameter, or chooses a
132 different type than you want. In the SQL command text, attach an
133 explicit cast to the parameter symbol to show what data type you will
135 SELECT * FROM mytable WHERE x = $1::bigint;
137 This forces parameter $1 to be treated as bigint, whereas by default it
138 would be assigned the same type as x. Forcing the parameter type
139 decision, either this way or by specifying a numeric type OID, is
140 strongly recommended when sending parameter values in binary format,
141 because binary format has less redundancy than text format and so there
142 is less chance that the server will detect a type mismatch mistake for
146 Submits a request to create a prepared statement with the given
147 parameters, and waits for completion.
149 PGresult *PQprepare(PGconn *conn,
150 const char *stmtName,
153 const Oid *paramTypes);
155 PQprepare creates a prepared statement for later execution with
156 PQexecPrepared. This feature allows commands to be executed
157 repeatedly without being parsed and planned each time; see
160 The function creates a prepared statement named stmtName from
161 the query string, which must contain a single SQL command.
162 stmtName can be "" to create an unnamed statement, in which case
163 any pre-existing unnamed statement is automatically replaced;
164 otherwise it is an error if the statement name is already
165 defined in the current session. If any parameters are used, they
166 are referred to in the query as $1, $2, etc. nParams is the
167 number of parameters for which types are pre-specified in the
168 array paramTypes[]. (The array pointer can be NULL when nParams
169 is zero.) paramTypes[] specifies, by OID, the data types to be
170 assigned to the parameter symbols. If paramTypes is NULL, or any
171 particular element in the array is zero, the server assigns a
172 data type to the parameter symbol in the same way it would do
173 for an untyped literal string. Also, the query can use parameter
174 symbols with numbers higher than nParams; data types will be
175 inferred for these symbols as well. (See PQdescribePrepared for
176 a means to find out what data types were inferred.)
178 As with PQexec, the result is normally a PGresult object whose
179 contents indicate server-side success or failure. A null result
180 indicates out-of-memory or inability to send the command at all.
181 Use PQerrorMessage to get more information about such errors.
183 Prepared statements for use with PQexecPrepared can also be created by
184 executing SQL PREPARE statements.
187 Sends a request to execute a prepared statement with given
188 parameters, and waits for the result.
190 PGresult *PQexecPrepared(PGconn *conn,
191 const char *stmtName,
193 const char * const *paramValues,
194 const int *paramLengths,
195 const int *paramFormats,
198 PQexecPrepared is like PQexecParams, but the command to be
199 executed is specified by naming a previously-prepared statement,
200 instead of giving a query string. This feature allows commands
201 that will be used repeatedly to be parsed and planned just once,
202 rather than each time they are executed. The statement must have
203 been prepared previously in the current session.
205 The parameters are identical to PQexecParams, except that the
206 name of a prepared statement is given instead of a query string,
207 and the paramTypes[] parameter is not present (it is not needed
208 since the prepared statement's parameter types were determined
209 when it was created).
212 Submits a request to obtain information about the specified
213 prepared statement, and waits for completion.
215 PGresult *PQdescribePrepared(PGconn *conn, const char *stmtName);
217 PQdescribePrepared allows an application to obtain information
218 about a previously prepared statement.
220 stmtName can be "" or NULL to reference the unnamed statement,
221 otherwise it must be the name of an existing prepared statement.
222 On success, a PGresult with status PGRES_COMMAND_OK is returned.
223 The functions PQnparams and PQparamtype can be applied to this
224 PGresult to obtain information about the parameters of the
225 prepared statement, and the functions PQnfields, PQfname,
226 PQftype, etc. provide information about the result columns (if
227 any) of the statement.
230 Submits a request to obtain information about the specified
231 portal, and waits for completion.
233 PGresult *PQdescribePortal(PGconn *conn, const char *portalName);
235 PQdescribePortal allows an application to obtain information
236 about a previously created portal. (libpq does not provide any
237 direct access to portals, but you can use this function to
238 inspect the properties of a cursor created with a DECLARE CURSOR
241 portalName can be "" or NULL to reference the unnamed portal,
242 otherwise it must be the name of an existing portal. On success,
243 a PGresult with status PGRES_COMMAND_OK is returned. The
244 functions PQnfields, PQfname, PQftype, etc. can be applied to
245 the PGresult to obtain information about the result columns (if
249 Submits a request to close the specified prepared statement, and
250 waits for completion.
252 PGresult *PQclosePrepared(PGconn *conn, const char *stmtName);
254 PQclosePrepared allows an application to close a previously
255 prepared statement. Closing a statement releases all of its
256 associated resources on the server and allows its name to be
259 stmtName can be "" or NULL to reference the unnamed statement.
260 It is fine if no statement exists with this name, in that case
261 the operation is a no-op. On success, a PGresult with status
262 PGRES_COMMAND_OK is returned.
265 Submits a request to close the specified portal, and waits for
268 PGresult *PQclosePortal(PGconn *conn, const char *portalName);
270 PQclosePortal allows an application to trigger a close of a
271 previously created portal. Closing a portal releases all of its
272 associated resources on the server and allows its name to be
273 reused. (libpq does not provide any direct access to portals,
274 but you can use this function to close a cursor created with a
275 DECLARE CURSOR SQL command.)
277 portalName can be "" or NULL to reference the unnamed portal. It
278 is fine if no portal exists with this name, in that case the
279 operation is a no-op. On success, a PGresult with status
280 PGRES_COMMAND_OK is returned.
282 The PGresult structure encapsulates the result returned by the server.
283 libpq application programmers should be careful to maintain the
284 PGresult abstraction. Use the accessor functions below to get at the
285 contents of PGresult. Avoid directly referencing the fields of the
286 PGresult structure because they are subject to change in the future.
289 Returns the result status of the command.
291 ExecStatusType PQresultStatus(const PGresult *res);
293 PQresultStatus can return one of the following values:
296 The string sent to the server was empty.
299 Successful completion of a command returning no data.
302 Successful completion of a command returning data (such as
306 Copy Out (from server) data transfer started.
309 Copy In (to server) data transfer started.
312 The server's response was not understood.
314 PGRES_NONFATAL_ERROR #
315 A nonfatal error (a notice or warning) occurred.
318 A fatal error occurred.
321 Copy In/Out (to and from server) data transfer started.
322 This feature is currently used only for streaming
323 replication, so this status should not occur in ordinary
327 The PGresult contains a single result tuple from the
328 current command. This status occurs only when single-row
329 mode has been selected for the query (see Section 32.6).
332 The PGresult contains several result tuples from the
333 current command. This status occurs only when chunked mode
334 has been selected for the query (see Section 32.6). The
335 number of tuples will not exceed the limit passed to
336 PQsetChunkedRowsMode.
338 PGRES_PIPELINE_SYNC #
339 The PGresult represents a synchronization point in
340 pipeline mode, requested by either PQpipelineSync or
341 PQsendPipelineSync. This status occurs only when pipeline
342 mode has been selected.
344 PGRES_PIPELINE_ABORTED #
345 The PGresult represents a pipeline that has received an
346 error from the server. PQgetResult must be called
347 repeatedly, and each time it will return this status code
348 until the end of the current pipeline, at which point it
349 will return PGRES_PIPELINE_SYNC and normal processing can
352 If the result status is PGRES_TUPLES_OK, PGRES_SINGLE_TUPLE, or
353 PGRES_TUPLES_CHUNK, then the functions described below can be
354 used to retrieve the rows returned by the query. Note that a
355 SELECT command that happens to retrieve zero rows still shows
356 PGRES_TUPLES_OK. PGRES_COMMAND_OK is for commands that can never
357 return rows (INSERT or UPDATE without a RETURNING clause, etc.).
358 A response of PGRES_EMPTY_QUERY might indicate a bug in the
361 A result of status PGRES_NONFATAL_ERROR will never be returned
362 directly by PQexec or other query execution functions; results
363 of this kind are instead passed to the notice processor (see
367 Converts the enumerated type returned by PQresultStatus into a
368 string constant describing the status code. The caller should
371 char *PQresStatus(ExecStatusType status);
373 PQresultErrorMessage #
374 Returns the error message associated with the command, or an
375 empty string if there was no error.
377 char *PQresultErrorMessage(const PGresult *res);
379 If there was an error, the returned string will include a
380 trailing newline. The caller should not free the result
381 directly. It will be freed when the associated PGresult handle
382 is passed to PQclear.
384 Immediately following a PQexec or PQgetResult call,
385 PQerrorMessage (on the connection) will return the same string
386 as PQresultErrorMessage (on the result). However, a PGresult
387 will retain its error message until destroyed, whereas the
388 connection's error message will change when subsequent
389 operations are done. Use PQresultErrorMessage when you want to
390 know the status associated with a particular PGresult; use
391 PQerrorMessage when you want to know the status from the latest
392 operation on the connection.
394 PQresultVerboseErrorMessage #
395 Returns a reformatted version of the error message associated
396 with a PGresult object.
398 char *PQresultVerboseErrorMessage(const PGresult *res,
399 PGVerbosity verbosity,
400 PGContextVisibility show_context);
402 In some situations a client might wish to obtain a more detailed
403 version of a previously-reported error.
404 PQresultVerboseErrorMessage addresses this need by computing the
405 message that would have been produced by PQresultErrorMessage if
406 the specified verbosity settings had been in effect for the
407 connection when the given PGresult was generated. If the
408 PGresult is not an error result, “PGresult is not an error
409 result” is reported instead. The returned string includes a
412 Unlike most other functions for extracting data from a PGresult,
413 the result of this function is a freshly allocated string. The
414 caller must free it using PQfreemem() when the string is no
417 A NULL return is possible if there is insufficient memory.
420 Returns an individual field of an error report.
422 char *PQresultErrorField(const PGresult *res, int fieldcode);
424 fieldcode is an error field identifier; see the symbols listed
425 below. NULL is returned if the PGresult is not an error or
426 warning result, or does not include the specified field. Field
427 values will normally not include a trailing newline. The caller
428 should not free the result directly. It will be freed when the
429 associated PGresult handle is passed to PQclear.
431 The following field codes are available:
434 The severity; the field contents are ERROR, FATAL, or
435 PANIC (in an error message), or WARNING, NOTICE, DEBUG,
436 INFO, or LOG (in a notice message), or a localized
437 translation of one of these. Always present.
439 PG_DIAG_SEVERITY_NONLOCALIZED #
440 The severity; the field contents are ERROR, FATAL, or
441 PANIC (in an error message), or WARNING, NOTICE, DEBUG,
442 INFO, or LOG (in a notice message). This is identical to
443 the PG_DIAG_SEVERITY field except that the contents are
444 never localized. This is present only in reports generated
445 by PostgreSQL versions 9.6 and later.
448 The SQLSTATE code for the error. The SQLSTATE code
449 identifies the type of error that has occurred; it can be
450 used by front-end applications to perform specific
451 operations (such as error handling) in response to a
452 particular database error. For a list of the possible
453 SQLSTATE codes, see Appendix A. This field is not
454 localizable, and is always present.
456 PG_DIAG_MESSAGE_PRIMARY #
457 The primary human-readable error message (typically one
458 line). Always present.
460 PG_DIAG_MESSAGE_DETAIL #
461 Detail: an optional secondary error message carrying more
462 detail about the problem. Might run to multiple lines.
464 PG_DIAG_MESSAGE_HINT #
465 Hint: an optional suggestion what to do about the problem.
466 This is intended to differ from detail in that it offers
467 advice (potentially inappropriate) rather than hard facts.
468 Might run to multiple lines.
470 PG_DIAG_STATEMENT_POSITION #
471 A string containing a decimal integer indicating an error
472 cursor position as an index into the original statement
473 string. The first character has index 1, and positions are
474 measured in characters not bytes.
476 PG_DIAG_INTERNAL_POSITION #
477 This is defined the same as the PG_DIAG_STATEMENT_POSITION
478 field, but it is used when the cursor position refers to
479 an internally generated command rather than the one
480 submitted by the client. The PG_DIAG_INTERNAL_QUERY field
481 will always appear when this field appears.
483 PG_DIAG_INTERNAL_QUERY #
484 The text of a failed internally-generated command. This
485 could be, for example, an SQL query issued by a PL/pgSQL
489 An indication of the context in which the error occurred.
490 Presently this includes a call stack traceback of active
491 procedural language functions and internally-generated
492 queries. The trace is one entry per line, most recent
495 PG_DIAG_SCHEMA_NAME #
496 If the error was associated with a specific database
497 object, the name of the schema containing that object, if
501 If the error was associated with a specific table, the
502 name of the table. (Refer to the schema name field for the
503 name of the table's schema.)
505 PG_DIAG_COLUMN_NAME #
506 If the error was associated with a specific table column,
507 the name of the column. (Refer to the schema and table
508 name fields to identify the table.)
510 PG_DIAG_DATATYPE_NAME #
511 If the error was associated with a specific data type, the
512 name of the data type. (Refer to the schema name field for
513 the name of the data type's schema.)
515 PG_DIAG_CONSTRAINT_NAME #
516 If the error was associated with a specific constraint,
517 the name of the constraint. Refer to fields listed above
518 for the associated table or domain. (For this purpose,
519 indexes are treated as constraints, even if they weren't
520 created with constraint syntax.)
522 PG_DIAG_SOURCE_FILE #
523 The file name of the source-code location where the error
526 PG_DIAG_SOURCE_LINE #
527 The line number of the source-code location where the
530 PG_DIAG_SOURCE_FUNCTION #
531 The name of the source-code function reporting the error.
535 The fields for schema name, table name, column name, data type
536 name, and constraint name are supplied only for a limited number
537 of error types; see Appendix A. Do not assume that the presence
538 of any of these fields guarantees the presence of another field.
539 Core error sources observe the interrelationships noted above,
540 but user-defined functions may use these fields in other ways.
541 In the same vein, do not assume that these fields denote
542 contemporary objects in the current database.
544 The client is responsible for formatting displayed information
545 to meet its needs; in particular it should break long lines as
546 needed. Newline characters appearing in the error message fields
547 should be treated as paragraph breaks, not line breaks.
549 Errors generated internally by libpq will have severity and
550 primary message, but typically no other fields.
552 Note that error fields are only available from PGresult objects,
553 not PGconn objects; there is no PQerrorField function.
556 Frees the storage associated with a PGresult. Every command
557 result should be freed via PQclear when it is no longer needed.
559 void PQclear(PGresult *res);
561 If the argument is a NULL pointer, no operation is performed.
563 You can keep a PGresult object around for as long as you need
564 it; it does not go away when you issue a new command, nor even
565 if you close the connection. To get rid of it, you must call
566 PQclear. Failure to do this will result in memory leaks in your
569 32.3.2. Retrieving Query Result Information #
571 These functions are used to extract information from a PGresult object
572 that represents a successful query result (that is, one that has status
573 PGRES_TUPLES_OK, PGRES_SINGLE_TUPLE, or PGRES_TUPLES_CHUNK). They can
574 also be used to extract information from a successful Describe
575 operation: a Describe's result has all the same column information that
576 actual execution of the query would provide, but it has zero rows. For
577 objects with other status values, these functions will act as though
578 the result has zero rows and zero columns.
581 Returns the number of rows (tuples) in the query result. (Note
582 that PGresult objects are limited to no more than INT_MAX rows,
583 so an int result is sufficient.)
585 int PQntuples(const PGresult *res);
588 Returns the number of columns (fields) in each row of the query
591 int PQnfields(const PGresult *res);
594 Returns the column name associated with the given column number.
595 Column numbers start at 0. The caller should not free the result
596 directly. It will be freed when the associated PGresult handle
597 is passed to PQclear.
599 char *PQfname(const PGresult *res,
602 NULL is returned if the column number is out of range.
605 Returns the column number associated with the given column name.
607 int PQfnumber(const PGresult *res,
608 const char *column_name);
610 -1 is returned if the given name does not match any column.
612 The given name is treated like an identifier in an SQL command,
613 that is, it is downcased unless double-quoted. For example,
614 given a query result generated from the SQL command:
616 SELECT 1 AS FOO, 2 AS "BAR";
618 we would have the results:
622 PQfnumber(res, "FOO") 0
623 PQfnumber(res, "foo") 0
624 PQfnumber(res, "BAR") -1
625 PQfnumber(res, "\"BAR\"") 1
628 Returns the OID of the table from which the given column was
629 fetched. Column numbers start at 0.
631 Oid PQftable(const PGresult *res,
634 InvalidOid is returned if the column number is out of range, or
635 if the specified column is not a simple reference to a table
636 column. You can query the system table pg_class to determine
637 exactly which table is referenced.
639 The type Oid and the constant InvalidOid will be defined when
640 you include the libpq header file. They will both be some
644 Returns the column number (within its table) of the column
645 making up the specified query result column. Query-result column
646 numbers start at 0, but table columns have nonzero numbers.
648 int PQftablecol(const PGresult *res,
651 Zero is returned if the column number is out of range, or if the
652 specified column is not a simple reference to a table column.
655 Returns the format code indicating the format of the given
656 column. Column numbers start at 0.
658 int PQfformat(const PGresult *res,
661 Format code zero indicates textual data representation, while
662 format code one indicates binary representation. (Other codes
663 are reserved for future definition.)
666 Returns the data type associated with the given column number.
667 The integer returned is the internal OID number of the type.
668 Column numbers start at 0.
670 Oid PQftype(const PGresult *res,
673 You can query the system table pg_type to obtain the names and
674 properties of the various data types. The OIDs of the built-in
675 data types are defined in the file catalog/pg_type_d.h in the
676 PostgreSQL installation's include directory.
679 Returns the type modifier of the column associated with the
680 given column number. Column numbers start at 0.
682 int PQfmod(const PGresult *res,
685 The interpretation of modifier values is type-specific; they
686 typically indicate precision or size limits. The value -1 is
687 used to indicate “no information available”. Most data types do
688 not use modifiers, in which case the value is always -1.
691 Returns the size in bytes of the column associated with the
692 given column number. Column numbers start at 0.
694 int PQfsize(const PGresult *res,
697 PQfsize returns the space allocated for this column in a
698 database row, in other words the size of the server's internal
699 representation of the data type. (Accordingly, it is not really
700 very useful to clients.) A negative value indicates the data
701 type is variable-length.
704 Returns 1 if the PGresult contains binary data and 0 if it
707 int PQbinaryTuples(const PGresult *res);
709 This function is deprecated (except for its use in connection
710 with COPY), because it is possible for a single PGresult to
711 contain text data in some columns and binary data in others.
712 PQfformat is preferred. PQbinaryTuples returns 1 only if all
713 columns of the result are binary (format 1).
716 Returns a single field value of one row of a PGresult. Row and
717 column numbers start at 0. The caller should not free the result
718 directly. It will be freed when the associated PGresult handle
719 is passed to PQclear.
721 char *PQgetvalue(const PGresult *res,
725 For data in text format, the value returned by PQgetvalue is a
726 null-terminated character string representation of the field
727 value. For data in binary format, the value is in the binary
728 representation determined by the data type's typsend and
729 typreceive functions. (The value is actually followed by a zero
730 byte in this case too, but that is not ordinarily useful, since
731 the value is likely to contain embedded nulls.)
733 An empty string is returned if the field value is null. See
734 PQgetisnull to distinguish null values from empty-string values.
736 The pointer returned by PQgetvalue points to storage that is
737 part of the PGresult structure. One should not modify the data
738 it points to, and one must explicitly copy the data into other
739 storage if it is to be used past the lifetime of the PGresult
743 Tests a field for a null value. Row and column numbers start at
746 int PQgetisnull(const PGresult *res,
750 This function returns 1 if the field is null and 0 if it
751 contains a non-null value. (Note that PQgetvalue will return an
752 empty string, not a null pointer, for a null field.)
755 Returns the actual length of a field value in bytes. Row and
756 column numbers start at 0.
758 int PQgetlength(const PGresult *res,
762 This is the actual data length for the particular data value,
763 that is, the size of the object pointed to by PQgetvalue. For
764 text data format this is the same as strlen(). For binary format
765 this is essential information. Note that one should not rely on
766 PQfsize to obtain the actual data length.
769 Returns the number of parameters of a prepared statement.
771 int PQnparams(const PGresult *res);
773 This function is only useful when inspecting the result of
774 PQdescribePrepared. For other types of results it will return
778 Returns the data type of the indicated statement parameter.
779 Parameter numbers start at 0.
781 Oid PQparamtype(const PGresult *res, int param_number);
783 This function is only useful when inspecting the result of
784 PQdescribePrepared. For other types of results it will return
788 Prints out all the rows and, optionally, the column names to the
789 specified output stream.
791 void PQprint(FILE *fout, /* output stream */
793 const PQprintOpt *po);
796 pqbool header; /* print output field headings and row count */
797 pqbool align; /* fill align the fields */
798 pqbool standard; /* old brain dead format */
799 pqbool html3; /* output HTML tables */
800 pqbool expanded; /* expand tables */
801 pqbool pager; /* use pager for output if needed */
802 char *fieldSep; /* field separator */
803 char *tableOpt; /* attributes for HTML table element */
804 char *caption; /* HTML table caption */
805 char **fieldName; /* null-terminated array of replacement field names */
808 This function was formerly used by psql to print query results,
809 but this is no longer the case. Note that it assumes all the
810 data is in text format.
812 32.3.3. Retrieving Other Result Information #
814 These functions are used to extract other information from PGresult
818 Returns the command status tag from the SQL command that
819 generated the PGresult.
821 char *PQcmdStatus(PGresult *res);
823 Commonly this is just the name of the command, but it might
824 include additional data such as the number of rows processed.
825 The caller should not free the result directly. It will be freed
826 when the associated PGresult handle is passed to PQclear.
829 Returns the number of rows affected by the SQL command.
831 char *PQcmdTuples(PGresult *res);
833 This function returns a string containing the number of rows
834 affected by the SQL statement that generated the PGresult. This
835 function can only be used following the execution of a SELECT,
836 CREATE TABLE AS, INSERT, UPDATE, DELETE, MERGE, MOVE, FETCH, or
837 COPY statement, or an EXECUTE of a prepared query that contains
838 an INSERT, UPDATE, DELETE, or MERGE statement. If the command
839 that generated the PGresult was anything else, PQcmdTuples
840 returns an empty string. The caller should not free the return
841 value directly. It will be freed when the associated PGresult
842 handle is passed to PQclear.
845 Returns the OID of the inserted row, if the SQL command was an
846 INSERT that inserted exactly one row into a table that has OIDs,
847 or a EXECUTE of a prepared query containing a suitable INSERT
848 statement. Otherwise, this function returns InvalidOid. This
849 function will also return InvalidOid if the table affected by
850 the INSERT statement does not contain OIDs.
852 Oid PQoidValue(const PGresult *res);
855 This function is deprecated in favor of PQoidValue and is not
856 thread-safe. It returns a string with the OID of the inserted
857 row, while PQoidValue returns the OID value.
859 char *PQoidStatus(const PGresult *res);
861 32.3.4. Escaping Strings for Inclusion in SQL Commands #
865 char *PQescapeLiteral(PGconn *conn, const char *str, size_t length);
867 PQescapeLiteral escapes a string for use within an SQL command.
868 This is useful when inserting data values as literal constants
869 in SQL commands. Certain characters (such as quotes and
870 backslashes) must be escaped to prevent them from being
871 interpreted specially by the SQL parser. PQescapeLiteral
872 performs this operation.
874 PQescapeLiteral returns an escaped version of the str parameter
875 in memory allocated with malloc(). This memory should be freed
876 using PQfreemem() when the result is no longer needed. A
877 terminating zero byte is not required, and should not be counted
878 in length. (If a terminating zero byte is found before length
879 bytes are processed, PQescapeLiteral stops at the zero; the
880 behavior is thus rather like strncpy.) The return string has all
881 special characters replaced so that they can be properly
882 processed by the PostgreSQL string literal parser. A terminating
883 zero byte is also added. The single quotes that must surround
884 PostgreSQL string literals are included in the result string.
886 On error, PQescapeLiteral returns NULL and a suitable message is
887 stored in the conn object.
891 It is especially important to do proper escaping when handling
892 strings that were received from an untrustworthy source.
893 Otherwise there is a security risk: you are vulnerable to “SQL
894 injection” attacks wherein unwanted SQL commands are fed to your
897 Note that it is neither necessary nor correct to do escaping
898 when a data value is passed as a separate parameter in
899 PQexecParams or its sibling routines.
903 char *PQescapeIdentifier(PGconn *conn, const char *str, size_t length);
905 PQescapeIdentifier escapes a string for use as an SQL
906 identifier, such as a table, column, or function name. This is
907 useful when a user-supplied identifier might contain special
908 characters that would otherwise not be interpreted as part of
909 the identifier by the SQL parser, or when the identifier might
910 contain upper case characters whose case should be preserved.
912 PQescapeIdentifier returns a version of the str parameter
913 escaped as an SQL identifier in memory allocated with malloc().
914 This memory must be freed using PQfreemem() when the result is
915 no longer needed. A terminating zero byte is not required, and
916 should not be counted in length. (If a terminating zero byte is
917 found before length bytes are processed, PQescapeIdentifier
918 stops at the zero; the behavior is thus rather like strncpy.)
919 The return string has all special characters replaced so that it
920 will be properly processed as an SQL identifier. A terminating
921 zero byte is also added. The return string will also be
922 surrounded by double quotes.
924 On error, PQescapeIdentifier returns NULL and a suitable message
925 is stored in the conn object.
929 As with string literals, to prevent SQL injection attacks, SQL
930 identifiers must be escaped when they are received from an
931 untrustworthy source.
935 size_t PQescapeStringConn(PGconn *conn,
936 char *to, const char *from, size_t length,
939 PQescapeStringConn escapes string literals, much like
940 PQescapeLiteral. Unlike PQescapeLiteral, the caller is
941 responsible for providing an appropriately sized buffer.
942 Furthermore, PQescapeStringConn does not generate the single
943 quotes that must surround PostgreSQL string literals; they
944 should be provided in the SQL command that the result is
945 inserted into. The parameter from points to the first character
946 of the string that is to be escaped, and the length parameter
947 gives the number of bytes in this string. A terminating zero
948 byte is not required, and should not be counted in length. (If a
949 terminating zero byte is found before length bytes are
950 processed, PQescapeStringConn stops at the zero; the behavior is
951 thus rather like strncpy.) to shall point to a buffer that is
952 able to hold at least one more byte than twice the value of
953 length, otherwise the behavior is undefined. Behavior is
954 likewise undefined if the to and from strings overlap.
956 If the error parameter is not NULL, then *error is set to zero
957 on success, nonzero on error. Presently the only possible error
958 conditions involve invalid multibyte encoding in the source
959 string. The output string is still generated on error, but it
960 can be expected that the server will reject it as malformed. On
961 error, a suitable message is stored in the conn object, whether
962 or not error is NULL.
964 PQescapeStringConn returns the number of bytes written to to,
965 not including the terminating zero byte.
968 PQescapeString is an older, deprecated version of
971 size_t PQescapeString (char *to, const char *from, size_t length);
973 The only difference from PQescapeStringConn is that
974 PQescapeString does not take PGconn or error parameters. Because
975 of this, it cannot adjust its behavior depending on the
976 connection properties (such as character encoding) and therefore
977 it might give the wrong results. Also, it has no way to report
980 PQescapeString can be used safely in client programs that work
981 with only one PostgreSQL connection at a time (in this case it
982 can find out what it needs to know “behind the scenes”). In
983 other contexts it is a security hazard and should be avoided in
984 favor of PQescapeStringConn.
987 Escapes binary data for use within an SQL command with the type
988 bytea. As with PQescapeStringConn, this is only used when
989 inserting data directly into an SQL command string.
991 unsigned char *PQescapeByteaConn(PGconn *conn,
992 const unsigned char *from,
996 Certain byte values must be escaped when used as part of a bytea
997 literal in an SQL statement. PQescapeByteaConn escapes bytes
998 using either hex encoding or backslash escaping. See Section 8.4
999 for more information.
1001 The from parameter points to the first byte of the string that
1002 is to be escaped, and the from_length parameter gives the number
1003 of bytes in this binary string. (A terminating zero byte is
1004 neither necessary nor counted.) The to_length parameter points
1005 to a variable that will hold the resultant escaped string
1006 length. This result string length includes the terminating zero
1009 PQescapeByteaConn returns an escaped version of the from
1010 parameter binary string in memory allocated with malloc(). This
1011 memory should be freed using PQfreemem() when the result is no
1012 longer needed. The return string has all special characters
1013 replaced so that they can be properly processed by the
1014 PostgreSQL string literal parser, and the bytea input function.
1015 A terminating zero byte is also added. The single quotes that
1016 must surround PostgreSQL string literals are not part of the
1019 On error, a null pointer is returned, and a suitable error
1020 message is stored in the conn object. Currently, the only
1021 possible error is insufficient memory for the result string.
1024 PQescapeBytea is an older, deprecated version of
1027 unsigned char *PQescapeBytea(const unsigned char *from,
1031 The only difference from PQescapeByteaConn is that PQescapeBytea
1032 does not take a PGconn parameter. Because of this, PQescapeBytea
1033 can only be used safely in client programs that use a single
1034 PostgreSQL connection at a time (in this case it can find out
1035 what it needs to know “behind the scenes”). It might give the
1036 wrong results if used in programs that use multiple database
1037 connections (use PQescapeByteaConn in such cases).
1040 Converts a string representation of binary data into binary data
1041 — the reverse of PQescapeBytea. This is needed when retrieving
1042 bytea data in text format, but not when retrieving it in binary
1045 unsigned char *PQunescapeBytea(const unsigned char *from, size_t *to_length);
1047 The from parameter points to a string such as might be returned
1048 by PQgetvalue when applied to a bytea column. PQunescapeBytea
1049 converts this string representation into its binary
1050 representation. It returns a pointer to a buffer allocated with
1051 malloc(), or NULL on error, and puts the size of the buffer in
1052 to_length. The result must be freed using PQfreemem when it is
1055 This conversion is not exactly the inverse of PQescapeBytea,
1056 because the string is not expected to be “escaped” when received
1057 from PQgetvalue. In particular this means there is no need for
1058 string quoting considerations, and so no need for a PGconn
begriffs.com / projects / ai-pg / blob