Part 3: And now for some procedural SQL

Let's capture what we defined in those separate java methods added in Part 2 in 1 PL-SQL function.

Connect to your database

.. with 'psql' and execute the 'create language' command to tell Postgresql to speak PL-SQL too (you only have to to do this once, maybe you already did so in the past).

psql -h localhost -U todo
create language 'plpgsql';

Define the function

Don't quit that psql session yet, but copy/paste the following into it..

create or replace
function add_new_task( a_task_description varchar ) returns integer
as $PLSQL$
    declare
         l_next_seq_value numeric;
         l_max_rank numeric;
    begin

    --  get the next sequence Value
    select nextval('seq_todo')
    into   l_next_seq_value;

    -- suss out the max rank
    select max(rank)
    into   l_max_rank
    from t_todo;


    -- and insert 
    insert into t_todo(todo_id, description, rank)
    values(l_next_seq_value, a_task_description, l_max_rank+1);

    return l_next_seq_value;
end;
$PLSQL$ language 'plpgsql';

On successful creation of the function, psql will reply with 'CREATE FUNCTION'.

Adapt the mlg file

Remove the functions getNextSequenceValue(), getMaxRank(), insertTask(), addNewTask() that we defined in previous section, and add the following:

int addNewTask(String task) target postgresql  
[[
    { ? = call add_new_task(?) }
]]

Note the double square brackets used as delimiters for calling PL-SQL.

You probably also noted the declaration of 'target postgresql': since some (but not all) JDBC code is different for the different target databases (postgresql and oracle) there has to be some way to tell the code-generator which database to target. For this, you add 'target postgresql' either to the method definition or to the class definition. In my projects I mostly put the definition at class level, because my code usually targets only 1 brand of database.

Call upon amalegeni again to regenerate the bean(s) and service classes.

cd ~/workspace/Todo
amalegeni -t template/gwt.stg src

You know the drill now: hit the F5 button in Eclipse to refresh the src directory.

Your Todo app is ready again..

.. so fire it up, and hit that 'Add' button.

Source code

Your Todo.mlg looks a whole lot simpler now!