pg_execute_from_file.v8.patch

*** a/doc/src/sgml/func.sgml
--- b/doc/src/sgml/func.sgml
***************
*** 1840,1846 ****
          </indexterm>
          <literal><function>replace(<parameter>string</parameter> <type>text</type>,
          <parameter>from</parameter> <type>text</type>,
!         <parameter>to</parameter> <type>text</type>)</function></literal>
         </entry>
         <entry><type>text</type></entry>
         <entry>Replace all occurrences in <parameter>string</parameter> of substring
--- 1840,1849 ----
          </indexterm>
          <literal><function>replace(<parameter>string</parameter> <type>text</type>,
          <parameter>from</parameter> <type>text</type>,
!         <parameter>to</parameter> <type>text</type>
!         [, <parameter>from</parameter> <type>text</type>,
!            <parameter>to</parameter> <type>text</type>,
!            [, ...] ])</function></literal>
         </entry>
         <entry><type>text</type></entry>
         <entry>Replace all occurrences in <parameter>string</parameter> of substring
***************
*** 14449,14466 **** postgres=# SELECT * FROM pg_xlogfile_name_offset(pg_stop_backup());
        </row>
        <row>
         <entry>
!         <literal><function>pg_read_file(<parameter>filename</> <type>text</>, <parameter>offset</> <type>bigint</>, <parameter>length</> <type>bigint</>)</function></literal>
         </entry>
         <entry><type>text</type></entry>
         <entry>Return the contents of a text file</entry>
        </row>
        <row>
         <entry>
          <literal><function>pg_stat_file(<parameter>filename</> <type>text</>)</function></literal>
         </entry>
         <entry><type>record</type></entry>
         <entry>Return information about a file</entry>
        </row>
       </tbody>
      </tgroup>
     </table>
--- 14452,14497 ----
        </row>
        <row>
         <entry>
!         <literal><function>pg_read_file(<parameter>filename</> <type>text</>, <parameter>offset</> <type>bigint</> [, <parameter>length</> <type>bigint</>])</function></literal>
         </entry>
         <entry><type>text</type></entry>
         <entry>Return the contents of a text file</entry>
        </row>
        <row>
         <entry>
+         <literal><function>pg_read_binary_file(<parameter>filename</> <type>text</>, <parameter>offset</> <type>bigint</> [, <parameter>length</> <type>bigint</>])</function></literal>
+        </entry>
+        <entry><type>bytea</type></entry>
+        <entry>Return the contents of a file</entry>
+       </row>
+       <row>
+        <entry>
          <literal><function>pg_stat_file(<parameter>filename</> <type>text</>)</function></literal>
         </entry>
         <entry><type>record</type></entry>
         <entry>Return information about a file</entry>
        </row>
+       <row>
+        <entry>
+         <literal><function>pg_execute_sql_string(<parameter>sql</> <type>text</>
+ [, <parameter>variable</parameter> <type>text</type>, <parameter>value</parameter> <type>text</type>
+ [, ...] ]) )</function></literal>
+        </entry>
+        <entry><type>void</type></entry>
+        <entry>Executes the given <acronym>SQL</> commands, replacing placeholders, if any.</entry>
+       </row>
+       <row>
+        <entry>
+         <literal><function>pg_execute_sql_file(<parameter>filename</> <type>text</>
+ [ [, <parameter>encoding</parameter> <type>name</type>]
+   [, <parameter>variable</parameter> <type>text</type>, <parameter>value</parameter> <type>text</type>
+   [, ...] ] ]) )</function></literal>
+        </entry>
+        <entry><type>void</type></entry>
+        <entry>Executes the <acronym>SQL</> commands contained in a file,
+        expected in either database encoding or given encoding, and replacing
+        given placeholders, if any.</entry>
+       </row>
       </tbody>
      </tgroup>
     </table>
***************
*** 14478,14487 **** postgres=# SELECT * FROM pg_xlogfile_name_offset(pg_stop_backup());
      <primary>pg_read_file</primary>
     </indexterm>
     <para>
!     <function>pg_read_file</> returns part of a text file, starting
!     at the given <parameter>offset</>, returning at most <parameter>length</>
!     bytes (less if the end of file is reached first).  If <parameter>offset</>
!     is negative, it is relative to the end of the file.
     </para>
  
     <indexterm>
--- 14509,14547 ----
      <primary>pg_read_file</primary>
     </indexterm>
     <para>
!     <function>pg_read_file</> returns part of a file, starting at the
!     given <parameter>offset</>, returning at most <parameter>length</> bytes
!     (less if the end of file is reached first).  If <parameter>offset</> is
!     negative, it is relative to the end of the file.
!    </para>
!    <para>
!     When the parameter <parameter>length</> is
!     omited, <function>pg_read_binary_file</> reads until the end of the
!     file.
!    </para>
! 
!    <indexterm>
!     <primary>pg_read_binary_file</primary>
!    </indexterm>
!    <para>
!     <function>pg_read_binary_file</> returns part of a text file, starting at the
!     given <parameter>offset</>, returning at most <parameter>length</> bytes
!     (less if the end of file is reached first).  If <parameter>offset</> is
!     negative, it is relative to the end of the
!     file.
! <programlisting>
! SELECT convert_from(pg_read_binary_file('postgresql.conf', -69), 'utf8');
!                                  convert_from                                  
! -------------------------------------------------------------------------------
!  #custom_variable_classes = ''           # list of custom variable class names+
!  
! (1 row)
! </programlisting>
!    </para>
!    <para>
!     When the parameter <parameter>length</> is
!     omited, <function>pg_read_binary_file</> reads until the end of the
!     file.
     </para>
  
     <indexterm>
***************
*** 14499,14504 **** SELECT (pg_stat_file('filename')).modification;
--- 14559,14620 ----
  </programlisting>
     </para>
  
+    <indexterm>
+     <primary>pg_execute_sql_string</primary>
+    </indexterm>
+    <para>
+     <function>pg_execute_sql_string</> makes the server execute
+     given <acronym>SQL</> commands.
+    </para>
+    <para>
+     The function accepts an optional list of placeholder variables as
+     arguments, and will replace any given placeholder name by its value,
+     found in the next parameter. Here's an example:
+ <programlisting>
+ SELECT pg_execute_sql_string('CREATE SCHEMA @schema@;', '@schema@', 'utils');
+  pg_execute_sql_string 
+ -----------------------
+  
+ (1 row)
+ 
+ SELECT pg_execute_sql_string('CREATE SCHEMA s;', 's', 'bar');
+  pg_execute_sql_string 
+ -----------------------
+  
+ (1 row)
+ 
+ SELECT oid, * from pg_namespace where nspname in ('utils', 'bar');
+   oid  | nspname | nspowner | nspacl 
+ -------+---------+----------+--------
+  16387 | utils   |       10 | 
+  16388 | bar     |       10 | 
+ (2 rows)
+ </programlisting>
+    </para>
+ 
+    <indexterm>
+     <primary>pg_execute_sql_file</primary>
+    </indexterm>
+    <para>
+     <function>pg_execute_sql_file</> makes the server
+     execute <acronym>SQL</> commands to be found in a file. This function is
+     reserved to superusers.
+    </para>
+    <para>
+     When the file is known to be encoded the same way as the database, you
+     can leave
+     the <parameter>encoding</parameter> <literal>NULL</>. Otherwise, you
+     need to give the file encoding and <productname>PostgreSQL</> will
+     convert the file content in the database encoding for you.
+    </para>
+    <para>
+     The script might contain placeholders that will be replaced by the
+     values given in the <literal>VARIADIC</literal> arguments, which must be
+     a pair of variable names and values. No specific formating is required
+     as far as placeholder names are concerned, so that you can follow your
+     own policies.
+    </para>
+ 
     <para>
      The functions shown in <xref linkend="functions-advisory-locks"> manage
      advisory locks.  For details about proper use of these functions, see
***************
*** 14521,14526 **** SELECT (pg_stat_file('filename')).modification;
--- 14637,14643 ----
         <entry><type>void</type></entry>
         <entry>Obtain exclusive advisory lock</entry>
        </row>
+ 
        <row>
         <entry>
          <literal><function>pg_advisory_lock(<parameter>key1</> <type>int</>, <parameter>key2</> <type>int</>)</function></literal>
*** a/src/backend/utils/adt/genfile.c
--- b/src/backend/utils/adt/genfile.c
***************
*** 7,12 ****
--- 7,13 ----
   * Copyright (c) 2004-2010, PostgreSQL Global Development Group
   *
   * Author: Andreas Pflug <pgadmin@pse-consulting.de>
+  *         Dimitri Fontaine <dimitri@2ndQuadrant.fr>
   *
   * IDENTIFICATION
   *	  src/backend/utils/adt/genfile.c
***************
*** 21,31 ****
--- 22,34 ----
  #include <dirent.h>
  
  #include "catalog/pg_type.h"
+ #include "executor/spi.h"
  #include "funcapi.h"
  #include "mb/pg_wchar.h"
  #include "miscadmin.h"
  #include "postmaster/syslogger.h"
  #include "storage/fd.h"
+ #include "utils/array.h"
  #include "utils/builtins.h"
  #include "utils/memutils.h"
  #include "utils/timestamp.h"
***************
*** 80,104 **** convert_and_check_filename(text *arg)
  
  
  /*
!  * Read a section of a file, returning it as text
   */
! Datum
! pg_read_file(PG_FUNCTION_ARGS)
  {
! 	text	   *filename_t = PG_GETARG_TEXT_P(0);
! 	int64		seek_offset = PG_GETARG_INT64(1);
! 	int64		bytes_to_read = PG_GETARG_INT64(2);
! 	char	   *buf;
! 	size_t		nbytes;
! 	FILE	   *file;
! 	char	   *filename;
  
  	if (!superuser())
! 		ereport(ERROR,
  				(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
  				 (errmsg("must be superuser to read files"))));
  
! 	filename = convert_and_check_filename(filename_t);
  
  	if ((file = AllocateFile(filename, PG_BINARY_R)) == NULL)
  		ereport(ERROR,
--- 83,119 ----
  
  
  /*
!  * Read a section of a file and returns it as bytea
!  *
!  * Will read all the file if given -1 bytes_to_read, and a negative offset
!  * is applied from the end of the file (SEEK_END)
   */
! static bytea *
! pg_read_binary_file_internal(const char *filename, int64 offset, int64 bytes_to_read)
  {
! 	FILE       *file;
! 	int64       fsize = -1, nbytes;
! 	struct stat fst;
! 	unsigned char *file_content = NULL;
! 	bytea      *retval;
  
+ 	/*
+ 	 * Only superuser can read files.
+ 	 */
  	if (!superuser())
!  		ereport(ERROR,
  				(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
  				 (errmsg("must be superuser to read files"))));
  
! 	if (stat(filename, &fst) < 0)
! 		ereport(ERROR,
! 				(errcode_for_file_access(),
! 				 errmsg("could not stat file \"%s\": %m", filename)));
! 
! 	if (bytes_to_read <= -1)
! 		fsize = Int64GetDatum((int64) fst.st_size);
! 	else
! 		fsize = bytes_to_read;
  
  	if ((file = AllocateFile(filename, PG_BINARY_R)) == NULL)
  		ereport(ERROR,
***************
*** 106,146 **** pg_read_file(PG_FUNCTION_ARGS)
  				 errmsg("could not open file \"%s\" for reading: %m",
  						filename)));
  
! 	if (fseeko(file, (off_t) seek_offset,
! 			   (seek_offset >= 0) ? SEEK_SET : SEEK_END) != 0)
  		ereport(ERROR,
  				(errcode_for_file_access(),
  				 errmsg("could not seek in file \"%s\": %m", filename)));
  
! 	if (bytes_to_read < 0)
  		ereport(ERROR,
! 				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
! 				 errmsg("requested length cannot be negative")));
  
  	/* not sure why anyone thought that int64 length was a good idea */
! 	if (bytes_to_read > (MaxAllocSize - VARHDRSZ))
  		ereport(ERROR,
  				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
  				 errmsg("requested length too large")));
  
! 	buf = palloc((Size) bytes_to_read + VARHDRSZ);
  
! 	nbytes = fread(VARDATA(buf), 1, (size_t) bytes_to_read, file);
  
! 	if (ferror(file))
! 		ereport(ERROR,
! 				(errcode_for_file_access(),
! 				 errmsg("could not read file \"%s\": %m", filename)));
  
  	/* Make sure the input is valid */
! 	pg_verifymbstr(VARDATA(buf), nbytes, false);
  
! 	SET_VARSIZE(buf, nbytes + VARHDRSZ);
  
! 	FreeFile(file);
! 	pfree(filename);
  
! 	PG_RETURN_TEXT_P(buf);
  }
  
  /*
--- 121,226 ----
  				 errmsg("could not open file \"%s\" for reading: %m",
  						filename)));
  
! 	if (fseeko(file, (off_t) offset,
! 			   (offset >= 0) ? SEEK_SET : SEEK_END) != 0)
  		ereport(ERROR,
  				(errcode_for_file_access(),
  				 errmsg("could not seek in file \"%s\": %m", filename)));
  
! 	if (ferror(file))
  		ereport(ERROR,
! 				(errcode_for_file_access(),
! 				 errmsg("could not read file \"%s\": %m", filename)));
  
  	/* not sure why anyone thought that int64 length was a good idea */
! 	if (fsize > (MaxAllocSize - VARHDRSZ))
  		ereport(ERROR,
  				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
  				 errmsg("requested length too large")));
  
! 	file_content = (unsigned char *)palloc0((fsize)*sizeof(char));
! 	nbytes = fread(file_content, 1, (size_t) fsize, file);
! 	FreeFile(file);
  
! 	/*
! 	 * build bytea data type structure.
! 	 */
! 	retval = palloc(nbytes + VARHDRSZ);
! 	SET_VARSIZE(retval, nbytes + VARHDRSZ);
! 	memcpy(VARDATA(retval), file_content, nbytes);
! 	pfree(file_content);
  
! 	return retval;
! }
! 
! /*
!  * Read a section of a file, returning its content as text
!  */
! Datum
! pg_read_file(PG_FUNCTION_ARGS)
! {
! 	text	   *filename_t    = PG_GETARG_TEXT_P(0);
! 	char       *filename      = text_to_cstring(filename_t);
! 	int64		seek_offset   = PG_GETARG_INT64(1);
! 	int64		bytes_to_read = PG_GETARG_INT64(2);
! 	bytea      *content =
! 		pg_read_binary_file_internal(filename, seek_offset, bytes_to_read);
  
  	/* Make sure the input is valid */
! 	pg_verifymbstr(VARDATA(content), VARSIZE(content) - VARHDRSZ, false);
  
! 	/* Abuse knowledge that we're bytea and text are both varlena */
! 	PG_RETURN_TEXT_P(content);
! }
  
! /*
!  * Read till the end of a file, returning its content as text
!  */
! Datum
! pg_read_whole_file(PG_FUNCTION_ARGS)
! {
! 	text	   *filename_t    = PG_GETARG_TEXT_P(0);
! 	char       *filename      = text_to_cstring(filename_t);
! 	int64		seek_offset   = PG_GETARG_INT64(1);
! 	bytea      *content =
! 		pg_read_binary_file_internal(filename, seek_offset, -1);
! 
! 	/* Make sure the input is valid */
! 	pg_verifymbstr(VARDATA(content), VARSIZE(content) - VARHDRSZ, false);
! 
! 	/* Abuse knowledge that we're bytea and text are both varlena */
! 	PG_RETURN_TEXT_P(content);
! }
! 
! /*
!  * Read a section of a file, returning its content as bytea
!  */
! Datum
! pg_read_binary_file(PG_FUNCTION_ARGS)
! {
! 	text	   *filename_t    = PG_GETARG_TEXT_P(0);
! 	char       *filename      = text_to_cstring(filename_t);
! 	int64		seek_offset   = PG_GETARG_INT64(1);
! 	int64		bytes_to_read = PG_GETARG_INT64(2);
! 	bytea      *content =
! 		pg_read_binary_file_internal(filename, seek_offset, bytes_to_read);
! 
! 	PG_RETURN_BYTEA_P(content);
! }
! 
! /*
!  * Read till the end of a file, returning its content as bytea
!  */
! Datum
! pg_read_whole_binary_file(PG_FUNCTION_ARGS)
! {
! 	text	   *filename_t    = PG_GETARG_TEXT_P(0);
! 	char       *filename      = text_to_cstring(filename_t);
! 	int64		seek_offset   = PG_GETARG_INT64(1);
! 	bytea      *content =
! 		pg_read_binary_file_internal(filename, seek_offset, -1);
  
! 	PG_RETURN_BYTEA_P(content);
  }
  
  /*
***************
*** 264,266 **** pg_ls_dir(PG_FUNCTION_ARGS)
--- 344,519 ----
  
  	SRF_RETURN_DONE(funcctx);
  }
+ 
+ /*
+  * The bulk of the execute from file functions, just call SPI to do the real
+  * work
+  */
+ static void
+ pg_execute_internal(const char *filename, const char *query_string)
+ {
+ 	/*
+ 	 * We abuse some internal knowledge from spi.h here. As we don't know
+ 	 * which queries are going to get executed, we don't know what to expect
+ 	 * as an OK return code from SPI_execute().  We assume that
+ 	 * SPI_OK_CONNECT, SPI_OK_FINISH and SPI_OK_FETCH are quite improbable,
+ 	 * though, and the errors are negatives.  So a valid return code is
+ 	 * considered to be SPI_OK_UTILITY or anything from there.
+ 	 */
+ 	if (SPI_connect() != SPI_OK_CONNECT)
+ 		elog(ERROR, "SPI_connect failed");
+ 
+ 	if (SPI_execute(query_string, false, 0) < SPI_OK_UTILITY)
+ 		ereport(ERROR,
+ 				(errcode(ERRCODE_DATA_EXCEPTION),
+ 				 (filename
+ 				  ? errmsg("could not execute sql file: '%s'", filename)
+ 				  : errmsg("could not execute sql string"))));
+ 
+ 	if (SPI_finish() != SPI_OK_FINISH)
+ 		elog(ERROR, "SPI_finish failed");
+ 
+ 	return;
+ }
+ 
+ /*
+  * The SQL callable version of it.
+  */
+ Datum
+ pg_execute_sql_string(PG_FUNCTION_ARGS)
+ {
+ 	text	   *sql_text   = PG_GETARG_TEXT_P(0);
+ 	pg_execute_internal(NULL, text_to_cstring(sql_text));
+ 	PG_RETURN_VOID();
+ }
+ 
+ /*
+  * Execute SQL string containing placeholders.
+  */
+ Datum
+ pg_execute_sql_string_with_placeholders(PG_FUNCTION_ARGS)
+ {
+ 	text	   *src = PG_GETARG_TEXT_P(0);
+ 	ArrayType  *placeholders = PG_GETARG_ARRAYTYPE_P(1);
+ 	char       *sql = text_to_cstring(
+ 		replace_placeholders_internal(src, NULL, NULL, placeholders));
+ 
+ 	/*
+ 	 * pg_execute_internal() error reporting includes filename, unless NULL
+ 	 */
+ 	pg_execute_internal(NULL, sql);
+ 	PG_RETURN_VOID();
+ }
+ 
+ /*
+  * Read a file then execute the SQL commands it contains.
+  */
+ Datum
+ pg_execute_sql_file(PG_FUNCTION_ARGS)
+ {
+ 	text	   *filename_t = PG_GETARG_TEXT_P(0);
+ 	char       *filename   = text_to_cstring(filename_t);
+ 	bytea      *content;
+ 	char       *sql;
+ 
+ 	if (!superuser())
+ 		ereport(ERROR,
+ 				(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ 				 (errmsg("must be superuser to execute sql file"))));
+ 
+ 	content = pg_read_binary_file_internal(filename, 0, -1);
+ 	sql = VARDATA(content);
+ 	pg_verifymbstr(sql, VARSIZE(content) - VARHDRSZ, false);
+ 	pg_execute_internal(filename, sql);
+ 	PG_RETURN_VOID();
+ }
+ 
+ /*
+  * Read a file, convert its encoding to the database encoding, then execute
+  * the SQL commands it contains.
+  */
+ Datum
+ pg_convert_and_execute_sql_file(PG_FUNCTION_ARGS)
+ {
+ 	text	   *filename_t = PG_GETARG_TEXT_P(0);
+ 	char       *filename   = text_to_cstring(filename_t);
+ 	Datum		src_encoding_name = PG_GETARG_DATUM(1);
+ 	Datum		dest_encoding_name =
+ 		DirectFunctionCall1(namein,
+ 							CStringGetDatum(GetDatabaseEncodingName()));
+ 	bytea      *content;
+ 	char       *sql;
+ 
+ 	if (!superuser())
+ 		ereport(ERROR,
+ 				(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ 				 (errmsg("must be superuser to execute sql file"))));
+ 
+ 	content = pg_read_binary_file_internal(filename, 0, -1);
+ 
+ 	if (!PG_ARGISNULL(1))
+ 	{
+ 		sql = text_to_cstring(
+ 			DatumGetTextP(
+ 				DirectFunctionCall3(pg_convert, PointerGetDatum(content),
+ 									src_encoding_name, dest_encoding_name)));
+ 	}
+ 	else
+ 	{
+ 		sql = VARDATA(content);
+ 		pg_verifymbstr(sql, VARSIZE(content) - VARHDRSZ, false);
+ 	}
+ 	pg_execute_internal(filename, sql);
+ 	PG_RETURN_VOID();
+ }
+ 
+ /*
+  * Variant accepting a VARIADIC text parameter containing placeholder
+  * variables and values, one after the other (so the variadic array length
+  * must be even).
+  *
+  * The main use case of the replacement facility is for setting the
+  * extension's schema, using @pg_extschema@ variable and a user given
+  * schema.
+  *
+  * This could be implemented in a single function together with the previous
+  * pg_execute_sql_file, if only it was possible to fill in the
+  * proargdefaults pg_proc column from the backend code.
+  */
+ Datum
+ pg_execute_sql_file_with_placeholders(PG_FUNCTION_ARGS)
+ {
+ 	text	   *filename_t   = PG_GETARG_TEXT_P(0);
+ 	ArrayType  *placeholders = PG_GETARG_ARRAYTYPE_P(2);
+ 	char       *filename     = text_to_cstring(filename_t);
+ 	Datum		src_encoding_name = PG_GETARG_DATUM(1);
+ 	Datum		dest_encoding_name =
+ 		DirectFunctionCall1(namein,
+ 							CStringGetDatum(GetDatabaseEncodingName()));
+ 	bytea      *content;
+ 	text       *sql;
+ 
+ 	if (!superuser())
+ 		ereport(ERROR,
+ 				(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ 				 (errmsg("must be superuser to execute sql file"))));
+ 
+ 	content = pg_read_binary_file_internal(filename, 0, -1);
+ 
+ 	if (!PG_ARGISNULL(1))
+ 	{
+ 		sql = DatumGetTextP(
+ 			DirectFunctionCall3(pg_convert, PointerGetDatum(content),
+ 								src_encoding_name, dest_encoding_name));
+ 	}
+ 	else
+ 	{
+ 		/* text and bytea are made the same, they are both varlena */
+ 		sql = content;
+ 		pg_verifymbstr(VARDATA(sql), VARSIZE(content) - VARHDRSZ, false);
+ 	}
+ 
+ 	pg_execute_internal(filename,
+ 						replace_placeholders_internal(sql, NULL, NULL, placeholders));
+ 	PG_RETURN_VOID();
+ }
*** a/src/backend/utils/adt/varlena.c
--- b/src/backend/utils/adt/varlena.c
***************
*** 24,29 ****
--- 24,30 ----
  #include "miscadmin.h"
  #include "parser/scansup.h"
  #include "regex/regex.h"
+ #include "utils/array.h"
  #include "utils/builtins.h"
  #include "utils/bytea.h"
  #include "utils/lsyscache.h"
***************
*** 2604,2609 **** replace_text(PG_FUNCTION_ARGS)
--- 2605,2672 ----
  }
  
  /*
+  * Given an array of repeated {variable, value}, replaces the placeholders
+  * by their values in the given query_string, by calling replace_text over
+  * each pair of arguments.
+  *
+  * Given restrictions in how we handle polymorphic functions in pg_proc.h,
+  * the first placeholder is not in the VARIADIC text[] argument.
+  */
+ text *
+ replace_placeholders_internal(const text *src,
+ 							  const text *first_ph_name,
+ 							  const text *first_ph_value,
+ 							  ArrayType *placeholders)
+ {
+ 	Datum	    retval;
+ 	Datum	   *replacements;
+ 	int			nrep;
+ 	int			i;
+ 
+ 	Assert(ARR_ELEMTYPE(placeholders) == TEXTOID);
+ 
+ 	deconstruct_array(placeholders, TEXTOID, -1, false, 'i',
+ 					  &replacements, NULL, &nrep);
+ 
+ 	if (nrep % 2 != 0)
+ 		ereport(ERROR,
+ 				(errmsg("Expected pairs of variable names and values"),
+ 				 errdetail("Please give an even number of replacement parameters")));
+ 
+ 	/*
+ 	 * That's simpler for call site having directly a VARIADIC argument,
+ 	 * such as the pg_execute*_with_placeholders variants.
+ 	 */
+ 	if (first_ph_name != NULL)
+ 		retval = DirectFunctionCall3(replace_text,
+ 									 PointerGetDatum(src),
+ 									 PointerGetDatum(first_ph_name),
+ 									 PointerGetDatum(first_ph_value));
+ 	else
+ 		retval = PointerGetDatum(src);
+ 
+ 	for (i = 0; i < nrep; i+=2)
+ 		retval = DirectFunctionCall3(replace_text,
+ 									 retval,
+ 									 replacements[i], replacements[i+1]);
+ 	return DatumGetTextP(retval);
+ }
+ 
+ Datum
+ replace_text_variadic(PG_FUNCTION_ARGS)
+ {
+ 	text	   *src = PG_GETARG_TEXT_P(0);
+ 	text	   *from_sub_text = PG_GETARG_TEXT_PP(1);
+ 	text	   *to_sub_text = PG_GETARG_TEXT_PP(2);
+ 	ArrayType  *placeholders = PG_GETARG_ARRAYTYPE_P(3);
+ 
+ 	PG_RETURN_TEXT_P(
+ 		replace_placeholders_internal(src,
+ 									  from_sub_text, to_sub_text,
+ 									  placeholders));
+ }
+ 
+ /*
   * check_replace_text_has_escape_char
   *
   * check whether replace_text contains escape char.
*** a/src/include/catalog/pg_proc.h
--- b/src/include/catalog/pg_proc.h
***************
*** 2255,2260 **** DATA(insert OID =  937 (  substring    PGNSP PGUID 12 1 0 0 f f f t f i 2 0 25 "
--- 2255,2262 ----
  DESCR("return portion of string");
  DATA(insert OID =  2087 ( replace	   PGNSP PGUID 12 1 0 0 f f f t f i 3 0 25 "25 25 25" _null_ _null_ _null_ _null_	replace_text _null_ _null_ _null_ ));
  DESCR("replace all occurrences in string of old_substr with new_substr");
+ DATA(insert OID = 3931  ( replace	   PGNSP PGUID 12 1 0 25 f f f t f v 4 0 25 "25 25 25 25" "{25,25,25,25}" "{i,i,i,v}" _null_ _null_ replace_text_variadic _null_ _null_ _null_ ));
+ DESCR("replace all occurrences in string of old_substr with new_substr");
  DATA(insert OID =  2284 ( regexp_replace	   PGNSP PGUID 12 1 0 0 f f f t f i 3 0 25 "25 25 25" _null_ _null_ _null_ _null_	textregexreplace_noopt _null_ _null_ _null_ ));
  DESCR("replace text using regexp");
  DATA(insert OID =  2285 ( regexp_replace	   PGNSP PGUID 12 1 0 0 f f f t f i 4 0 25 "25 25 25 25" _null_ _null_ _null_ _null_ textregexreplace _null_ _null_ _null_ ));
***************
*** 3399,3412 **** DESCR("reload configuration files");
  DATA(insert OID = 2622 ( pg_rotate_logfile		PGNSP PGUID 12 1 0 0 f f f t f v 0 0 16 "" _null_ _null_ _null_ _null_ pg_rotate_logfile _null_ _null_ _null_ ));
  DESCR("rotate log file");
  
! DATA(insert OID = 2623 ( pg_stat_file		PGNSP PGUID 12 1 0 0 f f f t f v 1 0 2249 "25" "{25,20,1184,1184,1184,1184,16}" "{i,o,o,o,o,o,o}" "{filename,size,access,modification,change,creation,isdir}" _null_ pg_stat_file _null_ _null_ _null_ ));
  DESCR("return file information");
! DATA(insert OID = 2624 ( pg_read_file		PGNSP PGUID 12 1 0 0 f f f t f v 3 0 25 "25 20 20" _null_ _null_ _null_ _null_ pg_read_file _null_ _null_ _null_ ));
  DESCR("read text from a file");
! DATA(insert OID = 2625 ( pg_ls_dir			PGNSP PGUID 12 1 1000 0 f f f t t v 1 0 25 "25" _null_ _null_ _null_ _null_ pg_ls_dir _null_ _null_ _null_ ));
  DESCR("list all files in a directory");
! DATA(insert OID = 2626 ( pg_sleep			PGNSP PGUID 12 1 0 0 f f f t f v 1 0 2278 "701" _null_ _null_ _null_ _null_ pg_sleep _null_ _null_ _null_ ));
  DESCR("sleep for the specified time in seconds");
  
  DATA(insert OID = 2971 (  text				PGNSP PGUID 12 1 0 0 f f f t f i 1 0 25 "16" _null_ _null_ _null_ _null_ booltext _null_ _null_ _null_ ));
  DESCR("convert boolean to text");
--- 3401,3430 ----
  DATA(insert OID = 2622 ( pg_rotate_logfile		PGNSP PGUID 12 1 0 0 f f f t f v 0 0 16 "" _null_ _null_ _null_ _null_ pg_rotate_logfile _null_ _null_ _null_ ));
  DESCR("rotate log file");
  
! DATA(insert OID = 2623 ( pg_stat_file			PGNSP PGUID 12 1 0 0 f f f t f v 1 0 2249 "25" "{25,20,1184,1184,1184,1184,16}" "{i,o,o,o,o,o,o}" "{filename,size,access,modification,change,creation,isdir}" _null_ pg_stat_file _null_ _null_ _null_ ));
  DESCR("return file information");
! DATA(insert OID = 2624 ( pg_read_file			PGNSP PGUID 12 1 0 0 f f f t f v 3 0 25 "25 20 20" _null_ _null_ _null_ _null_ pg_read_file _null_ _null_ _null_ ));
! DESCR("read text from a file");
! DATA(insert OID = 3935 ( pg_read_file			PGNSP PGUID 12 1 0 0 f f f t f v 2 0 25 "25 20" _null_ _null_ _null_ _null_ pg_read_whole_file _null_ _null_ _null_ ));
  DESCR("read text from a file");
! DATA(insert OID = 2625 ( pg_ls_dir				PGNSP PGUID 12 1 1000 0 f f f t t v 1 0 25 "25" _null_ _null_ _null_ _null_ pg_ls_dir _null_ _null_ _null_ ));
  DESCR("list all files in a directory");
! DATA(insert OID = 2626 ( pg_sleep				PGNSP PGUID 12 1 0 0 f f f t f v 1 0 2278 "701" _null_ _null_ _null_ _null_ pg_sleep _null_ _null_ _null_ ));
  DESCR("sleep for the specified time in seconds");
+ DATA(insert OID = 3930 ( pg_read_binary_file	PGNSP PGUID 12 1 0 0 f f f t f v 3 0 17 "25 20 20" _null_ _null_ _null_ _null_ pg_read_binary_file _null_ _null_ _null_ ));
+ DESCR("read bytea from a file");
+ DATA(insert OID = 3936 ( pg_read_binary_file	PGNSP PGUID 12 1 0 0 f f f t f v 2 0 17 "25 20" _null_ _null_ _null_ _null_ pg_read_whole_binary_file _null_ _null_ _null_ ));
+ DESCR("read bytea from a file");
+ DATA(insert OID = 3932 ( pg_execute_sql_string	PGNSP PGUID 12 1 0 0 f f f t f v 1 0 2278 "25" _null_ _null_ _null_ _null_ pg_execute_sql_string _null_ _null_ _null_ ));
+ DESCR("execute queries read from a string");
+ DATA(insert OID = 3933 ( pg_execute_sql_string	PGNSP PGUID 12 1 0 25 f f f t f v 2 0 2278 "25 25" "{25,25}" "{i,v}" _null_ _null_ pg_execute_sql_string_with_placeholders _null_ _null_ _null_ ));
+ DESCR("execute queries read from a string");
+ DATA(insert OID = 3927 ( pg_execute_sql_file	PGNSP PGUID 12 1 0 0 f f f t f v 1 0 2278 "25" _null_ _null_ _null_ _null_ pg_execute_sql_file _null_ _null_ _null_ ));
+ DESCR("execute queries read from a file");
+ DATA(insert OID = 3934 ( pg_execute_sql_file	PGNSP PGUID 12 1 0 0 f f f t f v 2 0 2278 "25 19" _null_ _null_ _null_ _null_ pg_convert_and_execute_sql_file _null_ _null_ _null_ ));
+ DESCR("execute queries read from a file");
+ DATA(insert OID = 3928 ( pg_execute_sql_file	PGNSP PGUID 12 1 0 25 f f f t f v 3 0 2278 "25 19 25" "{25,19,25}" "{i,i,v}" _null_ _null_ pg_execute_sql_file_with_placeholders _null_ _null_ _null_ ));
+ DESCR("execute queries read from a file");
  
  DATA(insert OID = 2971 (  text				PGNSP PGUID 12 1 0 0 f f f t f i 1 0 25 "16" _null_ _null_ _null_ _null_ booltext _null_ _null_ _null_ ));
  DESCR("convert boolean to text");
*** a/src/include/utils/builtins.h
--- b/src/include/utils/builtins.h
***************
*** 442,448 **** extern Datum pg_relation_filepath(PG_FUNCTION_ARGS);
--- 442,457 ----
  /* genfile.c */
  extern Datum pg_stat_file(PG_FUNCTION_ARGS);
  extern Datum pg_read_file(PG_FUNCTION_ARGS);
+ extern Datum pg_read_whole_file(PG_FUNCTION_ARGS);
+ extern Datum pg_read_binary_file(PG_FUNCTION_ARGS);
+ extern Datum pg_read_whole_binary_file(PG_FUNCTION_ARGS);
  extern Datum pg_ls_dir(PG_FUNCTION_ARGS);
+ extern Datum replace_placeholders(PG_FUNCTION_ARGS);
+ extern Datum pg_execute_sql_string(PG_FUNCTION_ARGS);
+ extern Datum pg_execute_sql_string_with_placeholders(PG_FUNCTION_ARGS);
+ extern Datum pg_execute_sql_file(PG_FUNCTION_ARGS);
+ extern Datum pg_convert_and_execute_sql_file(PG_FUNCTION_ARGS);
+ extern Datum pg_execute_sql_file_with_placeholders (PG_FUNCTION_ARGS);
  
  /* misc.c */
  extern Datum current_database(PG_FUNCTION_ARGS);
***************
*** 680,685 **** extern text *cstring_to_text(const char *s);
--- 689,698 ----
  extern text *cstring_to_text_with_len(const char *s, int len);
  extern char *text_to_cstring(const text *t);
  extern void text_to_cstring_buffer(const text *src, char *dst, size_t dst_len);
+ extern text *replace_placeholders_internal(const text *src,
+ 										   const text *first_ph_name,
+ 										   const text *first_ph_value,
+ 										   ArrayType *placeholders);
  
  #define CStringGetTextDatum(s) PointerGetDatum(cstring_to_text(s))
  #define TextDatumGetCString(d) text_to_cstring((text *) DatumGetPointer(d))
***************
*** 716,721 **** extern List *textToQualifiedNameList(text *textval);
--- 729,735 ----
  extern bool SplitIdentifierString(char *rawstring, char separator,
  					  List **namelist);
  extern Datum replace_text(PG_FUNCTION_ARGS);
+ extern Datum replace_text_variadic(PG_FUNCTION_ARGS);
  extern text *replace_text_regexp(text *src_text, void *regexp,
  					text *replace_text, bool glob);
  extern Datum split_text(PG_FUNCTION_ARGS);