POSTMASTER(1) PostgreSQL Server Applications POSTMASTER(1)NAMEpostmaster - PostgreSQL database server
SYNOPSISpostmaster [ option... ]
DESCRIPTIONpostmaster is a deprecated alias of postgres.
SEE ALSOpostgres(1)NAME
dblink_connect - opens a persistent connection to a remote database
SYNOPSIS
dblink_connect(text connstr) returns text
dblink_connect(text connname, text connstr) returns text
DESCRIPTIONdblink_connect() establishes a connection to a remote PostgreSQL data‐
base. The server and database to be contacted are identified through a
standard libpq connection string. Optionally, a name can be assigned to
the connection. Multiple named connections can be open at once, but
only one unnamed connection is permitted at a time. The connection will
persist until closed or until the database session is ended.
ARGUMENTS
conname
The name to use for this connection; if omitted, an unnamed con‐
nection is opened, replacing any existing unnamed connection.
connstr
libpq-style connection info string, for example
hostaddr=127.0.0.1 port=5432 dbname=mydb user=postgres pass‐
word=mypasswd. For details see PQconnectdb in in the documenta‐
tion.
RETURN VALUE
Returns status, which is always OK (since any error causes the function
to throw an error instead of returning).
NOTES
Only superusers may use dblink_connect to create non-password-authenti‐
cated connections. If non-superusers need this capability, use
dblink_connect_u instead.
It is unwise to choose connection names that contain equal signs, as
this opens a risk of confusion with connection info strings in other
dblink functions.
EXAMPLE
select dblink_connect('dbname=postgres');
dblink_connect
----------------
OK
(1 row)
select dblink_connect('myconn', 'dbname=postgres');
dblink_connect
----------------
OK
(1 row)
NAME
dblink_connect_u - opens a persistent connection to a remote database,
insecurely
SYNOPSIS
dblink_connect_u(text connstr) returns text
dblink_connect_u(text connname, text connstr) returns text
DESCRIPTIONdblink_connect_u() is identical to dblink_connect(), except that it
will allow non-superusers to connect using any authentication method.
If the remote server selects an authentication method that does not
involve a password, then impersonation and subsequent escalation of
privileges can occur, because the session will appear to have origi‐
nated from the user as which the local PostgreSQL server runs. There‐
fore, dblink_connect_u() is initially installed with all privileges
revoked from PUBLIC, making it un-callable except by superusers. In
some situations it may be appropriate to grant EXECUTE permission for
dblink_connect_u() to specific users who are considered trustworthy,
but this should be done with care.
For further details see dblink_connect().
NAME
dblink_disconnect - closes a persistent connection to a remote database
SYNOPSISdblink_disconnect() returns text
dblink_disconnect(text connname) returns text
DESCRIPTIONdblink_disconnect() closes a connection previously opened by
dblink_connect(). The form with no arguments closes an unnamed connec‐
tion.
ARGUMENTS
conname
The name of a named connection to be closed.
RETURN VALUE
Returns status, which is always OK (since any error causes the function
to throw an error instead of returning).
EXAMPLE
test=# select dblink_disconnect();
dblink_disconnect
-------------------
OK
(1 row)
select dblink_disconnect('myconn');
dblink_disconnect
-------------------
OK
(1 row)
NAME
dblink - executes a query in a remote database
SYNOPSIS
dblink(text connname, text sql [, bool fail_on_error]) returns setof record
dblink(text connstr, text sql [, bool fail_on_error]) returns setof record
dblink(text sql [, bool fail_on_error]) returns setof record
DESCRIPTION
dblink executes a query (usually a SELECT, but it can be any SQL state‐
ment that returns rows) in a remote database.
When two text arguments are given, the first one is first looked up as
a persistent connection's name; if found, the command is executed on
that connection. If not found, the first argument is treated as a con‐
nection info string as for dblink_connect, and the indicated connection
is made just for the duration of this command.
ARGUMENTS
conname
Name of the connection to use; omit this parameter to use the
unnamed connection.
connstr
A connection info string, as previously described for
dblink_connect.
sql The SQL query that you wish to execute in the remote database,
for example select * from foo.
fail_on_error
If true (the default when omitted) then an error thrown on the
remote side of the connection causes an error to also be thrown
locally. If false, the remote error is locally reported as a
NOTICE, and the function returns no rows.
RETURN VALUE
The function returns the row(s) produced by the query. Since dblink can
be used with any query, it is declared to return record, rather than
specifying any particular set of columns. This means that you must
specify the expected set of columns in the calling query — otherwise
PostgreSQL would not know what to expect. Here is an example:
SELECT *
FROM dblink('dbname=mydb', 'select proname, prosrc from pg_proc')
AS t1(proname name, prosrc text)
WHERE proname LIKE 'bytea%';
The ``alias'' part of the FROM clause must specify the column names and
types that the function will return. (Specifying column names in an
alias is actually standard SQL syntax, but specifying column types is a
PostgreSQL extension.) This allows the system to understand what *
should expand to, and what proname in the WHERE clause refers to, in
advance of trying to execute the function. At runtime, an error will be
thrown if the actual query result from the remote database does not
have the same number of columns shown in the FROM clause. The column
names need not match, however, and dblink does not insist on exact type
matches either. It will succeed so long as the returned data strings
are valid input for the column type declared in the FROM clause.
NOTES
dblink fetches the entire remote query result before returning any of
it to the local system. If the query is expected to return a large num‐
ber of rows, it's better to open it as a cursor with dblink_open and
then fetch a manageable number of rows at a time.
A convenient way to use dblink with predetermined queries is to create
a view. This allows the column type information to be buried in the
view, instead of having to spell it out in every query. For example,
create view myremote_pg_proc as
select *
from dblink('dbname=postgres', 'select proname, prosrc from pg_proc')
as t1(proname name, prosrc text);
select * from myremote_pg_proc where proname like 'bytea%';
EXAMPLE
select * from dblink('dbname=postgres', 'select proname, prosrc from pg_proc')
as t1(proname name, prosrc text) where proname like 'bytea%';
proname | prosrc
------------+------------
byteacat | byteacat
byteaeq | byteaeq
bytealt | bytealt
byteale | byteale
byteagt | byteagt
byteage | byteage
byteane | byteane
byteacmp | byteacmp
bytealike | bytealike
byteanlike | byteanlike
byteain | byteain
byteaout | byteaout
(12 rows)
select dblink_connect('dbname=postgres');
dblink_connect
----------------
OK
(1 row)
select * from dblink('select proname, prosrc from pg_proc')
as t1(proname name, prosrc text) where proname like 'bytea%';
proname | prosrc
------------+------------
byteacat | byteacat
byteaeq | byteaeq
bytealt | bytealt
byteale | byteale
byteagt | byteagt
byteage | byteage
byteane | byteane
byteacmp | byteacmp
bytealike | bytealike
byteanlike | byteanlike
byteain | byteain
byteaout | byteaout
(12 rows)
select dblink_connect('myconn', 'dbname=regression');
dblink_connect
----------------
OK
(1 row)
select * from dblink('myconn', 'select proname, prosrc from pg_proc')
as t1(proname name, prosrc text) where proname like 'bytea%';
proname | prosrc
------------+------------
bytearecv | bytearecv
byteasend | byteasend
byteale | byteale
byteagt | byteagt
byteage | byteage
byteane | byteane
byteacmp | byteacmp
bytealike | bytealike
byteanlike | byteanlike
byteacat | byteacat
byteaeq | byteaeq
bytealt | bytealt
byteain | byteain
byteaout | byteaout
(14 rows)
NAME
dblink_exec - executes a command in a remote database
SYNOPSIS
dblink_exec(text connname, text sql [, bool fail_on_error]) returns text
dblink_exec(text connstr, text sql [, bool fail_on_error]) returns text
dblink_exec(text sql [, bool fail_on_error]) returns text
DESCRIPTION
dblink_exec executes a command (that is, any SQL statement that doesn't
return rows) in a remote database.
When two text arguments are given, the first one is first looked up as
a persistent connection's name; if found, the command is executed on
that connection. If not found, the first argument is treated as a con‐
nection info string as for dblink_connect, and the indicated connection
is made just for the duration of this command.
ARGUMENTS
conname
Name of the connection to use; omit this parameter to use the
unnamed connection.
connstr
A connection info string, as previously described for
dblink_connect.
sql The SQL command that you wish to execute in the remote database,
for example insert into foo values(0,'a','{"a0","b0","c0"}').
fail_on_error
If true (the default when omitted) then an error thrown on the
remote side of the connection causes an error to also be thrown
locally. If false, the remote error is locally reported as a
NOTICE, and the function's return value is set to ERROR.
RETURN VALUE
Returns status, either the command's status string or ERROR.
EXAMPLE
select dblink_connect('dbname=dblink_test_slave');
dblink_connect
----------------
OK
(1 row)
select dblink_exec('insert into foo values(21,''z'',''{"a0","b0","c0"}'');');
dblink_exec
-----------------
INSERT 943366 1
(1 row)
select dblink_connect('myconn', 'dbname=regression');
dblink_connect
----------------
OK
(1 row)
select dblink_exec('myconn', 'insert into foo values(21,''z'',''{"a0","b0","c0"}'');');
dblink_exec
------------------
INSERT 6432584 1
(1 row)
select dblink_exec('myconn', 'insert into pg_class values (''foo'')',false);
NOTICE: sql error
DETAIL: ERROR: null value in column "relnamespace" violates not-null constraint
dblink_exec
-------------
ERROR
(1 row)
NAME
dblink_open - opens a cursor in a remote database
SYNOPSIS
dblink_open(text cursorname, text sql [, bool fail_on_error]) returns text
dblink_open(text connname, text cursorname, text sql [, bool fail_on_error]) returns text
DESCRIPTIONdblink_open() opens a cursor in a remote database. The cursor can sub‐
sequently be manipulated with dblink_fetch() and dblink_close().
ARGUMENTS
conname
Name of the connection to use; omit this parameter to use the
unnamed connection.
cursorname
The name to assign to this cursor.
sql The SELECT statement that you wish to execute in the remote
database, for example select * from pg_class.
fail_on_error
If true (the default when omitted) then an error thrown on the
remote side of the connection causes an error to also be thrown
locally. If false, the remote error is locally reported as a
NOTICE, and the function's return value is set to ERROR.
RETURN VALUE
Returns status, either OK or ERROR.
NOTES
Since a cursor can only persist within a transaction, dblink_open
starts an explicit transaction block (BEGIN) on the remote side, if the
remote side was not already within a transaction. This transaction will
be closed again when the matching dblink_close is executed. Note that
if you use dblink_exec to change data between dblink_open and
dblink_close, and then an error occurs or you use dblink_disconnect
before dblink_close, your change will be lost because the transaction
will be aborted.
EXAMPLE
test=# select dblink_connect('dbname=postgres');
dblink_connect
----------------
OK
(1 row)
test=# select dblink_open('foo', 'select proname, prosrc from pg_proc');
dblink_open
-------------
OK
(1 row)
NAME
dblink_fetch - returns rows from an open cursor in a remote database
SYNOPSIS
dblink_fetch(text cursorname, int howmany [, bool fail_on_error]) returns setof record
dblink_fetch(text connname, text cursorname, int howmany [, bool fail_on_error]) returns setof record
DESCRIPTION
dblink_fetch fetches rows from a cursor previously established by
dblink_open.
ARGUMENTS
conname
Name of the connection to use; omit this parameter to use the
unnamed connection.
cursorname
The name of the cursor to fetch from.
howmany
The maximum number of rows to retrieve. The next howmany rows
are fetched, starting at the current cursor position, moving
forward. Once the cursor has reached its end, no more rows are
produced.
fail_on_error
If true (the default when omitted) then an error thrown on the
remote side of the connection causes an error to also be thrown
locally. If false, the remote error is locally reported as a
NOTICE, and the function returns no rows.
RETURN VALUE
The function returns the row(s) fetched from the cursor. To use this
function, you will need to specify the expected set of columns, as pre‐
viously discussed for dblink.
NOTES
On a mismatch between the number of return columns specified in the
FROM clause, and the actual number of columns returned by the remote
cursor, an error will be thrown. In this event, the remote cursor is
still advanced by as many rows as it would have been if the error had
not occurred. The same is true for any other error occurring in the
local query after the remote FETCH has been done.
EXAMPLE
test=# select dblink_connect('dbname=postgres');
dblink_connect
----------------
OK
(1 row)
test=# select dblink_open('foo', 'select proname, prosrc from pg_proc where proname like ''bytea%''');
dblink_open
-------------
OK
(1 row)
test=# select * from dblink_fetch('foo', 5) as (funcname name, source text);
funcname | source
----------+----------
byteacat | byteacat
byteacmp | byteacmp
byteaeq | byteaeq
byteage | byteage
byteagt | byteagt
(5 rows)
test=# select * from dblink_fetch('foo', 5) as (funcname name, source text);
funcname | source
-----------+-----------
byteain | byteain
byteale | byteale
bytealike | bytealike
bytealt | bytealt
byteane | byteane
(5 rows)
test=# select * from dblink_fetch('foo', 5) as (funcname name, source text);
funcname | source
------------+------------
byteanlike | byteanlike
byteaout | byteaout
(2 rows)
test=# select * from dblink_fetch('foo', 5) as (funcname name, source text);
funcname | source
----------+--------
(0 rows)
NAME
dblink_close - closes a cursor in a remote database
SYNOPSIS
dblink_close(text cursorname [, bool fail_on_error]) returns text
dblink_close(text connname, text cursorname [, bool fail_on_error]) returns text
DESCRIPTION
dblink_close closes a cursor previously opened with dblink_open.
ARGUMENTS
conname
Name of the connection to use; omit this parameter to use the
unnamed connection.
cursorname
The name of the cursor to close.
fail_on_error
If true (the default when omitted) then an error thrown on the
remote side of the connection causes an error to also be thrown
locally. If false, the remote error is locally reported as a
NOTICE, and the function's return value is set to ERROR.
RETURN VALUE
Returns status, either OK or ERROR.
NOTES
If dblink_open started an explicit transaction block, and this is the
last remaining open cursor in this connection, dblink_close will issue
the matching COMMIT.
EXAMPLE
test=# select dblink_connect('dbname=postgres');
dblink_connect
----------------
OK
(1 row)
test=# select dblink_open('foo', 'select proname, prosrc from pg_proc');
dblink_open
-------------
OK
(1 row)
test=# select dblink_close('foo');
dblink_close
--------------
OK
(1 row)
NAME
dblink_get_connections - returns the names of all open named dblink
connections
SYNOPSISdblink_get_connections() returns text[]
DESCRIPTION
dblink_get_connections returns an array of the names of all open named
dblink connections.
RETURN VALUE
Returns a text array of connection names, or NULL if none.
EXAMPLE
SELECT dblink_get_connections();
NAME
dblink_error_message - gets last error message on the named connection
SYNOPSIS
dblink_error_message(text connname) returns text
DESCRIPTION
dblink_error_message fetches the most recent remote error message for a
given connection.
ARGUMENTS
conname
Name of the connection to use.
RETURN VALUE
Returns last error message, or an empty string if there has been no
error in this connection.
EXAMPLE
SELECT dblink_error_message('dtest1');
NAME
dblink_send_query - sends an async query to a remote database
SYNOPSIS
dblink_send_query(text connname, text sql) returns int
DESCRIPTION
dblink_send_query sends a query to be executed asynchronously, that is,
without immediately waiting for the result. There must not be an async
query already in progress on the connection.
After successfully dispatching an async query, completion status can be
checked with dblink_is_busy, and the results are ultimately collected
with dblink_get_result. It is also possible to attempt to cancel an
active async query using dblink_cancel_query.
ARGUMENTS
conname
Name of the connection to use.
sql The SQL statement that you wish to execute in the remote data‐
base, for example select * from pg_class.
RETURN VALUE
Returns 1 if the query was successfully dispatched, 0 otherwise.
EXAMPLE
SELECT dblink_send_query('dtest1', 'SELECT * FROM foo WHERE f1 < 3');
NAME
dblink_is_busy - checks if connection is busy with an async query
SYNOPSIS
dblink_is_busy(text connname) returns int
DESCRIPTION
dblink_is_busy tests whether an async query is in progress.
ARGUMENTS
conname
Name of the connection to check.
RETURN VALUE
Returns 1 if connection is busy, 0 if it is not busy. If this function
returns 0, it is guaranteed that dblink_get_result will not block.
EXAMPLE
SELECT dblink_is_busy('dtest1');
NAME
dblink_get_result - gets an async query result
SYNOPSIS
dblink_get_result(text connname [, bool fail_on_error]) returns setof record
DESCRIPTION
dblink_get_result collects the results of an asynchronous query previ‐
ously sent with dblink_send_query. If the query is not already com‐
pleted, dblink_get_result will wait until it is.
ARGUMENTS
conname
Name of the connection to use.
fail_on_error
If true (the default when omitted) then an error thrown on the
remote side of the connection causes an error to also be thrown
locally. If false, the remote error is locally reported as a
NOTICE, and the function returns no rows.
RETURN VALUE
For an async query (that is, a SQL statement returning rows), the func‐
tion 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 dblink.
For an async command (that is, a 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 FROM clause.
NOTES
This function must be called if dblink_send_query returned 1. It must
be called once for each query sent, and one additional time to obtain
an empty set result, before the connection can be used again.
EXAMPLE
contrib_regression=# SELECT dblink_connect('dtest1', 'dbname=contrib_regression');
dblink_connect
----------------
OK
(1 row)
contrib_regression=# SELECT * from
contrib_regression-# dblink_send_query('dtest1', 'select * from foo where f1 < 3') as t1;
t1
----
1
(1 row)
contrib_regression=# SELECT * from dblink_get_result('dtest1') as t1(f1 int, f2 text, f3 text[]);
f1 | f2 | f3
----+----+------------
0 | a | {a0,b0,c0}
1 | b | {a1,b1,c1}
2 | c | {a2,b2,c2}
(3 rows)
contrib_regression=# SELECT * from dblink_get_result('dtest1') as t1(f1 int, f2 text, f3 text[]);
f1 | f2 | f3
----+----+----
(0 rows)
contrib_regression=# SELECT * from
dblink_send_query('dtest1', 'select * from foo where f1 < 3; select * from foo where f1 > 6') as t1;
t1
----
1
(1 row)
contrib_regression=# SELECT * from dblink_get_result('dtest1') as t1(f1 int, f2 text, f3 text[]);
f1 | f2 | f3
----+----+------------
0 | a | {a0,b0,c0}
1 | b | {a1,b1,c1}
2 | c | {a2,b2,c2}
(3 rows)
contrib_regression=# SELECT * from dblink_get_result('dtest1') as t1(f1 int, f2 text, f3 text[]);
f1 | f2 | f3
----+----+---------------
7 | h | {a7,b7,c7}
8 | i | {a8,b8,c8}
9 | j | {a9,b9,c9}
10 | k | {a10,b10,c10}
(4 rows)
contrib_regression=# SELECT * from dblink_get_result('dtest1') as t1(f1 int, f2 text, f3 text[]);
f1 | f2 | f3
----+----+----
(0 rows)
NAME
dblink_cancel_query - cancels any active query on the named connection
SYNOPSIS
dblink_cancel_query(text connname) returns text
DESCRIPTION
dblink_cancel_query attempts to cancel any query that is in progress on
the named connection. Note that this is not certain to succeed (since,
for example, the remote query might already have finished). A cancel
request simply improves the odds that the query will fail soon. You
must still complete the normal query protocol, for example by calling
dblink_get_result.
ARGUMENTS
conname
Name of the connection to use.
RETURN VALUE
Returns OK if the cancel request has been sent, or the text of an error
message on failure.
EXAMPLE
SELECT dblink_cancel_query('dtest1');
NAME
dblink_current_query - returns the current query string
SYNOPSISdblink_current_query() returns text
DESCRIPTION
Returns the currently executing interactive command string of the local
database session, or NULL if it can't be determined. Note that this
function is not really related to dblink's other functionality. It is
provided since it is sometimes useful in generating queries to be for‐
warded to remote databases.
RETURN VALUE
Returns a copy of the currently executing query string.
EXAMPLE
test=# select dblink_current_query();
dblink_current_query
--------------------------------
select dblink_current_query();
(1 row)
NAME
dblink_get_pkey - returns the positions and field names of a relation's
primary key fields
SYNOPSIS
dblink_get_pkey(text relname) returns setof dblink_pkey_results
DESCRIPTION
dblink_get_pkey provides information about the primary key of a rela‐
tion in the local database. This is sometimes useful in generating
queries to be sent to remote databases.
ARGUMENTS
relname
Name of a local relation, for example foo or myschema.mytab.
Include double quotes if the name is mixed-case or contains spe‐
cial characters, for example "FooBar"; without quotes, the
string will be folded to lower case.
RETURN VALUE
Returns one row for each primary key field, or no rows if the relation
has no primary key. The result rowtype is defined as
CREATE TYPE dblink_pkey_results AS (position int, colname text);
EXAMPLE
test=# create table foobar(f1 int, f2 int, f3 int,
test(# primary key(f1,f2,f3));
CREATE TABLE
test=# select * from dblink_get_pkey('foobar');
position | colname
----------+---------
1 | f1
2 | f2
3 | f3
(3 rows)
NAME
dblink_build_sql_insert - builds an INSERT statement using a local
tuple, replacing the primary key field values with alternative sup‐
plied values
SYNOPSIS
dblink_build_sql_insert(text relname,
int2vector primary_key_attnums,
int2 num_primary_key_atts,
text[] src_pk_att_vals_array,
text[] tgt_pk_att_vals_array) returns text
DESCRIPTION
dblink_build_sql_insert can be useful in doing selective replication of
a local table to a remote database. It selects a row from the local ta‐
ble based on primary key, and then builds a SQL INSERT 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 the last two arguments.)
ARGUMENTS
relname
Name of a local relation, for example foo or myschema.mytab.
Include double quotes if the name is mixed-case or contains spe‐
cial characters, for example "FooBar"; without quotes, the
string will be folded to lower case.
primary_key_attnums
Attribute numbers (1-based) of the primary key fields, for exam‐
ple 1 2.
num_primary_key_atts
The number of primary key fields.
src_pk_att_vals_array
Values of the primary key fields to be used to look up the local
tuple. Each field is represented in text form. An error is
thrown if there is no local row with these primary key values.
tgt_pk_att_vals_array
Values of the primary key fields to be placed in the resulting
INSERT command. Each field is represented in text form.
RETURN VALUE
Returns the requested SQL statement as text.
EXAMPLE
test=# select dblink_build_sql_insert('foo', '1 2', 2, '{"1", "a"}', '{"1", "b''a"}');
dblink_build_sql_insert
--------------------------------------------------
INSERT INTO foo(f1,f2,f3) VALUES('1','b''a','1')
(1 row)
NAME
dblink_build_sql_delete - builds a DELETE statement using supplied val‐
ues for primary key field values
SYNOPSIS
dblink_build_sql_delete(text relname,
int2vector primary_key_attnums,
int2 num_primary_key_atts,
text[] tgt_pk_att_vals_array) returns text
DESCRIPTION
dblink_build_sql_delete can be useful in doing selective replication of
a local table to a remote database. It builds a SQL DELETE command that
will delete the row with the given primary key values.
ARGUMENTS
relname
Name of a local relation, for example foo or myschema.mytab.
Include double quotes if the name is mixed-case or contains spe‐
cial characters, for example "FooBar"; without quotes, the
string will be folded to lower case.
primary_key_attnums
Attribute numbers (1-based) of the primary key fields, for exam‐
ple 1 2.
num_primary_key_atts
The number of primary key fields.
tgt_pk_att_vals_array
Values of the primary key fields to be used in the resulting
DELETE command. Each field is represented in text form.
RETURN VALUE
Returns the requested SQL statement as text.
EXAMPLE
test=# select dblink_build_sql_delete('"MyFoo"', '1 2', 2, '{"1", "b"}');
dblink_build_sql_delete
---------------------------------------------
DELETE FROM "MyFoo" WHERE f1='1' AND f2='b'
(1 row)
NAME
dblink_build_sql_update - builds an UPDATE statement using a local
tuple, replacing the primary key field values with alternative sup‐
plied values
SYNOPSIS
dblink_build_sql_update(text relname,
int2vector primary_key_attnums,
int2 num_primary_key_atts,
text[] src_pk_att_vals_array,
text[] tgt_pk_att_vals_array) returns text
DESCRIPTION
dblink_build_sql_update can be useful in doing selective replication of
a local table to a remote database. It selects a row from the local ta‐
ble based on primary key, and then builds a SQL UPDATE 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 the last two arguments.) The UPDATE
command always assigns all fields of the row — the main difference
between this and dblink_build_sql_insert is that it's assumed that the
target row already exists in the remote table.
ARGUMENTS
relname
Name of a local relation, for example foo or myschema.mytab.
Include double quotes if the name is mixed-case or contains spe‐
cial characters, for example "FooBar"; without quotes, the
string will be folded to lower case.
primary_key_attnums
Attribute numbers (1-based) of the primary key fields, for exam‐
ple 1 2.
num_primary_key_atts
The number of primary key fields.
src_pk_att_vals_array
Values of the primary key fields to be used to look up the local
tuple. Each field is represented in text form. An error is
thrown if there is no local row with these primary key values.
tgt_pk_att_vals_array
Values of the primary key fields to be placed in the resulting
UPDATE command. Each field is represented in text form.
RETURN VALUE
Returns the requested SQL statement as text.
EXAMPLE
test=# select dblink_build_sql_update('foo', '1 2', 2, '{"1", "a"}', '{"1", "b"}');
dblink_build_sql_update
-------------------------------------------------------------
UPDATE foo SET f1='1',f2='b',f3='1' WHERE f1='1' AND f2='b'
(1 row)
Application 2013-02-04 POSTMASTER(1)
