Hi,
 
I am currently looking into derby and being used to TSQL, PL/PGSQL can't quite figure out how to migrate one of my postgreSQL functions to derby.
 
I have searched the web, the derby wiki and found some (/ few) examples on how to write a function in derby, but none of the examples was covering what I was looking for.
 
Here is the PL/PGSQL funtion as a reference on what I am trying to rebuild in derby:
CREATE OR REPLACE FUNCTION rte."sfGetNextID"(in "vcIDName" name)
  RETURNS bigint AS
$BODY$
   DECLARE
      "iID" bigint;
   BEGIN
      SELECT "NextVal" INTO "iID" FROM rte."IDs" WHERE "IDName" = "vcIDName" FOR UPDATE;
      UPDATE rte."IDs" SET "NextVal" = "iID" + 1 WHERE "IDName" = "vcIDName";
      RETURN "iID";
   END;
   $BODY$
  LANGUAGE 'plpgsql' VOLATILE
 
This function is selecting the next ID to be assigned to a new row from an ID table. As the table has multiple rows, i.e. one row for each "entity" for which ID generation is accomplished via this table, it needs to receive the entity name (e.g. ClientID) as parameter. Select for update is used to ensure transaction isolation.
 
This function can be called from any other plpgsql function as follows: iClientID := rte."sfGetNextID"('CLIENTID') or iOrderID := rte."sfGetNextID"('ORDERID')
 
The base table used to store the last used value in this case would be
IDName    NextValue
===============
CLIENTID    10
ORDERID    1000
 
In derby I am getting as far as:
 
CREATE FUNCTION rte"sfGetNextID"("vcIDName" character varying(128))
RETURNS INTEGER
PARAMETER STYLE JAVA
but latest after this I am lost and do not know how to define the function element and tell derby this function will read and modify data (aside from problem that I also have mot found a sample for a select update anywhere).
 
Might be asking a lot here, but hope to find someone supporting.
 
Thanks