Postgresql Skill

Comprehensive assistance with postgresql development, generated from official documentation.

When to Use This Skill

This skill should be triggered when:

• Working with postgresql
• Asking about postgresql features or APIs
• Implementing postgresql solutions
• Debugging postgresql code
• Learning postgresql best practices

Quick Reference

Common Patterns

Pattern 1: 32.1. Database Connection Control Functions # 32.1.1. Connection Strings 32.1.2. Parameter Key Words The following functions deal with making a connection to a PostgreSQL backend server. An application program can have several backend connections open at one time. (One reason to do that is to access more than one database.) Each connection is represented by a PGconn object, which is obtained from the function PQconnectdb, PQconnectdbParams, or PQsetdbLogin. Note that these functions will always return a non-null object pointer, unless perhaps there is too little memory even to allocate the PGconn object. The PQstatus function should be called to check the return value for a successful connection before queries are sent via the connection object. Warning If untrusted users have access to a database that has not adopted a secure schema usage pattern, begin each session by removing publicly-writable schemas from search_path. One can set parameter key word options to value -csearch_path=. Alternately, one can issue PQexec(conn, "SELECT pg_catalog.set_config('search_path', '', false)") after connecting. This consideration is not specific to libpq; it applies to every interface for executing arbitrary SQL commands. Warning On Unix, forking a process with open libpq connections can lead to unpredictable results because the parent and child processes share the same sockets and operating system resources. For this reason, such usage is not recommended, though doing an exec from the child process to load a new executable is safe. PQconnectdbParams # Makes a new connection to the database server. PGconn *PQconnectdbParams(const char * const *keywords, const char * const *values, int expand_dbname); This function opens a new database connection using the parameters taken from two NULL-terminated arrays. The first, keywords, is defined as an array of strings, each one being a key word. The second, values, gives the value for each key word. Unlike PQsetdbLogin below, the parameter set can be extended without changing the function signature, so use of this function (or its nonblocking analogs PQconnectStartParams and PQconnectPoll) is preferred for new application programming. The currently recognized parameter key words are listed in Section 32.1.2. The passed arrays can be empty to use all default parameters, or can contain one or more parameter settings. They must be matched in length. Processing will stop at the first NULL entry in the keywords array. Also, if the values entry associated with a non-NULL keywords entry is NULL or an empty string, that entry is ignored and processing continues with the next pair of array entries. When expand_dbname is non-zero, the value for the first dbname key word is checked to see if it is a connection string. If so, it is “expanded” into the individual connection parameters extracted from the string. The value is considered to be a connection string, rather than just a database name, if it contains an equal sign (=) or it begins with a URI scheme designator. (More details on connection string formats appear in Section 32.1.1.) Only the first occurrence of dbname is treated in this way; any subsequent dbname parameter is processed as a plain database name. In general the parameter arrays are processed from start to end. If any key word is repeated, the last value (that is not NULL or empty) is used. This rule applies in particular when a key word found in a connection string conflicts with one appearing in the keywords array. Thus, the programmer may determine whether array entries can override or be overridden by values taken from a connection string. Array entries appearing before an expanded dbname entry can be overridden by fields of the connection string, and in turn those fields are overridden by array entries appearing after dbname (but, again, only if those entries supply non-empty values). After processing all the array entries and any expanded connection string, any connection parameters that remain unset are filled with default values. If an unset parameter's corresponding environment variable (see Section 32.15) is set, its value is used. If the environment variable is not set either, then the parameter's built-in default value is used. PQconnectdb # Makes a new connection to the database server. PGconn *PQconnectdb(const char *conninfo); This function opens a new database connection using the parameters taken from the string conninfo. The passed string can be empty to use all default parameters, or it can contain one or more parameter settings separated by whitespace, or it can contain a URI. See Section 32.1.1 for details. PQsetdbLogin # Makes a new connection to the database server. PGconn *PQsetdbLogin(const char *pghost, const char *pgport, const char *pgoptions, const char *pgtty, const char *dbName, const char *login, const char *pwd); This is the predecessor of PQconnectdb with a fixed set of parameters. It has the same functionality except that the missing parameters will always take on default values. Write NULL or an empty string for any one of the fixed parameters that is to be defaulted. If the dbName contains an = sign or has a valid connection URI prefix, it is taken as a conninfo string in exactly the same way as if it had been passed to PQconnectdb, and the remaining parameters are then applied as specified for PQconnectdbParams. pgtty is no longer used and any value passed will be ignored. PQsetdb # Makes a new connection to the database server. PGconn *PQsetdb(char *pghost, char *pgport, char *pgoptions, char *pgtty, char *dbName); This is a macro that calls PQsetdbLogin with null pointers for the login and pwd parameters. It is provided for backward compatibility with very old programs. PQconnectStartParamsPQconnectStartPQconnectPoll # Make a connection to the database server in a nonblocking manner. PGconn *PQconnectStartParams(const char * const *keywords, const char * const *values, int expand_dbname); PGconn *PQconnectStart(const char *conninfo); PostgresPollingStatusType PQconnectPoll(PGconn *conn); These three functions are used to open a connection to a database server such that your application's thread of execution is not blocked on remote I/O whilst doing so. The point of this approach is that the waits for I/O to complete can occur in the application's main loop, rather than down inside PQconnectdbParams or PQconnectdb, and so the application can manage this operation in parallel with other activities. With PQconnectStartParams, the database connection is made using the parameters taken from the keywords and values arrays, and controlled by expand_dbname, as described above for PQconnectdbParams. With PQconnectStart, the database connection is made using the parameters taken from the string conninfo as described above for PQconnectdb. Neither PQconnectStartParams nor PQconnectStart nor PQconnectPoll will block, so long as a number of restrictions are met: The hostaddr parameter must be used appropriately to prevent DNS queries from being made. See the documentation of this parameter in Section 32.1.2 for details. If you call PQtrace, ensure that the stream object into which you trace will not block. You must ensure that the socket is in the appropriate state before calling PQconnectPoll, as described below. To begin a nonblocking connection request, call PQconnectStart or PQconnectStartParams. If the result is null, then libpq has been unable to allocate a new PGconn structure. Otherwise, a valid PGconn pointer is returned (though not yet representing a valid connection to the databas