November 1st, 2009 by depesz | Tags: , , , , , | 2 comments »
Did it help? If yes - maybe you can help me? Donate BTC to 19zPa5diT2LZqGtTi8f8bfApLn8rw9zBHx

On 22nd of September, Tom Lane committed a patch by Petr Jelinek:

Log Message:
-----------
Implement the DO statement to support execution of PL code without having
to create a function for it.
 
Procedural languages now have an additional entry point, namely a function
to execute an inline code block. This seemed a better design than trying
to hide the transient-ness of the code from the PL. As of this patch, only
plpgsql has an inline handler, but probably people will soon write handlers
for the other standard PLs.
 
In passing, remove the long-dead LANCOMPILER option of CREATE LANGUAGE.
 
Petr Jelinek

What it really is?

Well, it's a way to make a simple function, and call it, without having actual function. Complicated?

Let's say – you want to do a grant-all type of thing. With do, you can just:

DO $$
DECLARE
temprec record;
sql_prefix text := 'GRANT ALL ON TABLE ';
sql_suffix text := ' TO depesz';
sql_table text;
BEGIN
for temprec in select * from information_schema.tables where table_schema !~ '^(information_schema|pg_.*)$' LOOP
sql_table := quote_ident( temprec.table_schema ) || '.' || quote_ident( temprec.table_name );
EXECUTE sql_prefix || sql_table || sql_suffix;
RAISE NOTICE 'Granted to %', sql_table;
END LOOP;
END;
$$ language plpgsql;

( You can skip ‘language plpgsql' if you're writing in your default language – default as specified in default_do_language configuration variable ).

You can't select any rows with DO. It is treated as a function with no arguments, and no return value. But besides this simple limitation – you're good to go :)

Possible use cases? Well, I can easily imagine using DO when writing patch files for database (i.e. .sql files which add new functionality to developed application/database).

Aside from this – I think I will stick with procedures – if only for being able to easily re-run the code. But – everybody has got another needs, so it might be that DO will prove indispensable for you.

  1. 2 comments

  2. # Bob
    Nov 2, 2009

    “You can’t select any rows with DO”

    I may of miss read your comment, but you can select rows within the DO. You just can’t return them out side of the do. I assume that was what you meant and I read into too much.

    DO $$
    DECLARE
    l_name space_ships.name%TYPE = NULL;
    BEGIN

    SELECT name INTO l_name
    FROM space_ships;

    RAISE INFO ‘Space Ship Name = %’,l_name;

    END$$;

  3. Nov 2, 2009

    @Bob:
    sorry if I wasn’t clear.
    I meant that you can’t SELECT * FROM DO $$ $$ …;

    of course within function itself you can do anything you want.

Leave a comment