如何在 postgres 过程中通过 arg 文本更改架构 [英] How to change the schema by arg text in postgres procedure

问题描述

CREATE OR REPLACE FUNCTION newfunction (Schema1 text, Schema2 text)
RETURNS integer
LANGUAGE plpgsql
SECURITY DEFINER
AS $function$ 
.
.
.
insert into [Schema1].table (name,phone,address,......)
select name,phone,address,..... from [Schema2].table where....;

我想通过 arg 文本更改架构这有可能吗?

I want to change the schema by arg text is this possible to do this?

推荐答案

您必须始终使用动态 SQL:

You'll have to use dynamic SQL throughout:

EXECUTE
   format(
      E'INSERT INTO %I.tab (...)\n'
      'SELECT ... FROM %I.tab WHERE ...',
      schema1,
      schema2
   );

这篇关于如何在 postgres 过程中通过 arg 文本更改架构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！