dblink_build_sql_insert(3) [centos man page]

DBLINK_BUILD_SQL_INSERT(3)				  PostgreSQL 9.2.7 Documentation				DBLINK_BUILD_SQL_INSERT(3)

NAME

       dblink_build_sql_insert - builds an INSERT statement using a local tuple, replacing the primary key field values with alternative supplied
       values

SYNOPSIS

       dblink_build_sql_insert(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

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
       table 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 special
	   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 example 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.

NOTES

       As of PostgreSQL 9.0, the attribute numbers in primary_key_attnums are interpreted as logical column numbers, corresponding to the column's
       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.

EXAMPLES

	   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)

PostgreSQL 9.2.7						    2014-02-17						DBLINK_BUILD_SQL_INSERT(3)

INSERT(7)							   SQL Commands 							 INSERT(7)

NAME

       INSERT - create new rows in a table

SYNOPSIS

       INSERT INTO table [ ( column [, ...] ) ]
	   { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) | SELECT query }

   INPUTS
       table  The name (optionally schema-qualified) of an existing table.

       column The name of a column in table.

       DEFAULT VALUES
	      All columns will be filled by null values or by values specified when the table was created using DEFAULT clauses.

       expression
	      A valid expression or value to assign to column.

       DEFAULT
	      This column will be filled in by the column DEFAULT clause, or NULL if a default is not available.

       query  A valid query. Refer to the SELECT statement for a further description of valid arguments.

   OUTPUTS
       INSERT oid 1
	      Message returned if only one row was inserted.  oid is the numeric OID of the inserted row.

       INSERT 0 #
	      Message returned if more than one rows were inserted.  # is the number of rows inserted.

DESCRIPTION

       INSERT  allows one to insert new rows into a table. One can insert a single row at a time or several rows as a result of a query.  The col-
       umns in the target list may be listed in any order.

       Each column not present in the target list will be inserted using a default value, either a declared DEFAULT value or NULL. PostgreSQL will
       reject the new column if a NULL is inserted into a column declared NOT NULL.

       If the expression for each column is not of the correct data type, automatic type coercion will be attempted.

       You must have insert privilege to a table in order to append to it, as well as select privilege on any table specified in a WHERE clause.

USAGE

       Insert a single row into table films:

       INSERT INTO films VALUES
	   ('UA502','Bananas',105,'1971-07-13','Comedy',INTERVAL '82 minute');

       In this second example the last column len is omitted and therefore it will have the default value of NULL:

       INSERT INTO films (code, title, did, date_prod, kind)
	   VALUES ('T_601', 'Yojimbo', 106, DATE '1961-06-16', 'Drama');

       In the third example, we use the DEFAULT values for the date columns rather than specifying an entry.

       INSERT INTO films VALUES
	   ('UA502','Bananas',105,DEFAULT,'Comedy',INTERVAL '82 minute');
       INSERT INTO films (code, title, did, date_prod, kind)
	   VALUES ('T_601', 'Yojimbo', 106, DEFAULT, 'Drama');

       Insert  a  single  row  into  table  distributors;  note that only column name is specified, so the omitted column did will be assigned its
       default value:

       INSERT INTO distributors (name) VALUES ('British Lion');

       Insert several rows into table films from table tmp:

       INSERT INTO films SELECT * FROM tmp;

       Insert into arrays (refer to the PostgreSQL User's Guide for further information about arrays):

       -- Create an empty 3x3 gameboard for noughts-and-crosses
       -- (all of these queries create the same board attribute)
       INSERT INTO tictactoe (game, board[1:3][1:3])
	   VALUES (1,'{{"","",""},{},{"",""}}');
       INSERT INTO tictactoe (game, board[3][3])
	   VALUES (2,'{}');
       INSERT INTO tictactoe (game, board)
	   VALUES (3,'{{,,},{,,},{,,}}');

COMPATIBILITY

   SQL92
       INSERT is fully compatible with SQL92.  Possible limitations in features of the query clause are documented for SELECT [select(7)].

SQL - Language Statements					    2002-11-22								 INSERT(7)