时间戳

首页 > 解决方案 > postgres，如何在函数中引用插入 ID 来创建表？

postgresql - postgres，如何在函数中引用插入 ID 来创建表？

问题描述

我正在尝试创建以下函数，它将角色插入表中，获取 ID 值，然后在“创建表”调用中使用该 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;

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

想通了，只是缺少一个|| 在 _id 之后。在职的。

标签： postgresqlpostgresql-9.4postgresql-9.5

解决方案

您可以将 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;

推荐阅读