diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 21f1ddf..b43e46e 100644 *** a/doc/src/sgml/func.sgml --- b/doc/src/sgml/func.sgml *************** *** 1840,1846 **** replace(string text, from text, ! to text) text Replace all occurrences in string of substring --- 1840,1846 ---- replace(string text, from text, ! to text [, ...]) text Replace all occurrences in string of substring *************** postgres=# SELECT * FROM pg_xlogfile_nam *** 14449,14466 **** ! pg_read_file(filename text, offset bigint, length bigint) text Return the contents of a text file pg_stat_file(filename text) record Return information about a file --- 14449,14494 ---- ! pg_read_file(filename text, offset bigint [, length bigint]) text Return the contents of a text file + pg_read_binary_file(filename text, offset bigint [, length bigint]) + + bytea + Return the contents of a file + + + pg_stat_file(filename text) record Return information about a file + + + pg_execute_sql_string(sql text + [, variable text, value text + [, ...] ]) ) + + void + Execute the given string as SQL commands, replacing placeholders, if any. + + + + pg_execute_sql_file(filename text + [ [, encoding name] + [, variable text, value text + [, ...] ] ]) ) + + void + Execute contents of the given file as SQL commands, + expected in either database encoding or given encoding, and replacing + given placeholders, if any. + *************** postgres=# SELECT * FROM pg_xlogfile_nam *** 14482,14487 **** --- 14510,14539 ---- at the given offset, returning at most length bytes (less if the end of file is reached first). If offset is negative, it is relative to the end of the file. + When the parameter length is omitted, + pg_read_file reads until the end of the file. + The part of a file must be a valid text in the server encoding. + + + + pg_read_binary_file + + + pg_read_binary_file returns part of a file as like as + pg_read_file, but the result is a bytea value. + + SELECT convert_from(pg_read_binary_file('postgresql.conf', -69), 'utf8'); + convert_from + ------------------------------------------------------------------------------- + #custom_variable_classes = '' # list of custom variable class names+ + + (1 row) + + + + When the parameter length is + omited, pg_read_binary_file reads until the end of the + file. *************** SELECT (pg_stat_file('filename')).modifi *** 14499,14504 **** --- 14551,14598 ---- + + pg_execute_sql_string + + + pg_execute_sql_string makes the server execute the given string + as SQL commands. + The script may contain placeholders that will be replaced by the optional + parameters, that are pairs of variable names and values. Here's an example: + + 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) + + + + + pg_execute_sql_file + + + pg_execute_sql_file makes the server execute contents in a file + as SQL commands. You can give an encoding name of the file to the + function. If omitted, the server encoding is used. + The script may contain placeholders that will be replaced by the optional + parameters, that are pairs of variable names and values. + Use of the function is restricted to superusers. + + The functions shown in manage advisory locks. For details about proper use of these functions, see diff --git a/src/backend/utils/adt/genfile.c b/src/backend/utils/adt/genfile.c index e8a36ed..3a327b6 100644 *** a/src/backend/utils/adt/genfile.c --- b/src/backend/utils/adt/genfile.c *************** *** 21,31 **** --- 21,33 ---- #include #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" *************** convert_and_check_filename(text *arg) *** 80,104 **** /* ! * 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, --- 82,126 ---- /* ! * Read a section of a file, returning it as bytea ! * ! * Will read all the file if given a nagative bytes_to_read, and a negative ! * offset is applied from the end of the file (SEEK_END) */ ! static bytea * ! read_binary_file(const char *filename, int64 offset, int64 bytes_to_read) { ! FILE *file; ! bytea *buf; ! int64 nbytes; + /* Only superuser can read files. */ if (!superuser()) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), (errmsg("must be superuser to read files")))); ! if (bytes_to_read >= 0) ! nbytes = bytes_to_read; ! else if (offset < 0) ! nbytes = -offset; ! else ! { ! struct stat fst; ! ! if (stat(filename, &fst) < 0) ! ereport(ERROR, ! (errcode_for_file_access(), ! errmsg("could not stat file \"%s\": %m", filename))); ! ! nbytes = fst.st_size; ! } ! ! /* not sure why anyone thought that int64 length was a good idea */ ! if (nbytes > (MaxAllocSize - VARHDRSZ)) ! ereport(ERROR, ! (errcode(ERRCODE_INVALID_PARAMETER_VALUE), ! errmsg("requested length too large"))); if ((file = AllocateFile(filename, PG_BINARY_R)) == NULL) ereport(ERROR, *************** pg_read_file(PG_FUNCTION_ARGS) *** 106,146 **** 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); } /* --- 128,228 ---- 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))); + buf = (bytea *) palloc((Size) nbytes + VARHDRSZ); + nbytes = fread(VARDATA(buf), 1, (size_t) nbytes, file); + + if (ferror(file)) + ereport(ERROR, + (errcode_for_file_access(), + errmsg("could not read file \"%s\": %m", filename))); + + FreeFile(file); + + SET_VARSIZE(buf, nbytes + VARHDRSZ); + + return buf; + } + + /* + * In addition to read_binary_file, verify the contents are encoded in the + * database encoding. + */ + static text * + read_text_file(const char *filename, int64 seek_offset, int64 bytes_to_read) + { + bytea *content = read_binary_file(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 */ + return (text *) content; + } + + /* + * Read a section of a file, returning its content as text + */ + Datum + pg_read_file(PG_FUNCTION_ARGS) + { + char *filename = text_to_cstring(PG_GETARG_TEXT_PP(0)); + int64 seek_offset = PG_GETARG_INT64(1); + int64 bytes_to_read = PG_GETARG_INT64(2); + if (bytes_to_read < 0) ereport(ERROR, (errcode(ERRCODE_INVALID_PARAMETER_VALUE), errmsg("requested length cannot be negative"))); ! PG_RETURN_TEXT_P(read_text_file(filename, seek_offset, bytes_to_read)); ! } ! /* ! * Read till the end of a file, returning its content as text ! */ ! Datum ! pg_read_whole_file(PG_FUNCTION_ARGS) ! { ! char *filename = text_to_cstring(PG_GETARG_TEXT_PP(0)); ! int64 seek_offset = PG_GETARG_INT64(1); ! PG_RETURN_TEXT_P(read_text_file(filename, seek_offset, -1)); ! } ! /* ! * Read a section of a file, returning its content as bytea ! */ ! Datum ! pg_read_binary_file(PG_FUNCTION_ARGS) ! { ! char *filename = text_to_cstring(PG_GETARG_TEXT_PP(0)); ! int64 seek_offset = PG_GETARG_INT64(1); ! int64 bytes_to_read = PG_GETARG_INT64(2); ! if (bytes_to_read < 0) ! ereport(ERROR, ! (errcode(ERRCODE_INVALID_PARAMETER_VALUE), ! errmsg("requested length cannot be negative"))); ! PG_RETURN_BYTEA_P(read_binary_file(filename, seek_offset, bytes_to_read)); ! } ! /* ! * Read till the end of a file, returning its content as bytea ! */ ! Datum ! pg_read_whole_binary_file(PG_FUNCTION_ARGS) ! { ! char *filename = text_to_cstring(PG_GETARG_TEXT_PP(0)); ! int64 seek_offset = PG_GETARG_INT64(1); ! PG_RETURN_BYTEA_P(read_binary_file(filename, seek_offset, -1)); } /* *************** pg_read_file(PG_FUNCTION_ARGS) *** 149,155 **** Datum pg_stat_file(PG_FUNCTION_ARGS) { ! text *filename_t = PG_GETARG_TEXT_P(0); char *filename; struct stat fst; Datum values[6]; --- 231,237 ---- Datum pg_stat_file(PG_FUNCTION_ARGS) { ! text *filename_t = PG_GETARG_TEXT_PP(0); char *filename; struct stat fst; Datum values[6]; *************** pg_ls_dir(PG_FUNCTION_ARGS) *** 234,240 **** oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx); fctx = palloc(sizeof(directory_fctx)); ! fctx->location = convert_and_check_filename(PG_GETARG_TEXT_P(0)); fctx->dirdesc = AllocateDir(fctx->location); --- 316,322 ---- oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx); fctx = palloc(sizeof(directory_fctx)); ! fctx->location = convert_and_check_filename(PG_GETARG_TEXT_PP(0)); fctx->dirdesc = AllocateDir(fctx->location); *************** pg_ls_dir(PG_FUNCTION_ARGS) *** 264,266 **** --- 346,492 ---- SRF_RETURN_DONE(funcctx); } + + /* + * Replace placeholders in the sql text and execute it. + */ + static void + execute_sql_string(const char *filename, text *sql, ArrayType *placeholders) + { + /* replace placeholders */ + if (placeholders != NULL) + sql = DatumGetTextP(DirectFunctionCall2(replace_text_variadic, + PointerGetDatum(sql), + PointerGetDatum(placeholders))); + + /* + * 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(text_to_cstring(sql), false, 0) < SPI_OK_UTILITY) + ereport(ERROR, + (errcode(ERRCODE_DATA_EXCEPTION), + (filename != NULL + ? 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"); + } + + static void + execute_sql_file(const char *filename, + const char *encoding_name, + ArrayType *placeholders) + { + int src_encoding; + int dest_encoding = GetDatabaseEncoding(); + bytea *content; + text *sql; + const char *src_str; + char *dest_str; + int len; + + /* use database encoding if not given */ + if (encoding_name == NULL) + src_encoding = dest_encoding; + else + src_encoding = pg_char_to_encoding(encoding_name); + + if (src_encoding < 0) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("invalid source encoding name \"%s\"", + encoding_name))); + + /* superuser checks will be done in reading files */ + content = read_binary_file(filename, 0, -1); + + /* make sure that source string is valid */ + len = VARSIZE_ANY_EXHDR(content); + src_str = VARDATA_ANY(content); + pg_verify_mbstr_len(src_encoding, src_str, len, false); + + /* convert the encoding to the database encoding */ + dest_str = (char *) pg_do_encoding_conversion( + (unsigned char *) src_str, len, src_encoding, dest_encoding); + if (dest_str != src_str) + sql = cstring_to_text(dest_str); + else + sql = (text *) content; + + execute_sql_string(filename, sql, placeholders); + } + + /* + * Execute SQL string. + */ + Datum + pg_execute_sql_string(PG_FUNCTION_ARGS) + { + text *sql = PG_GETARG_TEXT_PP(0); + + execute_sql_string(NULL, sql, NULL); + PG_RETURN_VOID(); + } + + /* + * Execute SQL string containing placeholders. + */ + Datum + pg_execute_sql_string_with_placeholders(PG_FUNCTION_ARGS) + { + text *sql = PG_GETARG_TEXT_PP(0); + ArrayType *placeholders = PG_GETARG_ARRAYTYPE_P(1); + + execute_sql_string(NULL, sql, placeholders); + PG_RETURN_VOID(); + } + + /* + * Read a file, and execute the contents as SQL commands. + */ + Datum + pg_execute_sql_file(PG_FUNCTION_ARGS) + { + char *filename = text_to_cstring(PG_GETARG_TEXT_PP(0)); + + execute_sql_file(filename, NULL, NULL); + PG_RETURN_VOID(); + } + + /* + * In addition to pg_execute_sql_file, convert the encoding of the contents + * to the database encoding. + */ + Datum + pg_convert_and_execute_sql_file(PG_FUNCTION_ARGS) + { + char *filename = text_to_cstring(PG_GETARG_TEXT_PP(0)); + char *encoding_name = NameStr(*PG_GETARG_NAME(1)); + + execute_sql_file(filename, encoding_name, NULL); + PG_RETURN_VOID(); + } + + /* + * In addition to pg_convert_and_execute_sql_file, replace placeholder + * variables to given values in the contents. + */ + Datum + pg_execute_sql_file_with_placeholders(PG_FUNCTION_ARGS) + { + char *filename = text_to_cstring(PG_GETARG_TEXT_PP(0)); + char *encoding_name = NameStr(*PG_GETARG_NAME(1)); + ArrayType *placeholders = PG_GETARG_ARRAYTYPE_P(2); + + execute_sql_file(filename, encoding_name, placeholders); + PG_RETURN_VOID(); + } diff --git a/src/backend/utils/adt/varlena.c b/src/backend/utils/adt/varlena.c index ee83e2f..33683e9 100644 *** 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" *************** replace_text(PG_FUNCTION_ARGS) *** 2604,2609 **** --- 2605,2640 ---- } /* + * Given an array of repeated {FROM, TO} pairs, replaces each FROM value + * in the given string to the corresponding TO value. + */ + Datum + replace_text_variadic(PG_FUNCTION_ARGS) + { + Datum str = PG_GETARG_DATUM(0); + ArrayType *arr_pairs = PG_GETARG_ARRAYTYPE_P(1); + Datum *pairs; + int len; + int i; + + Assert(ARR_ELEMTYPE(arr_pairs) == TEXTOID); + + deconstruct_array(arr_pairs, TEXTOID, -1, false, 'i', + &pairs, NULL, &len); + + if (len % 2 != 0) + ereport(ERROR, + (errmsg("number of replacement parameters (%d) must be even", + len))); + + /* call replace_text over each pair of arguments */ + for (i = 0; i < len; i += 2) + str = DirectFunctionCall3(replace_text, str, pairs[i], pairs[i + 1]); + + PG_RETURN_DATUM(str); + } + + /* * check_replace_text_has_escape_char * * check whether replace_text contains escape char. diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h index 611adef..ff640af 100644 *** a/src/include/catalog/pg_proc.h --- b/src/include/catalog/pg_proc.h *************** DATA(insert OID = 937 ( substring P *** 2255,2260 **** --- 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 2 0 25 "25 25" "{25,25}" "{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_ )); *************** DESCR("reload configuration files"); *** 3399,3412 **** 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"); diff --git a/src/include/utils/builtins.h b/src/include/utils/builtins.h index a2fb749..0c45546 100644 *** a/src/include/utils/builtins.h --- b/src/include/utils/builtins.h *************** extern Datum pg_relation_filepath(PG_FUN *** 442,448 **** --- 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); *************** extern List *textToQualifiedNameList(tex *** 716,721 **** --- 725,731 ---- 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); diff --git a/src/test/regress/expected/strings.out b/src/test/regress/expected/strings.out index 1bd6772..8cd3242 100644 *** a/src/test/regress/expected/strings.out --- b/src/test/regress/expected/strings.out *************** SELECT replace('yabadoo', 'bad', '') AS *** 1227,1232 **** --- 1227,1238 ---- yaoo (1 row) + SELECT replace('yabadabadoo', 'ba', '123', 'a', 'X') AS "yX123dX123doo"; + yX123dX123doo + --------------- + yX123dX123doo + (1 row) + -- -- test split_part -- *************** SELECT encode(overlay(E'Th\\000omas'::by *** 1603,1605 **** --- 1609,1630 ---- Th\000o\x02\x03 (1 row) + -- + -- test execute + -- + SELECT pg_execute_sql_string($do$ DO $$BEGIN RAISE INFO 'foo'; END;$$ $do$); + INFO: foo + CONTEXT: SQL statement " DO $$BEGIN RAISE INFO 'foo'; END;$$ " + pg_execute_sql_string + ----------------------- + + (1 row) + + SELECT pg_execute_sql_string($do$ DO $$BEGIN RAISE INFO 'foo'; END;$$ $do$, 'foo', 'bar'); + INFO: bar + CONTEXT: SQL statement " DO $$BEGIN RAISE INFO 'bar'; END;$$ " + pg_execute_sql_string + ----------------------- + + (1 row) + diff --git a/src/test/regress/sql/strings.sql b/src/test/regress/sql/strings.sql index 6ef4463..aa56739 100644 *** a/src/test/regress/sql/strings.sql --- b/src/test/regress/sql/strings.sql *************** SELECT replace('yabadabadoo', 'ba', '123 *** 430,435 **** --- 430,437 ---- SELECT replace('yabadoo', 'bad', '') AS "yaoo"; + SELECT replace('yabadabadoo', 'ba', '123', 'a', 'X') AS "yX123dX123doo"; + -- -- test split_part -- *************** SELECT btrim(E'\\000trim\\000'::bytea, ' *** 552,554 **** --- 554,562 ---- SELECT encode(overlay(E'Th\\000omas'::bytea placing E'Th\\001omas'::bytea from 2),'escape'); SELECT encode(overlay(E'Th\\000omas'::bytea placing E'\\002\\003'::bytea from 8),'escape'); SELECT encode(overlay(E'Th\\000omas'::bytea placing E'\\002\\003'::bytea from 5 for 3),'escape'); + + -- + -- test execute + -- + SELECT pg_execute_sql_string($do$ DO $$BEGIN RAISE INFO 'foo'; END;$$ $do$); + SELECT pg_execute_sql_string($do$ DO $$BEGIN RAISE INFO 'foo'; END;$$ $do$, 'foo', 'bar');