On Tue, Nov 30, 2010 at 05:03, Dimitri Fontaine <dimitri@2ndquadrant.fr> wrote:
> I believe v6 fixes it all, please find it attached.
>
>> ==== Design and Implementation ====
>> * pg_execute_from_file() can execute any files even if they are not
>> in $PGDATA. OTOH, we restrict pg_read_file() to read such files.
>> What will be our policy? Note that the contents of file might be
>> logged or returned to the client on errors.
>>
>> * Do we have any reasons to have pg_execute_from_file separated from
>> pg_read_file ? If we allow pg_read_file() to read files in $PGSHARE,
>> pg_execute_from_file could be replaced with "EXECUTE pg_read_file()".
>> (Note that pg_execute_from_file is implemented to do so even now.)
>
> Thinking some more about it, there's still a reason to maintain them
> separated: the API ain't the same, we're not proposing to read a sql
> script file chunk after chunk, nor do we want users to have to check for
> the file size before being able to call the function.
>
> A problem with pg_read_file() as it stands is that it's returning text
> rather than bytea, too, and if we choose to fix that rather than adding
> some new functions, we will want to avoid having to separate the two
> functions again.
I think there are two topics here: 1. Do we need to restrict locations in which sql files are executable? 2. Which is
better,pg_execute_sql_file() or EXECUTE pg_read_file() ?
There are no discussion yet for 1, but I think we need some restrictions
anyway. If we will be conservative, we would allow only files in $PGSHARE
or $PGSHARE/contrib. More aggressive approach might be something like
CREATE DIRECTORY command in Oracle Database:
http://download.oracle.com/docs/cd/E14072_01/server.112/e10592/statements_5007.htm
For 2, I'd like to monofunctionalize pg_execute_sql_file() into
separated functions something like:
- FUNCTION pg_execute_sql(sql text)
- FUNCTION replace(str text, from1 text, to1 text, VARIADIC text)
- FUNCTION pg_read_binary_file(path text, offset bigint, size bigint) (size == -1 means whole-file)
pg_read_binary_file() is the most important functions probably.
pg_execute_sql_file() can be rewritten as follows. We can also use
existing convert_from() to support encodings.
SELECT pg_execute_sql( replace( convert_from( pg_read_binary_file('path', 0, -1),
'encoding'), 'key1', 'value1', 'key2', 'value2') );
--
Itagaki Takahiro