D7net
Home
Console
Upload
information
Create File
Create Folder
About
Tools
:
/
proc
/
self
/
root
/
opt
/
td-agent
/
embedded
/
share
/
man
/
man3
/
Filename :
dblink_build_sql_update.3
back
Copy
'\" t .\" Title: dblink_build_sql_update .\" Author: The PostgreSQL Global Development Group .\" Generator: DocBook XSL Stylesheets v1.76.1 <http://docbook.sf.net/> .\" Date: 2014 .\" Manual: PostgreSQL 9.3.5 Documentation .\" Source: PostgreSQL 9.3.5 .\" Language: English .\" .TH "DBLINK_BUILD_SQL_UPDATE" "3" "2014" "PostgreSQL 9.3.5" "PostgreSQL 9.3.5 Documentation" .\" ----------------------------------------------------------------- .\" * Define some portability stuff .\" ----------------------------------------------------------------- .\" ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ .\" http://bugs.debian.org/507673 .\" http://lists.gnu.org/archive/html/groff/2009-02/msg00013.html .\" ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ .ie \n(.g .ds Aq \(aq .el .ds Aq ' .\" ----------------------------------------------------------------- .\" * set default formatting .\" ----------------------------------------------------------------- .\" disable hyphenation .nh .\" disable justification (adjust text to left margin only) .ad l .\" ----------------------------------------------------------------- .\" * MAIN CONTENT STARTS HERE * .\" ----------------------------------------------------------------- .SH "NAME" dblink_build_sql_update \- builds an UPDATE statement using a local tuple, replacing the primary key field values with alternative supplied values .\" dblink_build_sql_update .SH "SYNOPSIS" .sp .nf dblink_build_sql_update(text relname, int2vector primary_key_attnums, integer num_primary_key_atts, text[] src_pk_att_vals_array, text[] tgt_pk_att_vals_array) returns text .fi .SH "DESCRIPTION" .PP \fBdblink_build_sql_update\fR 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 builds a SQL \fBUPDATE\fR 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 \fBUPDATE\fR command always assigns all fields of the row \(em the main difference between this and \fBdblink_build_sql_insert\fR is that it\*(Aqs assumed that the target row already exists in the remote table\&. .SH "ARGUMENTS" .PP \fIrelname\fR .RS 4 Name of a local relation, for example foo or myschema\&.mytab\&. Include double quotes if the name is mixed\-case or contains special characters, for example "FooBar"; without quotes, the string will be folded to lower case\&. .RE .PP \fIprimary_key_attnums\fR .RS 4 Attribute numbers (1\-based) of the primary key fields, for example 1 2\&. .RE .PP \fInum_primary_key_atts\fR .RS 4 The number of primary key fields\&. .RE .PP \fIsrc_pk_att_vals_array\fR .RS 4 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\&. .RE .PP \fItgt_pk_att_vals_array\fR .RS 4 Values of the primary key fields to be placed in the resulting \fBUPDATE\fR command\&. Each field is represented in text form\&. .RE .SH "RETURN VALUE" .PP Returns the requested SQL statement as text\&. .SH "NOTES" .PP As of PostgreSQL 9\&.0, the attribute numbers in \fIprimary_key_attnums\fR are interpreted as logical column numbers, corresponding to the column\*(Aqs position in SELECT * FROM relname\&. Previous versions interpreted the numbers as physical column positions\&. There is a difference if any column(s) to the left of the indicated column have been dropped during the lifetime of the table\&. .SH "EXAMPLES" .sp .if n \{\ .RS 4 .\} .nf SELECT dblink_build_sql_update(\*(Aqfoo\*(Aq, \*(Aq1 2\*(Aq, 2, \*(Aq{"1", "a"}\*(Aq, \*(Aq{"1", "b"}\*(Aq); dblink_build_sql_update \-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\- UPDATE foo SET f1=\*(Aq1\*(Aq,f2=\*(Aqb\*(Aq,f3=\*(Aq1\*(Aq WHERE f1=\*(Aq1\*(Aq AND f2=\*(Aqb\*(Aq (1 row) .fi .if n \{\ .RE .\}