Hi,
Please find attached the v8 version of the patch, that fixes the following:
Itagaki Takahiro <itagaki.takah...@gmail.com> writes:
> * pg_read_binary_file_internal() should return not only the contents
> as char * but also the length, because the file might contain 0x00.
> In addition, null-terminations for the contents buffer is useless.
>
> * The 1st argument of pg_convert must be bytea rather than cstring in
> pg_convert_and_execute_sql_file(). I think you can fix both the bug
> and the above one if pg_read_binary_file_internal() returns bytea.
I've changed pg_read_binary_file_internal() to return bytea*, which is
much cleaner, thanks for the suggestion!
> * pg_read_file() has stronger restrictions than pg_read_binary_file().
> (absolute path not allowed) and -1 length is not supported.
> We could fix pg_read_file() to behave as like as pg_read_binary_file().
It's now using the _internal function directly, so that there's only one
code definition to care about here.
> * (It was my suggestion, but) Is it reasonable to use -1 length to read
> the while file? It might fit with C, but NULL might be better for SQL.
Well thinking about it, omitting the length parameter alltogether seems
like the more natural SQL level API for me, so I've made it happen:
=# \df pg_read_*|replace|pg_exe*
List of functions
Schema | Name | Result data type | Argument data
types | Type
------------+-----------------------+------------------+---------------------------------+--------
pg_catalog | pg_execute_sql_file | void | text
| normal
pg_catalog | pg_execute_sql_file | void | text, name
| normal
pg_catalog | pg_execute_sql_file | void | text, name, VARIADIC
text | normal
pg_catalog | pg_execute_sql_string | void | text
| normal
pg_catalog | pg_execute_sql_string | void | text, VARIADIC text
| normal
pg_catalog | pg_read_binary_file | bytea | text, bigint
| normal
pg_catalog | pg_read_binary_file | bytea | text, bigint, bigint
| normal
pg_catalog | pg_read_file | text | text, bigint
| normal
pg_catalog | pg_read_file | text | text, bigint, bigint
| normal
pg_catalog | replace | text | text, text, text
| normal
pg_catalog | replace | text | text, text, text,
VARIADIC text | normal
(11 rows)
> * The doc says pg_execute_sql_string() is restricted for superusers,
> but is not restricted actually. I think we don't have to.
Agreed, fixed the doc.
> * In docs, the example of replace_placeholders() is
> replace('abcdefabcdef', 'cd', 'XX', 'ef', 'YY').
> ~~~~~~~
> BTW, I think we can call it just "replace" because parser can handle
> them correctly even if we have both replace(text, text, text) and
> replace(text, VARIADIC text[]). We will need only one doc for them.
> Note that if we call replace() with 3 args, the non-VARIADIC version
> is called. So, there is no performance penalty.
The same idea occured to me yesternight when reading through the patch
to send. It's now done in the way you can see above. The idea is not to
change the existing behavior at all, so I've not changed the
non-VARIADIC version of the function.
> * We might rename pg_convert_and_execute_sql_file() to
> pg_execute_sql_file_with_encoding() or so to have the same prefix.
Well, I think I prefer reading the verbs in the order that things are
happening in the code, it's actually convert then execute. But again,
maybe some Native Speaker will have a say here, or maybe your proposed
name fits better in PostgreSQL. I'd leave it for commiter :)
Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
*** 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 <pgad...@pse-consulting.de>
+ * Dimitri Fontaine <dimi...@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);
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
