如何从postgres表中创建一个以参数作为文件名的json文件

Question

是否有办法从postgres表创建json文件

就像我用

创建的那样

copy to

，但当我在一个postgres函数中创建要调度，它是无法捕获参数作为文件名

copy  v_originaltext to '/var/lib/postgresql/sftp/smrptesting1.json';

Answer 1

您可能会通过几种不同的方式完成您想要做的事情，尽管这两种方式都需要您将pg_write_server_files和/或pg_execute_server_program的组合授予执行角色/用户。

方法1:使用动态SQL (withpg_write_server_files

)在这里，您可以根据使用COPY命令的初始计划进行推断，更改

COPY v_originaltext to '/var/lib/postgresql/sftp/smrptesting1.json';

在函数块中声明SQL字符串变量(v_sql)，然后执行，例如

CREATE OR REPLACE FUNCTION json_putter(_parameters TEXT)
RETURNS VOID
LANGUAGE plpgsql
AS $function$
DECLARE
v_sql TEXT DEFAULT NULL;
v_originaltext JSONB;
v_filename TEXT;
BEGIN
-- < ... code to populate v_originaltext here ... >
v_filename := '/var/lib/postgresql/sftp/' || _parameters || '.json';
v_sql := 'COPY $1 TO $2';

EXECUTE v_sql USING v_originaltext, v_filename;

——或者，加上单引号:

v_sql := 'COPY (SELECT v_originaltext) TO ' || CHR(39) || v_filename || CHR(39);
EXECUTE v_sql;
END
$function$

，它将构建COPY语句，就像您从客户端手动运行一样。

方法2:使用服务器端psql命令(withpg_execute_server_program

)可以直接从psql命令中输出json，并将标准输出重定向到从参数中生成的文件描述符。

为了简单起见，您可能需要创建两个原子函数来实现这一点:

• 作为包装器来生成v_originaltext的值[例如fx_originaltext(_parameters =>文本)
• 一个将运行一个通用命令通过psql使用COPY…FROM PROGRAM实用程序，并使用一个临时表或类似的表消耗标准输出:

,

CREATE OR REPLACE FUNCTION psql_runner(_function TEXT, _parameters TEXT)
RETURNS VOID
LANGUAGE plpgsql
AS $function$
DECLARE
v_filename TEXT;
BEGIN
v_filename := '/var/lib/postgresql/sftp/' || _parameters || '.json';
CREATE TEMP TABLE _out ( stdout text );
COPY _out FROM PROGRAM ('psql -tq -c "SELECT ' || _function || '(_parameters :=' || quote_literal(_parameters) || ') " > ' || v_filename);
END
$function$

值得注意的是，第二种方法需要更大的权限范围，并且取决于您使用的是哪个版本的postgres，可能需要像第一个方法一样执行COPY动态SQL。