如何使用变量在存储过程中执行授权？

Question

作为Hibernate中BLOB值的后期创建钩子的替代方法，我认为数据库内触发器是更好的解决方案，因此我尝试编写以下触发器：

CREATE FUNCTION lo_default_grant() RETURNS trigger AS $$
DECLARE
    lo_oid lo := NEW[TG_ARGV[0]];
BEGIN
    IF TG_OP = 'INSERT' THEN
        GRANT SELECT ON LARGE OBJECT lo_oid TO reader_role;
    ELSIF OLD[TG_ARGV[0]] != NEW[TG_ARGV[0]] THEN
        GRANT SELECT ON LARGE OBJECT lo_oid TO reader_role;
    END IF;
END;
$$ LANGUAGE plpgsql;

将与触发器一起使用，例如：

CREATE TRIGGER t_grant AFTER INSERT OR UPDATE ON my_entity
  FOR EACH ROW EXECUTE PROCEDURE lo_default_grant(file);

然而，我无法计算出构建GRANT语句的语法。

[2022-03-09 16:14:51][42601]ERROR：语法错误在或接近lo_oid
[2022-03-09 16:14:51]位置：279

真的不可能像在其他语句中那样使用变量吗？

Answer 1

GRANT语句不可优化，因此不能直接将参数插入其中。也不能用动态字段名为行下标。

但是，PL/pgSQL具有EXECUTE语句，允许您构造包含所需语句的字符串：

CREATE OR REPLACE FUNCTION lo_default_grant() RETURNS trigger AS $$
DECLARE
    old_lo oid;
    new_lo oid;
BEGIN
    EXECUTE format('SELECT $1.%I', TG_ARGV[0]) USING OLD INTO old_lo;
    EXECUTE format('SELECT $1.%I', TG_ARGV[0]) USING NEW INTO new_lo;
    IF TG_OP = 'INSERT' OR old_lo != new_lo THEN
        EXECUTE format('GRANT SELECT ON LARGE OBJECT %s TO reader_role', new_lo);
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;