1. Home
  2. Docs
  3. Oracle to PostgreSQL Migration
  4. PLSQL vs plpgsql
  5. Anonymous Block

Anonymous Block

Anonymous blocks are essential part of PL/SQL and plpgsql. They are mostly used in any database to unit test code, debugging issues or testing any business logic.

A block is defined by the keywords DECLARE, BEGIN, EXCEPTION, and END in both Oracle and PostgreSQL. These keywords divide the block into a declarative part, an executable part, and an exception-handling part.

Below shows the syntax of a block. A block must have the executable part and rest all are optional.

[ <<label>> ]
[ DECLARE
    declarations 
    ... ]
BEGIN
    statements;
    ...
[ EXCEPTION
    statements;
    ... ]
END [ label ];

The minimum block contains the BEGIN section with at least one executable statement. The executable statement can be NULL.

BEGIN
    statement;
END;

Executing block

OraclePostgreSQL

You can execute a block in PL/SQL as below.

BEGIN
    NULL;
END;
/

DO executes an anonymous code block. The syntax to execute a block is as below.

DO [ LANGUAGE lang_name ] plpgsqlblock

or

DO plpgsqlblock [ LANGUAGE lang_name ]
DO $$
BEGIN
    NULL;
END $$;

Specifying LANGUAGE is optional and you can include/exclude in any plpgsql block.

DO LANGUAGE plpgsql
$$
BEGIN
    NULL;
END $$;

LANGUAGE can be specified after DO or at the end.

DO $$
BEGIN
    NULL;
END; $$
LANGUAGE plpgsql;

Converting an Anonymous block

Anonymous blocks are never stored in either Oracle or PostgreSQL database. They are parsed each time you execute an anonymous block.

OraclePostgreSQL
SET SERVEROUTPUT ON;
DECLARE
    l_today DATE := sysdate;
BEGIN
    IF to_char(l_today,'D') < 4 THEN
        dbms_output.put_line(
           'Have a wonderful week');
    ELSE
         dbms_output.put_line(
            'Enjoy the rest of the week');
    END IF;
    dbms_output.put_line('today is '||
         to_char(l_today,'Day')||
         ' day '||to_char(l_today,'D')||
         ' of the week.');
END;
/

today is Sunday    day 1 of the week.

PL/SQL procedure successfully completed.
DO $$
DECLARE
    l_today DATE := current_date;
BEGIN
    IF to_char(l_today,'D') < 4 THEN
        raise notice 'Have a wonderful week';
    ELSE
         raise notice 'Enjoy the rest of the week';
    END IF;
    raise notice 'today is % day % of the week.', 
                 to_char(l_today,'Day'), 
                 to_char(l_today,'D');
END; $$
LANGUAGE plpgsql;

LINE 1: SELECT to_char(l_today,'D') < 4
                                    ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.
QUERY:  SELECT to_char(l_today,'D') < 4
CONTEXT:  PL/pgSQL function inline_code_block line 5 at IF

Adding explicit type casting to the above code.

DO $$
DECLARE
    l_today DATE := current_date;
BEGIN
    IF to_char(l_today,'D')::INT < 4 THEN
        raise notice 'Have a wonderful week';
    ELSE
         raise notice 'Enjoy the rest of the week';
    END IF;
    raise notice 'today is % day % of the week.', 
                 to_char(l_today,'Day'), 
                 to_char(l_today,'D');
END; $$
LANGUAGE plpgsql;

NOTICE:  Have a wonderful week
NOTICE:  today is Sunday    day 1 of the week.
DO
Was this article helpful to you? Yes No

How can we help?