PostgreSQL COPY命令中动态生成的表名 [英] Dynamically-generated table-name in PostgreSQL COPY command

问题描述

此PostgreSQL COPY命令有效:

This PostgreSQL COPY command works:

copy tablename from E'c:\\abc\\a.txt';

但是我希望表名是动态生成的.我该怎么办?

but I want the tablename to be dynamically generated. How can I do this?

推荐答案

您需要构建一个字符串，并在动态表名称中串联起来，然后使用execute.请注意，您对'by'进行了转义.这也包括一个动态名称来保存文件.您需要用您正在使用的实际目录替换saveir.

You need to build a string, concatenating in the dynamic table name, and then use execute. Note that you escape the ' by ''. This also includes a dynamic name to save the file too. You need to replace savedir with the actual directory you are using.

CREATE OR REPLACE FUNCTION dynamicCopy(tablename text, outname text) RETURNS VOID AS $$

DECLARE STATEMENT TEXT;

BEGIN

  STATEMENT := 'COPY (select * from ' || quote_ident(tablename) || ') to ''savedir' || outname ||'.txt''';

  EXECUTE STATEMENT;

END;

$$ LANGUAGE 'plpgsql';

自从我第一次写这篇文章以来，我发现了format函数，我认为它通常比使用串联运算符||生成的SQL更易于阅读.并且更加灵活.

Since I first wrote this, I have discovered the format function, which I think is generally easier to read than SQL generated with the concatenation operator || and more flexible.

CREATE OR REPLACE FUNCTION dynamicCopy(tablename text, outname text) RETURNS VOID AS 
$BODY$
BEGIN
  EXECUTE FORMAT('COPY (SELECT * FROM %s) TO ''savedir%s.csv''',
                  tablename, 
                  outname);
END
$BODY$ 
LANGUAGE plpgsql;

请参阅官方文档以进行全面讨论:

See the official docs for a full discussion: https://www.postgresql.org/docs/current/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

这篇关于PostgreSQL COPY命令中动态生成的表名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！