PostgreSQL:如何从函数内部设置search_path? [英] PostgreSQL: how do I set the search_path from inside a function?
问题描述
我需要在查询之前设置search_path。新的搜索路径应基于功能参数。我到底该怎么做?现在我有:
I need to set the search_path before a query. The new search path should be based on a function parameter. How exactly can I Do it? Right now I have:
CREATE FUNCTION get_sections(integer) RETURNS
table(id integer, name varchar, type varchar) as $$
SET search_path to $1, public;
select id, name, type from sections;
$$ language 'sql';
但是它不会接受$ 1。我还尝试了quote_ident($ 1),但没有用。
But it simply won't accept $1. I also tried with quote_ident($1) but it did not work.
谢谢!
推荐答案
通用解决方案
我使用set_config()创建了一个纯SQL函数。
Generic'ish solution
I created a pure sql function by using set_config().
此解决方案支持在逗号分隔的字符串中设置多个架构。默认情况下,
更改适用于当前会话。将 is_local参数设置为true可使更改仅适用于当前事务,请参见 http://www.postgresql.org/docs/9.4/static/functions-admin.html 了解更多详细信息。
This solution supports setting multiple schemas in a comma separated string. By default the change applies to the current session. Setting the "is_local" parameter to true makes the change only apply to the current transaction, see http://www.postgresql.org/docs/9.4/static/functions-admin.html for more details.
CREATE OR REPLACE FUNCTION public.set_search_path(path TEXT, is_local BOOLEAN DEFAULT false) RETURNS TEXT AS $$
SELECT set_config('search_path', regexp_replace(path, '[^\w ,]', '', 'g'), is_local);
$$ LANGUAGE sql;
由于我们没有运行任何动态sql,因此应减少sql注入的机会。为了确保我通过删除除字母数字,空格和逗号以外的所有字符,对文本进行了一些幼稚的处理。转义/引用字符串并非易事,但我不是专家,所以.. =)
Since we're not running any dynamic sql there should be less chance of sql injection. Just to be sure I added some naive sanitizing of the text by removing all characters except alphanumerics,space and comma. Escaping/quoting the string was not trivial, but I'm not an expert, so.. =)
请记住,如果您设置格式错误的路径,则不会有任何反馈。
Remember that there is no feedback if you set a malformed path.
下面是一些测试示例代码:
Here is some sample code for testing:
DROP SCHEMA IF EXISTS testschema CASCADE;
CREATE SCHEMA testschema;
CREATE TABLE testschema.mytable ( id INTEGER );
SELECT set_search_path('testschema, public');
SHOW search_path;
INSERT INTO mytable VALUES(123);
SELECT * FROM mytable;
基于OP原始代码的测试
由于我们事先不知道mytable的架构,因此我们需要使用动态sql。我将set_config-oneliner嵌入到get_sections()函数中,而不是使用通用的 ish函数。
A test based on OP's original code
Since we don't know the schema for mytable in advance, we need to use dynamic sql. I embedded the set_config-oneliner into the get_sections()-function instead of using the generic'ish function.
注意: set_config()中的is_local = false可以正常工作。这意味着修改后的路径在函数运行后仍保留。我不知道为什么。
Note: I had to set is_local=false in set_config() for this to work. That means the modified path remains after the function is run. I'm not sure why.
DROP SCHEMA IF EXISTS testschema CASCADE;
CREATE SCHEMA testschema;
SET search_path TO public;
CREATE TABLE testschema.mytable ( id INTEGER, name varchar, type varchar );
INSERT INTO testschema.mytable VALUES (123,'name', 'some-type');
INSERT INTO testschema.mytable VALUES (567,'name2', 'beer');
CREATE OR REPLACE FUNCTION get_sections(schema_name TEXT) RETURNS
TABLE(id integer, name varchar, type varchar) AS $$
BEGIN
PERFORM set_config('search_path', regexp_replace(schema_name||', public', '[^\w ,]', '', 'g'), true);
EXECUTE 'SELECT id, name, type FROM mytable';
END;
$$ LANGUAGE plpgsql;
SET search_path TO public;
SELECT * FROM get_sections('testschema');
SHOW search_path; -- Unfortunately this has modified the search_path for the whole session.
这篇关于PostgreSQL:如何从函数内部设置search_path?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!