我可能在这里扭曲postgres,但从根本上说,我想做的是获取一个字符串变量,并将其传递给一个仅使用psql的sql命令(在本例中是COPY)。
所以这就是我想出来的。这些命令被分成两个文件,因为我想在其他情况下使用mydb_函数:
文件一:mydb_functions--1.0.sql(在share/extension和mydb_functions.control中),也可以按照手册中的说明进行设置。给定文件名,返回完整的文件路径。这样做只是为了使下面add_data.sql中的COPY语句更整洁。
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将数据复制到现有的PoSGRESH表中。注意:由于CREATE EXTENSION命令的原因,需要使用超级用户权限运行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命令。任何关于实现这一目标的简单方法的建议也都是受欢迎的。我认为创建一个表来存储变量要容易得多。但我想完成最后一步。
最佳答案
如果问题是使用动态目标路径运行COPY
,请使用EXECUTE
运行format
ted 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中不起作用。或者,您可以使用here文档(类似于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';
关于postgresql - 如何从PostgreSQL的plpgsql函数返回的SELECT查询中提取字符串(或文本)?,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/13247863/