postgresql: syntax error at or near "DO"

Question

I am trying to run a DO block, e.g. this one (copied from a post in this forum)

DO $$
DECLARE x int;
    BEGIN
    x := 10;
    RAISE NOTICE '>>>%<<<', x;
END;
$$

but what happens is:

ERROR: syntax error at or near "DO"

SQL state: 42601

Character: 1

It doesn't matter, what DO block I run, the DO statement is simply not working (including on examples copy/pasted from the doucment, and I wonder what could be the reason. Could anybody help me, please?

Thanks a lot

Answer 1

You're running on an old version of PostgreSQL.

In general, if you get an unexpected syntax error on a keyword or PostgreSQL complains about a missing function that should obviously exist, the first thing to do is check that the feature you're trying to use actually existed in your version.

You can replace a DO block with:

CREATE OR REPLACE FUNCTION some_func() RETURNS void AS 
$$
.. body of the DO block here...
$$ LANGUAGE plpgsql VOLATILE;

SELECT some_func();

DROP FUNCTION some_func();

in an older version.

Answer 2

If you have the same error with postgreSQL 9.0+, then it is due to the wrong execute type. pgAdiminIII has two execute (green triangle) buttons:

• Execute Query
• Execute pgScript

In order to run DO $$ you need the first one.