• Home

  • Custom Ecommerce
  • Application Development
  • Database Consulting
  • Cloud Hosting
  • Systems Integration
  • Legacy Business Systems
  • Security & Compliance
  • GIS

  • Expertise

  • About Us
  • Our Team
  • Clients
  • Careers

  • Blog

  • EpiTrax

  • VisionPort

  • Contact
  • Our Blog

    Ongoing observations by End Point Dev people

    Anonymous code blocks

    Josh Tolley

    By Josh Tolley
    September 23, 2010

    With the release of PostgreSQL 9.0 comes the ability to execute “anonymous code blocks” in various of the PostgreSQL procedural languages. The idea stemmed from work back in autumn of 2009 that tried to respond to a common question on IRC or the mailing lists: how do I grant a permission to a particular user for all objects in a schema? At the time, the only solution short of manually writing commands to grant the permission in question on every object individually was to write a script of some sort. Further discussion uncovered several people that often found themselves writing simple functions to handle various administrative tasks. Many of those people, it turned out, would rather simply call one statement, rather than create a function, call the function, and then drop (or just ignore) the function they’d never need again. Hence, the new DO command.

    The first language to support DO was PL/pgSQL. The PostgreSQL documentation provides an example to answer the original question: how do I grant permissions on everything to a particular user.

    DO $$DECLARE r record;
    BEGIN
        FOR r IN SELECT table_schema, table_name FROM information_schema.tables
                 WHERE table_type = 'VIEW' AND table_schema = 'public'
        LOOP
            EXECUTE 'GRANT ALL ON ' || quote_ident(r.table_schema) || '.' || quote_ident(r.table_name) || ' TO webuser';
        END LOOP;
    END$$;
    

    Notice that this doesn’t actually tell us what language to use. If no language is specified, DO defaults to PL/pgSQL (which, in 9.0, is enabled by default). But you can use other languages as well:

    DO $$
    HAI
        BTW Calculate pi using Gregory-Leibniz series
        BTW This method does not converge particularly quickly...
        I HAS A PIADD ITZ 0.0
        I HAS A PISUB ITZ 0.0
        I HAS A ITR ITZ 0
        I HAS A T1
        I HAS A T2
        I HAS A PI ITZ 0.0
        I HAS A ITERASHUNZ ITZ 1000
    
        IM IN YR LOOP
            T1 R QUOSHUNT OF 4.0 AN SUM OF 3.0 AN ITR
            T2 R QUOSHUNT OF 4.0 AN SUM OF 5.0 AN ITR
            PISUB R SUM OF PISUB AN T1
            PIADD R SUM OF PIADD AN T2
            ITR R SUM OF ITR AN 4.0
            BOTH SAEM ITR AN BIGGR OF ITR AN ITERASHUNZ, O RLY?
                YA RLY, GTFO
            OIC
        IM OUTTA YR LOOP
        PI R SUM OF 4.0 AN DIFF OF PIADD AN PISUB
        VISIBLE "PI R: "
        VISIBLE PI
        FOUND YR PI
    KTHXBYE
    $$ LANGUAGE PLLOLCODE;
    

    I tried to rewrite the GRANT function shown above in PL/LOLCODE for this example, until I discovered that some of PL/LOLCODE’s limitations make it extremely difficult, if not impossible. So far as I know, PL/LOLCODE was the second language to support anonymous blocks, thanks to what turned out to be a relatively simple programming exercise. After finishing PL/LOLCODE’s DO support, I decided to do the same for PL/Perl. I wasn’t particularly surprised to find that PL/Perl was harder to extend than PL/LOLCODE; PL/Perl is a much more feature-rich (and hence, complicated) language and I wasn’t as familiar with its internals. However, after my initial submission and with helpful commentary from several other people, Andrew Dunstan tied off the loose ends and got it committed. It looks like this:

    DO $$
        my $row;
        my $rv = spi_exec_query(q{
            SELECT quote_ident(table_schema) || '.' || quote_ident(table_name) AS relname
            FROM information_schema.tables WHERE table_type = 'VIEW' AND table_schema = 'public'
        });
        my $nrows = $rv->{processed};
        foreach my $i (0 .. $nrows - 1) {
            my $row = $rv->{rows}[$rn];
            spi_exec_query("GRANT ALL ON $row->{relname} TO webuser");
        }
    $$ LANGUAGE plperl;
    

    DO wasn’t the only thing to come from the pgsql-hackers discussion I mentioned above. In PostgreSQL 9.0, the GRANT command has also been modified, so it’s now possible to grant permissions several objects in one stroke syntax. For instance:

    GRANT SELECT ON ALL TABLES IN SCHEMA public TO webuser
    

    database open-source postgres


    Comments