如何在sql脚本中包含带参数的文件

okxuctiv  于 2021-07-26  发布在  Java
关注(0)|答案(0)|浏览(262)

给定以下postgresql的sql脚本。。。

  1. CREATE TABLE IF NOT EXISTS my_table (
  2. id UUID PRIMARY KEY,
  3. name VARCHAR(255) NOT NULL,
  4. create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  5. acl_read UUID[] DEFAULT array[current_setting('subject')]::uuid[] NOT NULL,
  6. acl_write UUID[] DEFAULT array[current_setting('subject')]::uuid[] NOT NULL
  7. );
  8. CREATE INDEX ON my_table USING gin(acl_read);
  9. CREATE INDEX ON my_table USING gin(acl_write);
  10. CREATE POLICY my_table_policy ON my_table
  11. USING (
  12. acl_read @> array[current_setting('subject')]::uuid[]
  13. OR
  14. acl_write @> array[current_setting('subject')]::uuid[]
  15. )
  16. WITH CHECK (
  17. acl_write @> array[current_setting('subject')]::uuid[]
  18. );
  19. ALTER TABLE my_table ENABLE ROW LEVEL SECURITY;

... 我想搬家 POLICY 节,并传递要为其创建策略的表的名称:

  1. CREATE TABLE IF NOT EXISTS my_table (
  2. id UUID PRIMARY KEY,
  3. name VARCHAR(255) NOT NULL,
  4. create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  5. acl_read UUID[] DEFAULT array[current_setting('subject')]::uuid[] NOT NULL,
  6. acl_write UUID[] DEFAULT array[current_setting('subject')]::uuid[] NOT NULL
  7. );
  8. \i policy.sql 'my_table'

这里是 policy.sql ,应将表的名称作为输入参数:

  1. CREATE INDEX ON PARAM_VALUE USING gin(acl_read);
  2. CREATE INDEX ON PARAM_VALUE USING gin(acl_write);
  3. CREATE POLICY my_table_policy ON PARAM_VALUE
  4. USING (
  5. acl_read @> array[current_setting('subject')]::uuid[]
  6. OR
  7. acl_write @> array[current_setting('subject')]::uuid[]
  8. )
  9. WITH CHECK (
  10. acl_write @> array[current_setting('subject')]::uuid[]
  11. );
  12. ALTER TABLE PARAM_VALUE ENABLE ROW LEVEL SECURITY;

所以问题是:如何将表名传递给 policy.sql ? 那么如何得到参数的值呢( PARAM_VALUE )在 policy.sql ?

暂无答案!

目前还没有任何答案,快来回答吧!

相关问题