PL/pgSQL anonymous code block

Question

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
$$;

but it fails:

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.

klin · Accepted Answer · 2019-02-26T13:18:10.490

7

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';
        ...

The same also applies to GRANT and REVOKE, of course.

edited Feb 26 '19 at 13:18

answered May 10 '14 at 23:08

klin

112,967
15
204
232

Erwin Brandstetter · Answer 2 · 2023-01-18T12:33:28.383

6

In addition to what @klin already cleared up, you cannot use SELECT without a target in PL/pgSQL code. Replace it with PERFORM in those calls.

Aside: Using "com.ektyn.eshops.myuser" as name for a role is a terrible idea. Use legal, lower case identifiers that don't have to be double-quoted.

edited Jan 18 '23 at 12:33

answered May 11 '14 at 01:46

Erwin Brandstetter

605,456
145
1,078
1,228

My answer is surely helpful, but klin addresses the primary issue in your question. So the accept should probably go to his answer. – Erwin Brandstetter May 12 '14 at 00:47

score 5 · Answer 3 · edited May 23 '17 at 12:01

This is an artifact of the fact that PostgreSQL has two completely different kinds of SQL statements internally - plannable (SELECT, INSERT, UPDATE, and DELETE) and unplannable (everything else) statements.

Only plannable statements support query parameters.

PL/pgSQL implements variable substitutions into statements, like your bigobject, using query parameters.

Because they aren't supported for non-plannable statements, no substitution is performed. So PL/pgSQL tries to execute the statement literally, as if you'd typed:

ALTER LARGE OBJECT bigobject OWNER TO postgres;

directly at the psql prompt. It does not detect this as an error.

To work around this, use EXECUTE ... FORMAT, e.g.

EXECUTE format('ALTER LARGE OBJECT %s OWNER TO postgres', bigobject);

See this related answer about COPY.

PL/pgSQL anonymous code block

3 Answers3

Linked