postgresql - PL/pgSQL匿名代码块

在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

而且我找不到代码中的错误。

最佳答案

ALTER LARGE OBJECT oid ...中必须有一个 oid常数。尝试以下解决方法:

DO $$
    DECLARE
        bigobject integer;
    BEGIN
        SELECT lo_creat(-1) INTO bigobject;
        EXECUTE 'ALTER LARGE OBJECT ' || bigobject::text || ' OWNER TO postgres';
        ...

当然，这同样适用于GRANT和REVOKE。