db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From <Thomas.K.H...@t-online.de>
Subject creating a SQL function in derby
Date Mon, 10 Aug 2009 18:59:23 GMT
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

Mime
View raw message