Dimitri Fontaine <dimi...@2ndquadrant.fr> writes: > So, you will find two new branches for those purposes at the repository, > named cfparser and pg_execute_from_file: > > http://git.postgresql.org/gitweb?p=postgresql-extension.git;a=summary
I updated the pg_execute_from_file branch with documentation for the function, and added documentation (catalog, functions, custom classes) to the main feature branch too. The cfparser patch didn't change, the current version is still v1. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
extension.v2.patch.gz
Description: extension, v2, more docs
*** a/doc/src/sgml/func.sgml
--- b/doc/src/sgml/func.sgml
***************
*** 13895,13900 **** postgres=# SELECT * FROM pg_xlogfile_name_offset(pg_stop_backup());
--- 13895,13907 ----
<entry><type>record</type></entry>
<entry>Return information about a file</entry>
</row>
+ <row>
+ <entry>
+ <literal><function>pg_execute_from_file(<parameter>filename</> <type>text</>)</function></literal>
+ </entry>
+ <entry><type>void</type></entry>
+ <entry>Executes the <acronym>SQL</> commands contained in a file</entry>
+ </row>
</tbody>
</tgroup>
</table>
***************
*** 13933,13938 **** SELECT (pg_stat_file('filename')).modification;
--- 13940,13954 ----
</programlisting>
</para>
+ <indexterm>
+ <primary>pg_execute_from_file</primary>
+ </indexterm>
+ <para>
+ <function>pg_execute_from_file</> makes the server
+ execute <acronym>SQL</> commands to be found in a file. This function is
+ reserved to superusers.
+ </para>
+
<para>
The functions shown in <xref linkend="functions-advisory-locks"> manage
advisory locks. For details about proper use of these functions, see
***************
*** 13955,13960 **** SELECT (pg_stat_file('filename')).modification;
--- 13971,13977 ----
<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
***************
*** 30,35 ****
--- 31,46 ----
#include "utils/memutils.h"
#include "utils/timestamp.h"
+ #include "tcop/pquery.h"
+ #include "tcop/tcopprot.h"
+ #include "tcop/utility.h"
+ #include "access/transam.h"
+ #include "access/xact.h"
+ #include "utils/resowner.h"
+ #include "utils/snapmgr.h"
+ #include "parser/analyze.h"
+ #include "access/printtup.h"
+
typedef struct
{
char *location;
***************
*** 264,266 **** pg_ls_dir(PG_FUNCTION_ARGS)
--- 275,459 ----
SRF_RETURN_DONE(funcctx);
}
+
+ /*
+ * Read a file then execute the SQL commands it contains.
+ */
+ Datum
+ pg_execute_from_file(PG_FUNCTION_ARGS)
+ {
+ text *filename_t = PG_GETARG_TEXT_P(0);
+ char *filename;
+ FILE *file;
+ int64 fsize = -1, nbytes;
+ struct stat fst;
+ char *query_string = NULL;
+
+ CommandDest dest = DestNone;
+ MemoryContext oldcontext;
+ List *parsetree_list;
+ ListCell *parsetree_item;
+ bool save_log_statement_stats = log_statement_stats;
+ bool was_logged = false;
+ bool isTopLevel;
+ char msec_str[32];
+
+ if (!superuser())
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ (errmsg("must be superuser to get file information"))));
+
+ /*
+ * Only superuser can call pg_execute_from_file, and CREATE EXTENSION
+ * uses that too. Don't double check the PATH. Also note that
+ * extension's install files are not in $PGDATA but `pg_config
+ * --sharedir`.
+ */
+ filename = text_to_cstring(filename_t);
+
+ if (stat(filename, &fst) < 0)
+ ereport(ERROR,
+ (errcode_for_file_access(),
+ errmsg("could not stat file \"%s\": %m", filename)));
+
+ fsize = Int64GetDatum((int64) fst.st_size);
+
+ if ((file = AllocateFile(filename, PG_BINARY_R)) == NULL)
+ ereport(ERROR,
+ (errcode_for_file_access(),
+ errmsg("could not open file \"%s\" for reading: %m",
+ filename)));
+
+ if (ferror(file))
+ ereport(ERROR,
+ (errcode_for_file_access(),
+ errmsg("could not read file \"%s\": %m", filename)));
+
+ query_string = (char *)palloc((fsize+1)*sizeof(char));
+ memset(query_string, 0, fsize+1);
+ nbytes = fread(query_string, 1, (size_t) fsize, file);
+ pg_verifymbstr(query_string, nbytes, false);
+ FreeFile(file);
+
+ /*
+ elog(NOTICE, "pg_execute_from_file('%s') read %d/%d bytes:", filename, nbytes, fsize);
+ elog(NOTICE, "%s", query_string);
+ */
+
+ /*
+ * Code pasted from postgres.c:exec_simple_query, main differences are:
+ * - don't override unnamed portal, name it after filename instead
+ * - don't start nor finish a transaction
+ * - don't set stats or tracing markers
+ */
+ oldcontext = MemoryContextSwitchTo(MessageContext);
+ parsetree_list = pg_parse_query(query_string);
+ MemoryContextSwitchTo(oldcontext);
+
+ isTopLevel = false;
+
+ foreach(parsetree_item, parsetree_list)
+ {
+ Node *parsetree = (Node *) lfirst(parsetree_item);
+ bool snapshot_set = false;
+ const char *commandTag;
+ char completionTag[COMPLETION_TAG_BUFSIZE];
+ List *querytree_list,
+ *plantree_list;
+ Portal portal;
+ DestReceiver *receiver;
+ int16 format = 0; /* TEXT */
+
+ commandTag = CreateCommandTag(parsetree);
+
+ /* If we got a cancel signal in parsing or prior command, quit */
+ CHECK_FOR_INTERRUPTS();
+
+ /*
+ * Set up a snapshot if parse analysis/planning will need one.
+ */
+ if (analyze_requires_snapshot(parsetree))
+ {
+ PushActiveSnapshot(GetTransactionSnapshot());
+ snapshot_set = true;
+ }
+
+ /*
+ * OK to analyze, rewrite, and plan this query.
+ *
+ * Switch to appropriate context for constructing querytrees (again,
+ * these must outlive the execution context).
+ */
+ oldcontext = MemoryContextSwitchTo(MessageContext);
+
+ querytree_list = pg_analyze_and_rewrite(parsetree, query_string,
+ NULL, 0);
+
+ plantree_list = pg_plan_queries(querytree_list, 0, NULL);
+
+ /* Done with the snapshot used for parsing/planning */
+ if (snapshot_set)
+ PopActiveSnapshot();
+
+ /* If we got a cancel signal in analysis or planning, quit */
+ CHECK_FOR_INTERRUPTS();
+
+ /*
+ * Create a portal to run the query or queries in. Name if after the
+ * given filename. If there already is one, silently drop it.
+ */
+ portal = CreatePortal(filename, true, true);
+ /* Don't display the portal in pg_cursors */
+ portal->visible = false;
+
+ /*
+ * We don't have to copy anything into the portal, because everything
+ * we are passing here is in MessageContext, which will outlive the
+ * portal anyway.
+ */
+ PortalDefineQuery(portal,
+ NULL,
+ query_string,
+ commandTag,
+ plantree_list,
+ NULL);
+
+ /*
+ * Start the portal. No parameters here.
+ */
+ PortalStart(portal, NULL, InvalidSnapshot);
+ PortalSetResultFormat(portal, 1, &format);
+
+ /*
+ * Now we can create the destination receiver object.
+ */
+ receiver = CreateDestReceiver(dest);
+ if (dest == DestRemote)
+ SetRemoteDestReceiverParams(receiver, portal);
+
+ /*
+ * Switch back to transaction context for execution.
+ */
+ MemoryContextSwitchTo(oldcontext);
+
+ /*
+ * Run the portal to completion, and then drop it (and the receiver).
+ */
+ (void) PortalRun(portal,
+ FETCH_ALL,
+ isTopLevel,
+ receiver,
+ receiver,
+ completionTag);
+
+ (*receiver->rDestroy) (receiver);
+
+ PortalDrop(portal, false);
+
+ if (!IsA(parsetree, TransactionStmt) && lnext(parsetree_item) != NULL)
+ {
+ CommandCounterIncrement();
+ }
+ }
+ PG_RETURN_VOID();
+ }
*** a/src/include/catalog/pg_proc.h
--- b/src/include/catalog/pg_proc.h
***************
*** 3386,3399 **** 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");
--- 3386,3401 ----
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 = 3627 ( pg_execute_from_file PGNSP PGUID 12 1 0 0 f f f t f v 1 0 2278 "25" _null_ _null_ _null_ _null_ pg_execute_from_file _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,447 **** extern Datum pg_relation_filepath(PG_FUNCTION_ARGS);
--- 442,448 ----
extern Datum pg_stat_file(PG_FUNCTION_ARGS);
extern Datum pg_read_file(PG_FUNCTION_ARGS);
extern Datum pg_ls_dir(PG_FUNCTION_ARGS);
+ extern Datum pg_execute_from_file(PG_FUNCTION_ARGS);
/* misc.c */
extern Datum current_database(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
