Return-Path: Delivered-To: apmail-db-derby-user-archive@www.apache.org Received: (qmail 94892 invoked from network); 10 Aug 2009 21:22:03 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.3) by minotaur.apache.org with SMTP; 10 Aug 2009 21:22:03 -0000 Received: (qmail 21579 invoked by uid 500); 10 Aug 2009 21:22:10 -0000 Delivered-To: apmail-db-derby-user-archive@db.apache.org Received: (qmail 21490 invoked by uid 500); 10 Aug 2009 21:22:10 -0000 Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: List-Id: Reply-To: "Derby Discussion" Delivered-To: mailing list derby-user@db.apache.org Delivered-To: moderator for derby-user@db.apache.org Received: (qmail 28251 invoked by uid 99); 10 Aug 2009 19:00:21 -0000 X-ASF-Spam-Status: No, hits=2.2 required=10.0 tests=HTML_MESSAGE,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (nike.apache.org: local policy) From: To: Subject: creating a SQL function in derby Date: Mon, 10 Aug 2009 20:59:23 +0200 Message-ID: <3DCF2280161E481EB3E17BEB72798084@ThinkpadZ61m> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0000_01CA19FD.73480340" X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.6838 Importance: Normal Thread-Index: AcoZ7Kvw8ftWblHvSMKbldU+C4ZZzA== X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579 X-ID: Z2df4OZe8hK-gCgkD775wzngWeGBDv-HxTd4eGhP5x6AMZMPiAAv5B6gdbHzf6nweO X-TOI-MSGID: cb6e9164-9afa-471c-baeb-68e0971e5836 X-Virus-Checked: Checked by ClamAV on apache.org This is a multi-part message in MIME format. ------=_NextPart_000_0000_01CA19FD.73480340 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Hi, =20 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. =20 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. =20 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" =3D = "vcIDName" FOR UPDATE; UPDATE rte."IDs" SET "NextVal" =3D "iID" + 1 WHERE "IDName" =3D "vcIDName"; RETURN "iID"; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE =20 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. =20 This function can be called from any other plpgsql function as follows: iClientID :=3D rte."sfGetNextID"('CLIENTID') or iOrderID :=3D rte."sfGetNextID"('ORDERID') =20 The base table used to store the last used value in this case would be=20 IDName NextValue =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D CLIENTID 10 ORDERID 1000 =20 In derby I am getting as far as: =20 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). =20 Might be asking a lot here, but hope to find someone supporting. =20 Thanks ------=_NextPart_000_0000_01CA19FD.73480340 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Nachricht
Hi,
 
I am = currently=20 looking into derby and being used to TSQL, PL/PGSQL can't quite figure = out how=20 to migrate one of my postgreSQL functions to derby.
 
I have = searched the=20 web, the derby wiki and found some (/ few) examples on how to write a = function=20 in derby, but none of the examples was covering what I was looking=20 for.
 
Here is the=20 PL/PGSQL funtion as a reference on what I am trying to rebuild in=20 derby:
CREATE OR=20 REPLACE FUNCTION rte."sfGetNextID"(in "vcIDName" name)
  RETURNS = bigint=20 AS
$BODY$
   DECLARE
      = "iID"=20 bigint;
   BEGIN
      SELECT = "NextVal"=20 INTO "iID" FROM rte."IDs" WHERE "IDName" =3D "vcIDName" FOR=20 UPDATE;
      UPDATE rte."IDs" SET "NextVal" = =3D "iID"=20 + 1 WHERE "IDName" =3D "vcIDName";
      = RETURN=20 "iID";
   END;
   $BODY$
  LANGUAGE = 'plpgsql'=20 VOLATILE
 
This function=20 is selecting the next ID to be assigned to a new row from an ID table. = As the=20 table has multiple rows, i.e. one row for each "entity" for which ID = generation=20 is accomplished via this table, it needs to receive the entity name = (e.g.=20 ClientID) as parameter. Select for update is used to ensure transaction=20 isolation.
 
This function=20 can be called from any other plpgsql function as follows: iClientID :=3D = rte."sfGetNextID"('CLIENTID') or iOrderID :=3D=20 rte."sfGetNextID"('ORDERID')
 
The base table=20 used to store the last used value in this case would be=20
IDName   =20 NextValue
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D<= /SPAN>
CLIENTID   =20 10
ORDERID   =20 1000
 
In derby I am=20 getting as far as:
 
CREATE=20 FUNCTION rte"sfGetNextID"("vcIDName" character=20 varying(128))
RETURNS=20 INTEGER
PARAMETER=20 STYLE JAVA
but latest = after this I am=20 lost and do not know how to define the function element and tell derby = this=20 function will read and modify data (aside from problem that I = also have=20 mot found a sample for a select update=20 anywhere).
 
Might be asking a lot here, but hope to find someone=20 supporting.
 
Thanks
------=_NextPart_000_0000_01CA19FD.73480340--