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

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

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

CREATE TABLE IF NOT EXISTS my_table (

    id          UUID PRIMARY KEY,
    name        VARCHAR(255) NOT NULL, 
    create_time TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,

    acl_read    UUID[] DEFAULT array[current_setting('subject')]::uuid[] NOT NULL,
    acl_write   UUID[] DEFAULT array[current_setting('subject')]::uuid[] NOT NULL
);

CREATE INDEX ON my_table USING gin(acl_read);
CREATE INDEX ON my_table USING gin(acl_write);

CREATE POLICY my_table_policy ON my_table
USING (
    acl_read @> array[current_setting('subject')]::uuid[]
    OR
    acl_write @> array[current_setting('subject')]::uuid[]
)
WITH CHECK (
    acl_write @> array[current_setting('subject')]::uuid[]
);

ALTER TABLE my_table ENABLE ROW LEVEL SECURITY;

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

CREATE TABLE IF NOT EXISTS my_table (

    id          UUID PRIMARY KEY,
    name        VARCHAR(255) NOT NULL, 
    create_time TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,

    acl_read    UUID[] DEFAULT array[current_setting('subject')]::uuid[] NOT NULL,
    acl_write   UUID[] DEFAULT array[current_setting('subject')]::uuid[] NOT NULL
);

\i policy.sql 'my_table'

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

CREATE INDEX ON PARAM_VALUE USING gin(acl_read);
CREATE INDEX ON PARAM_VALUE USING gin(acl_write);

CREATE POLICY my_table_policy ON PARAM_VALUE
USING (
    acl_read @> array[current_setting('subject')]::uuid[]
    OR
    acl_write @> array[current_setting('subject')]::uuid[]
)
WITH CHECK (
    acl_write @> array[current_setting('subject')]::uuid[]
);

ALTER TABLE PARAM_VALUE ENABLE ROW LEVEL SECURITY;

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

暂无答案!

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

相关问题