Hi there,
on PostgreSQL 14, I'm using function pg_read_file to read a JSON file on
the server. After that, the JSON file gets casted to jsonb and with
function jsonb_array_elements I'm iterating over the "records", which I
transform into a PostgreSQL ROWTYPE with jsonb_populate_record...
Since the source files are actually XML files, these are turned into
JSON files with Node JS and the fast-xml-parser module (processing JSON
is much faster and more comfortable than processing XML in PostgreSQL).
The command line of this conversion process is like this:
# node /opt/my_node_apps/xml_to_json.js <path_to_xml_file>
In order to do this without temporary JSON files (which need to be
deleted at some time), it would be great to have a new Generic File
Access Function
pg_read_program_output(command)
Although one could argue, that it's not a Generic *File* Access
Function, that function would be a worthwhile addition and could use the
same semantics and rules as with the
COPY table_name FROM PROGRAM 'command'
statement. Also the implementation (running a command with the shell and
capture it's STDOUT) is nearly the same.
In contrast to the other Generic File Access Functions, it will be
almost impossible to restrict access to programs or commands within the
database cluster directory (could be a complex shell command). Aside
from that this makes no sense since, typically, there are no executable
programs in those directories.
Even worse, it's likely also not possible to restrict the source of the
content read (the STDOUT) to be any of these directories, since the
program could just dump anything to its STDOUT.
AFAIT, that's not really an issue but only makes this new Generic File
Access Function special, in that these restrictions and the meaning of
role pg_read_server_files just do not apply for it.
Do you know if there is already such a function, maybe provided by an
extension I do not yet know?
Cheers
Carsten