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
