在 PostgreSQL 9.0 中，我有这个 PLPGSQL 匿名代码块：
DO $$
DECLARE
bigobject integer;
BEGIN
SELECT lo_creat(-1) INTO bigobject;
ALTER LARGE OBJECT bigobject OWNER TO postgres;
INSERT INTO files (id, "mountPoint", data, comment) VALUES (15, '/images/image.png', bigobject, 'image data');
SET search_path = pg_catalog;
SELECT pg_catalog.lo_open(bigobject, 131072);
SELECT pg_catalog.lowrite(0, '\\x000001000100101010000000000028010000160000002800000010000000200000000100040');
SELECT pg_catalog.lo_close(0);
REVOKE ALL ON LARGE OBJECT bigobject FROM PUBLIC;
REVOKE ALL ON LARGE OBJECT bigobject FROM postgres;
GRANT ALL ON LARGE OBJECT bigobject TO postgres;
GRANT ALL ON LARGE OBJECT bigobject TO "com.ektyn.eshops.myuser";
END
$$;
但它失败了：
ERROR: syntax error at or near "bigobject"
LINE 6: ALTER LARGE OBJECT bigobject OWNER TO postgres;
^
********** Error **********
ERROR: syntax error at or near "bigobject"
SQL state: 42601
Character: 103
而且我在代码中找不到错误。
必须有一个常数 in ALTER LARGE OBJECT oid ...。尝试这个解决方法：
DO $$
DECLARE
bigobject integer;
BEGIN
SELECT lo_creat(-1) INTO bigobject;
EXECUTE 'ALTER LARGE OBJECT ' || bigobject::text || ' OWNER TO postgres';
...
当然，这同样适用于 GRANT 和 REVOKE。
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)
