PL/pgSQL匿名代码块 [英] PL/pgSQL anonymous code block
本文介绍了PL/pgSQL匿名代码块的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
在PostgreSQL 9.0中,我有以下PLPGSQL匿名代码块:
In PostgreSQL 9.0 I have this PLPGSQL anonymous code block:
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
我在代码中找不到错误.
and I can't find mistake in code.
推荐答案
ALTER LARGE OBJECT oid ...
中必须有一个 oid常量.尝试以下解决方法:
There must be an oid constant in ALTER LARGE OBJECT oid ...
. Try this workaround:
DO $$
DECLARE
bigobject integer;
BEGIN
SELECT lo_creat(-1) INTO bigobject;
EXECUTE 'ALTER LARGE OBJECT ' || bigobject::text || ' OWNER TO postgres';
...
当然,这同样适用于GRANT和REVOKE.
The same also applies to GRANT and REVOKE, of course.
这篇关于PL/pgSQL匿名代码块的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文