如何在PostgreSQL函数中运行多个动态查询

发布于 2025-01-31 06:42:01 字数 396 浏览 2 评论 0原文

我遇到了一些问题，弄清楚如何在单个功能中运行多个动态查询。

 CREATE OR REPLACE FUNCTION cnms_fy22q2.test_function(
    fyq text)
    RETURNS void
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE 
AS $BODY$
BEGIN
 -- logic
    TRUNCATE TABLE 'schema_' || fyq || '.my_table'
    DROP TABLE 'schema_' || fyq || '.my_table';
END;
$BODY$;

我通常会遇到语法错误，例如错误：或附近...的语法错误。我在这里做错了什么？

原文

I am having some issues figuring out how to run multiple dynamic queries in a single function.

 CREATE OR REPLACE FUNCTION cnms_fy22q2.test_function(
    fyq text)
    RETURNS void
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE 
AS $BODY$
BEGIN
 -- logic
    TRUNCATE TABLE 'schema_' || fyq || '.my_table'
    DROP TABLE 'schema_' || fyq || '.my_table';
END;
$BODY$;

I am generally getting syntax errors, like ERROR: syntax error at or near .... What am I doing wrong here?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

〗斷ホ乔殘χμё〖 2025-02-07 06:42:01

您不能简单地将串联字符串做出动态的SQL语句。看看 eccepute 和立即执行。

就您而言，您可以这样使用：

CREATE OR REPLACE FUNCTION cnms_fy22q2.test_function(
    fyq text)
    RETURNS void
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE
AS $BODY$
BEGIN
    -- logic
    EXECUTE 'TRUNCATE TABLE schema_' || fyq || '.my_table';
    EXECUTE 'DROP TABLE schema_' || fyq || '.my_table';
END
$BODY$;

You can't simply concatenate strings to make a dynamic sql statement. Take a look at EXECUTE and EXECUTE IMMEDIATE.

In your case, you could use it like this:

CREATE OR REPLACE FUNCTION cnms_fy22q2.test_function(
    fyq text)
    RETURNS void
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE
AS $BODY$
BEGIN
    -- logic
    EXECUTE 'TRUNCATE TABLE schema_' || fyq || '.my_table';
    EXECUTE 'DROP TABLE schema_' || fyq || '.my_table';
END
$BODY$;

回复收藏 0 原文

记忆之渊 2025-02-07 06:42:01

使用将返回字符串并在您的函数中执行的格式函数。

create function permanently_delete_table(fyq text) RETURNS void
    LANGUAGE plpgsql AS $
    declare
    begin
         EXECUTE format('TRUNCATE TABLE schema_%s.my_table',fyq); 
         EXECUTE format('DROP TABLE schema_%s.my_table',fyq); 
    end
$;

sqldaddy.io

Use the format function which will return a string and execute in your function.

create function permanently_delete_table(fyq text) RETURNS void
    LANGUAGE plpgsql AS $
    declare
    begin
         EXECUTE format('TRUNCATE TABLE schema_%s.my_table',fyq); 
         EXECUTE format('DROP TABLE schema_%s.my_table',fyq); 
    end
$;

Demo in sqldaddy.io

回复收藏 0 原文

戏舞 2025-02-07 06:42:01

只是为了添加我提出的第三个选项，该选项结合了单个execute format（）;语句中的查询，这是我想到的：

CREATE OR REPLACE FUNCTION cnms_fy22q2.drop_summary_tables_function(
fyq text)
  RETURNS void
  LANGUAGE 'plpgsql'
  COST 100
  VOLATILE 
AS $BODY$
BEGIN
 -- logic
  EXECUTE format(
  'TRUNCATE TABLE schema_%s.my_table;
  DROP TABLE  schema_%s.my_table', fyq, fyq);
END;
$BODY$;

Just to add a third option that I came up with which combines the queries inside a single EXECUTE format(); statement, here's what I came up with:

CREATE OR REPLACE FUNCTION cnms_fy22q2.drop_summary_tables_function(
fyq text)
  RETURNS void
  LANGUAGE 'plpgsql'
  COST 100
  VOLATILE 
AS $BODY$
BEGIN
 -- logic
  EXECUTE format(
  'TRUNCATE TABLE schema_%s.my_table;
  DROP TABLE  schema_%s.my_table', fyq, fyq);
END;
$BODY$;

回复收藏 0 原文

~没有更多了~