postgres，如何在函数中引用insert ID来创建表

Question

我正在尝试创建以下函数，该函数将角色插入表中，获取ID值，然后在"create table"调用中使用该ID值。

CREATE FUNCTION data.addtable (sch text, tbl text) RETURNS INTEGER AS $$
begin
WITH i AS (
SELECT nextval('data.customdata_id_seq') AS id
)
INSERT INTO "data"."customtables" (id,        
ownerid,name,internalname,tableinfo)
SELECT id, 15,tbl, 'tbl_' || id, ('{
"counter": "2",
"tablename": "' || sch ||'_tbl_' || id || '",
"headers": ["ID", "First Name", "Last Name"],
"columns": [
{"data": "id"},
{"data": "field_1"},
{"data": "field_2"}
]
}')::jsonb 
FROM  i RETURNING ID;
EXECUTE 'Create table ' || sch || '.' || 'tbl_'  || id || ' (id serial 
primary key, field_1 varchar(100), field_2 varchar(100))';
end;
$$ LANGUAGE plpgsql;

当我尝试运行这个时，我得到错误：

ERROR:  column "id" does not exist
LINE 1: ...ELECT 'Create table ' || sch || '.' || 'tbl_'  || id || ' 
(i...
^
QUERY:  SELECT 'Create table ' || sch || '.' || 'tbl_'  || id || ' (id 
serial primary key, field_1 varchar(100), field_2 varchar(100))'
CONTEXT:  PL/pgSQL function data.addtable(text,text) line 18 at EXECUTE
SQL state: 42703

要插入到CustomTables中的查询的第一部分可以工作，但我如何传递/引用此ID，以便在EXECUTE命令中使用它来创建动态表？我还希望它以整数形式返回ID。

更新，我做了一些更改：试图弄清楚为什么这不起作用？

CREATE FUNCTION data.addtable (sch text, tbl text) RETURNS integer AS $$
DECLARE
_id data.customtables.id%TYPE;
BEGIN  
SELECT nextval('data.customdata_id_seq') AS id into _id;
INSERT INTO "data"."customtables" (id, 
ownerid,name,internalname,tableinfo)
SELECT _id, 15,tbl, 'tbl_' || _id, ('{
"counter": "2",
"tablename": "' || sch ||'_tbl_' || _id || '",
"headers": ["ID", "First Name", "Last Name"],
"columns": [
{"data": "id"},
{"data": "field_1"},
{"data": "field_2"}
]
}')::jsonb;
EXECUTE 'Create table ' || sch || '.' || 'tbl_'  || _id ' (id serial 
primary key, field_1 varchar(100), field_2 varchar(100))';
return _id;
END;
$$ LANGUAGE plpgsql;

出于某种原因，它知道top语句中的_id，但在EXECUTE语句中抛出_id的错误，因为"不存在"。为什么？

想清楚了，只是在_id后面缺少一个||。工作

Answer 1

您可以将ID返回到变量中，并在串联中使用它。

CREATE FUNCTION data.addtable (sch text,
tbl text)
RETURNS integer
AS
$$
DECLARE
_id data.customtables.id%TYPE;
BEGIN
WITH ...
INSERT INTO ...
SELECT ...
RETURNING id
INTO _id;
EXECUTE 'CREATE TABLE ' || sch || '.' || 'tbl_'  || _id || ' (id serial primary key, field_1 varchar(100), field_2 varchar(100))';
END;
$$
LANGUAGE plpgsql;