postgresql - 如何从 postgresql 中的 plpgsql 函数返回的 SELECT 查询中提取字符串(或文本)？

Question

我可能在这里扭曲了 postgres，但从根本上说，我想做的是获取一个字符串变量并将其传递给仅使用 psql 的 sql 命令(在本例中为 COPY)。

这就是我想到的。这些命令分为 2 个文件，因为我希望能够在其他情况下使用 mydb_functions:

文件一:mydb_functions--1.0.sql(在共享/扩展和 mydb_functions.control 中也按照手册中的描述进行设置。给定文件名，返回完整的文件路径。这样做只是为了在 add_data 中创建 COPY 语句.sql 下面，更整洁。

CREATE OR REPLACE FUNCTION fpn(filename text) RETURNS TEXT as '
  DECLARE 
    mypath text := ''/path/to/my/directory/'';
  BEGIN
    RETURN mypath || filename;
  END
' LANGUAGE plpgsql;

文件二:add_data.sql。这仅用于在命令行使用 psql 将数据复制到现有的 postgres 表中。注意:由于 CREATE EXTENSION 命令，需要以 super 用户权限运行 psql。

CREATE EXTENSION IF NOT EXISTS mydb_functions;
-- haven't figured out how to create a function without arguments yet.
CREATE OR REPLACE FUNCTION test (dummyvar text) RETURNS text as '     
  DECLARE 
    filepath RECORD;
  BEGIN 
    SELECT * INTO filepath from fpn(''mydatafile.data'');
    COPY tablename (columnname) FROM filepath;
  END
' LANGUAGE plpgsql;

我坚持的部分是如何从文件路径记录中提取文本以在 COPY 命令中使用。也欢迎任何有关实现此目标的更简单方法的提示。我认为创建一个表来存储变量比这容易得多。但我想完成最后一步。

Answer 1

如果您的问题是使用动态目标路径运行COPY，请使用EXECUTE 运行格式化SQL 查询。

CREATE OR REPLACE FUNCTION test () RETURNS text as $$ 
  DECLARE 
    filepath RECORD;
  BEGIN 
    SELECT * INTO filepath from fpn('mydatafile.data');
    EXECUTE format('COPY tablename (columnname) FROM %L', filepath);
  END
$$ LANGUAGE plpgsql;

看，这失败了:

DO
$$
DECLARE
    somevar text;
BEGIN
    somevar := '/tmp/somepath.csv';
    COPY tablename(columnname) FROM somevar;
END;
$$;

但这行得通:

DO
$$
DECLARE
    somevar text;
BEGIN
    somevar := '/tmp/somepath.csv';
    EXECUTE format('COPY tablename(columnname) FROM %L', somevar);
END;
$$;

参见:

• EXECUTE
• format()

format() 的 %L 说明符自动引用文字。 %I 对标识符做同样的事情。

---

如果像我最初想的那样，您正在谈论从外部将数据导入 psql，您可能会发现 psql 变量和变量插值很有用:

$ psql -v filepath=/path/to/my/directory/mydatafile.data regress
regress=> SELECT :'filepath';
               ?column?                
---------------------------------------
 /path/to/my/directory/mydatafile.data
(1 row)

请注意，冒号是不带引号的，然后是变量名被引号。奇怪的语法，我知道。这只适用于 psql；它不会在(比如说)PgAdmin-III 中工作。

或者，您可以使用此处文档(类 unix shell 特定的，在 Windows 的 cmd.exe 中不起作用)来进行引用文本插值:

$ FILEPATH=/path/to/my/directory/mydatafile.data
$ psql regress <<__END__
SELECT '$FILEPATH';
__END__

               ?column?                
---------------------------------------
 /path/to/my/directory/mydatafile.data
(1 row)

这两个都展示了如何从 shell 级别将变量插入到 psql 中。从那里开始，这是在您的函数中使用它的问题。比如，使用 -v filename=myfilename.csv:

CREATE OR REPLACE FUNCTION test() RETURNS text as $$
  DECLARE 
    filepath RECORD;
  BEGIN 
    SELECT * INTO filepath from fpn(:'filename');
    COPY tablename (columnname) FROM filepath;
  END
$$ LANGUAGE plpgsql;

或者仅使用 -v filepath=/full/path/to/myfilename.csv:

$ psql -v filepath=/path/to/my/directory/mydatafile.data regress
regress=> COPY tablename (columnname) FROM :'filepath';