<listitem>

<para>

Allows for mapping between system and database user names. See

<xref linkend="auth-username-maps"/> for details. Fora SSPI/Kerberos

<xref linkend="auth-username-maps"/> for details. Foran SSPI/Kerberos

principal, such as <literal>username@EXAMPLE.COM</literal> (or, less

commonly, <literal>username/hostbased@EXAMPLE.COM</literal>), the

user name used for mapping is

In general, enclose the value in single quotes, doubling any single

quotes within the value. Quotes can usually be omitted if the value

is a simple number or identifier, however.

(Values that matcha SQL keyword require quoting in some contexts.)

(Values that matchan SQL keyword require quoting in some contexts.)

</para>

</listitem>

<productname>PostgreSQL</productname> provides three SQL

commands to establish configuration defaults.

The already-mentioned <command>ALTER SYSTEM</command> command

providesa SQL-accessible means of changing global defaults; it is

providesan SQL-accessible means of changing global defaults; it is

functionally equivalent to editing <filename>postgresql.conf</filename>.

In addition, there are two commands that allow setting of defaults

on a per-database or per-role basis:

<literal>\'</literal>. However, use of <literal>\'</literal> creates security risks

because in some client character set encodings, there are multibyte

characters in which the last byte is numerically equivalent to ASCII

<literal>\</literal>. If client-side code does escaping incorrectly thena

<literal>\</literal>. If client-side code does escaping incorrectly thenan

SQL-injection attack is possible. This risk can be prevented by

making the server reject queries in which a quote mark appears to be

escaped by a backslash.

<para>

The reason that single quotes must be doubled, as shown

in <xref linkend="datatype-binary-sqlesc"/>, is that this

is true for any string literal ina SQL command. The generic

is true for any string literal inan SQL command. The generic

string-literal parser consumes the outermost single quotes

and reduces any pair of single quotes to one data character.

What the <type>bytea</type> input function sees is just one

<title>Return Value</title>

<para>

For an async query (that is,a SQL statement returning rows),

For an async query (that is,an SQL statement returning rows),

the function returns the row(s) produced by the query. To use this

function, you will need to specify the expected set of columns,

as previously discussed for <function>dblink</function>.

</para>

<para>

For an async command (that is,a SQL statement not returning rows),

For an async command (that is,an SQL statement not returning rows),

the function returns a single row with a single text column containing

the command's status string. It is still necessary to specify that

the result will have a single text column in the calling <literal>FROM</literal>

<para>

<function>dblink_build_sql_insert</function> can be useful in doing selective

replication of a local table to a remote database. It selects a row

from the local table based on primary key, and then buildsa SQL

from the local table based on primary key, and then buildsan SQL

<command>INSERT</command> command that will duplicate that row, but with

the primary key values replaced by the values in the last argument.

(To make an exact copy of the row, just specify the same values for

<para>

<function>dblink_build_sql_delete</function> can be useful in doing selective

replication of a local table to a remote database. It buildsa SQL

replication of a local table to a remote database. It buildsan SQL

<command>DELETE</command> command that will delete the row with the given

primary key values.

</para>

<para>

<function>dblink_build_sql_update</function> can be useful in doing selective

replication of a local table to a remote database. It selects a row

from the local table based on primary key, and then buildsa SQL

from the local table based on primary key, and then buildsan SQL

<command>UPDATE</command> command that will duplicate that row, but with

the primary key values replaced by the values in the last argument.

(To make an exact copy of the row, just specify the same values for

</para>

<para>

The third option is to declarea SQL identifier linked to

The third option is to declarean SQL identifier linked to

the connection, for example:

<programlisting>

EXEC SQL AT <replaceable>connection-name</replaceable> DECLARE <replaceable>statement-name</replaceable> STATEMENT;

EXEC SQL PREPARE <replaceable>statement-name</replaceable> FROM :<replaceable>dyn-string</replaceable>;

</programlisting>

Once you linka SQL identifier to a connection, you execute dynamic SQL

Once you linkan SQL identifier to a connection, you execute dynamic SQL

without an AT clause. Note that this option behaves like preprocessor

directives, therefore the link is enabled only in the file.

</para>

variables and vice-versa. However, when creating a statement ecpg does

not know the types of the columns, so that it cannot check if a C array

is input into a corresponding SQL-level array. When processing the

output ofa SQL statement, ecpg has the necessary information and thus

output ofan SQL statement, ecpg has the necessary information and thus

checks if both are arrays.

</para>

parameters to a prepared query are:

</para>

<step><simpara>Create a prepared query (prepared statement)</simpara></step>

<step><simpara>Declarea sqlda_t structure as an input SQLDA.</simpara></step>

<step><simpara>Declarean sqlda_t structure as an input SQLDA.</simpara></step>

<step><simpara>Allocate memory area (as sqlda_t structure) for the input SQLDA.</simpara></step>

<step><simpara>Set (copy) input values in the allocated memory.</simpara></step>

<step><simpara>Open a cursor with specifying the input SQLDA.</simpara></step>

EXEC SQL PREPARE stmt1 FROM :query;

EXEC SQL DECLARE cur1 CURSOR FOR stmt1;

/* Createa SQLDA structure for an input parameter */

/* Createan SQLDA structure for an input parameter */

sqlda2 = (sqlda_t *)malloc(sizeof(sqlda_t) + sizeof(sqlvar_t));

memset(sqlda2, 0, sizeof(sqlda_t) + sizeof(sqlvar_t));

sqlda2->sqln = 2; /* a number of input variables */

<title>Description</title>

<para>

<command>DECLARE STATEMENT</command> declaresa SQL statement identifier.

<command>DECLARE STATEMENT</command> declaresan SQL statement identifier.

SQL statement identifier can be associated with the connection.

When the identifier is used by dynamic SQL statements, the statements

are executed using the associated connection.

<term><replaceable class="parameter">statement_name</replaceable></term>

<listitem>

<para>

The name ofa SQL statement identifier, either as an SQL identifier or a host variable.

The name ofan SQL statement identifier, either as an SQL identifier or a host variable.

</para>

</listitem>

</varlistentry>

and include the global <acronym>PGXS</acronym> makefile.

Here is an example that builds an extension module named

<literal>isbn_issn</literal>, consisting of a shared library containing

some C code, an extension control file,a SQL script, an include file

some C code, an extension control file,an SQL script, an include file

(only needed if other modules might need to access the extension functions

without going via SQL), and a documentation text file:

<programlisting>

<returnvalue>json</returnvalue>

</para>

<para>

Convertsa SQL array to a JSON array. The behavior is the same

Convertsan SQL array to a JSON array. The behavior is the same

as <function>to_json</function> except that line feeds will be added

between top-level array elements if the optional boolean parameter is

true.

<returnvalue>json</returnvalue>

</para>

<para>

Convertsa SQL composite value to a JSON object. The behavior is the

Convertsan SQL composite value to a JSON object. The behavior is the

same as <function>to_json</function> except that line feeds will be

added between top-level elements if the optional boolean parameter is

true.

<itemizedlist spacing="compact">

<listitem>

<para>

A JSON null value is converted toa SQL null in all cases.

A JSON null value is converted toan SQL null in all cases.

</para>

</listitem>

<listitem>

<literal>string</literal>, <literal>number</literal>,

<literal>boolean</literal>, and <literal>null</literal>.

(The <literal>null</literal> result should not be confused

witha SQL NULL; see the examples.)

withan SQL NULL; see the examples.)

</para>

<para>

<literal>json_typeof('-123.4')</literal>

Currently, temporary table creation is not allowed during read only

transactions, so in some cases existing scripts will not run correctly.

This restriction might be relaxed in a later release. This is

botha SQL Standard compliance issue and a technical issue.

bothan SQL Standard compliance issue and a technical issue.

</para>

<para>

<para>

If the index AM wishes to cache data across successive index insertions

withina SQL statement, it can allocate space

withinan SQL statement, it can allocate space

in <literal>indexInfo-&gt;ii_Context</literal> and store a pointer to the

data in <literal>indexInfo-&gt;ii_AmCache</literal> (which will be NULL

initially).

<listitem>

<para>

The text of a failed internally-generated command. This could

be, for example,a SQL query issued by a PL/pgSQL function.

be, for example,an SQL query issued by a PL/pgSQL function.

</para>

</listitem>

</varlistentry>

</indexterm>

<para>

Ordinarily, <application>libpq</application> collectsa SQL command's

Ordinarily, <application>libpq</application> collectsan SQL command's

entire result and returns it to the application as a single

<structname>PGresult</structname>. This can be unworkable for commands

that return a large number of rows. For such cases, applications can use

</para>

<para>

We could simplify this by creatinga SQL function that inserts a label

We could simplify this by creatingan SQL function that inserts a label

at a specified position in a path:

<screen>

CREATE FUNCTION ins_label(ltree, int, text) RETURNS ltree

transactions might do, or it will not successfully commit. It is

important that an environment which uses this technique have a

generalized way of handling serialization failures (which always return

witha SQLSTATE value of '40001'), because it will be very hard to

withan SQLSTATE value of '40001'), because it will be very hard to

predict exactly which transactions might contribute to the read/write

dependencies and need to be rolled back to prevent serialization

anomalies. The monitoring of read/write dependencies has a cost, as does

<listitem>

<para>

<function>fsm_page_contents</function> shows the internal node structure

ofa FSM page. For example:

ofan FSM page. For example:

<screen>

test=# SELECT fsm_page_contents(get_raw_page('pg_class', 'fsm', 0));

</screen>

</listitem>

<listitem>

<para>

An SHA1 hash of the random prefix and data is appended.

A SHA1 hash of the random prefix and data is appended.

</para>

</listitem>

<listitem>

One might expect that writing <command>SELECT</command> directly

would accomplish this result, but at

present the only accepted way to do it is

<command>PERFORM</command>.A SQL command that can return rows,

<command>PERFORM</command>.An SQL command that can return rows,

such as <command>SELECT</command>, will be rejected as an error

unless it has an <literal>INTO</literal> clause as discussed in the

next section.

</indexterm>

<para>

The result ofa SQL command yielding a single row (possibly of multiple

The result ofan SQL command yielding a single row (possibly of multiple

columns) can be assigned to a record variable, row-type variable, or list

of scalar variables. This is done by writing the base SQL command and

adding an <literal>INTO</literal> clause. For example,

<para>

The <literal>INTO</literal> clause specifies where the results of

a SQL command returning rows should be assigned. If a row variable

an SQL command returning rows should be assigned. If a row variable

or variable list is provided, it must exactly match the structure

of the command's results; if a

record variable is provided, it will configure itself to match the

<para>

The <literal>FOREACH</literal> loop is much like a <literal>FOR</literal> loop,

but instead of iterating through the rows returned bya SQL query,

but instead of iterating through the rows returned byan SQL query,

it iterates through the elements of an array value.

(In general, <literal>FOREACH</literal> is meant for looping through

components of a composite-valued expression; variants for looping

<para>

Another way to understand this is that variable substitution can only

insert data values intoa SQL command; it cannot dynamically change which

insert data values intoan SQL command; it cannot dynamically change which

database objects are referenced by the command. (If you want to do

that, you must build a command string dynamically, as explained in

<xref linkend="plpgsql-statements-executing-dyn"/>.)

<para>

By default, <application>PL/pgSQL</application> will report an error if a name

ina SQL statement could refer to either a variable or a table column.

inan SQL statement could refer to either a variable or a table column.

You can fix such a problem by renaming the variable or column,

or by qualifying the ambiguous reference, or by telling

<application>PL/pgSQL</application> which interpretation to prefer.

<itemizedlist>

<listitem>

<para>

If a name used ina SQL command could be either a column name of a

If a name used inan SQL command could be either a column name of a

table used in the command or a reference to a variable of the function,

<application>PL/SQL</application> treats it as a column name.

By default, <application>PL/pgSQL</application> will throw an error

$$ LANGUAGE plpythonu;

</programlisting>

To returna SQL null for any column, insert <symbol>None</symbol> at

To returnan SQL null for any column, insert <symbol>None</symbol> at

the corresponding position.

</para>

<para>

Any extra dictionary key/value pairs are ignored. Missing keys are

treated as errors.

To returna SQL null value for any column, insert

To returnan SQL null value for any column, insert

<symbol>None</symbol> with the corresponding column name as the key.

</para>

</listitem>

<listitem>

<para>

The Tcl script contained in <replaceable>command</replaceable> is

executed withina SQL subtransaction. If the script returns an

executed withinan SQL subtransaction. If the script returns an

error, that entire subtransaction is rolled back before returning the

error out to the surrounding Tcl code.

See <xref linkend="pltcl-subtransactions"/> for more details and an

PL/Tcl's <function>elog</function> command. Such errors can be caught

within Tcl using the Tcl <function>catch</function> command. If an

error is not caught but is allowed to propagate out to the top level of

execution of the PL/Tcl function, it is reported asa SQL error in the

execution of the PL/Tcl function, it is reported asan SQL error in the

function's calling query.

</para>

subtransaction, which is rolled back on error, so that any

partially-completed operation is automatically cleaned up.)

Again, if an error propagates out to the top level without being caught,

it turns back intoa SQL error.

it turns back intoan SQL error.

</para>

<para>