Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 3B66E6339C7 for ; Fri, 23 Oct 2009 15:04:25 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 41812-05 for ; Fri, 23 Oct 2009 18:04:11 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from col0-omc2-s9.col0.hotmail.com (col0-omc2-s9.col0.hotmail.com [65.55.34.83]) by mail.postgresql.org (Postfix) with ESMTP id DA2C5633475 for ; Fri, 23 Oct 2009 15:04:13 -0300 (ADT) Received: from COL122-W14 ([65.55.34.72]) by col0-omc2-s9.col0.hotmail.com with Microsoft SMTPSVC(6.0.3790.3959); Fri, 23 Oct 2009 11:03:01 -0700 Message-ID: Content-Type: multipart/alternative; boundary="_3015250d-69d2-4b4c-b98c-29db4112b829_" X-Originating-IP: [86.154.68.198] From: Andrew Hall To: Subject: Table Valued Parameters Date: Fri, 23 Oct 2009 18:03:00 +0000 Importance: Normal MIME-Version: 1.0 X-OriginalArrivalTime: 23 Oct 2009 18:03:01.0161 (UTC) FILETIME=[0F4DC990:01CA540B] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.598 tagged_above=-10 required=5 tests=BAYES_00=-2.599, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200910/119 X-Sequence-Number: 33318 --_3015250d-69d2-4b4c-b98c-29db4112b829_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi=2C I was wondering whether anybody would be able to advise me on how (if it is= possible) to port some functionality from Oracle? This is just an example - in Oracle=2C I am able to do the following=20 -- -- Create a data type which replicates the data structure of a single user = in my application. -- I know that this can be done using PostgreSQL. -- CREATE TYPE TY_APP_USER AS OBJECT ( aur_id INT =2C aur_username VARCHAR2(30 CHAR) =2C aur_is_account_enabled VARCHAR2(1 CHAR) =2C aur_created_date DATE =2C aur_updated_date TIMESTAMP ) / -- -- Create a data type which can store many instances of a single 'TY_APP_US= ER'=20 -- [essentially this is a table valued data type]. An instance of this data= type can be -- created and populated by the client application [a java based one in my = case]. -- -- I can't find any reference to something=20 -- similar to this using postgreSQL. -- CREATE TYPE TTY_APP_USER AS TABLE OF TY_APP_USER / -- -- Next define a stored procedure which can accept an instance of a TTY_APP= _USER data -- type=2C and treat that instance as a table=2C for example ... -- CREATE OR REPLACE PROCEDURE prAddUsers ( p_in_users IN tty_app_user ) IS BEGIN INSERT INTO users ( aur_id =2C aur_username =2C aur_is_account_enabled =2C aur_created_by =2C aur_created_date ) SELECT aur_id =2C aur_username =2C aur_is_account_enabled =2C aur_created_by =2C aur_created_date FROM TABLE ( CAST ( p_in_users AS tty_app_user ) )=3B END prUpdateUsers=3B My motivation for doing this is to reduce network round trips=2C instead of= having 1 call per record to be sent to the db=2C I can have 1 call passing= all values which I wish to store in the database. Sending multiple records to the database as a result of a single form submi= ssion is a requirement that arises frequently [the example is just intended= to demonstrate the principle!]=2C and I would be grateful if anybody could= help me to arrive at an optimal solution. Cheers=2C Andrew. =20 _________________________________________________________________ Download Messenger onto your mobile for free http://clk.atdmt.com/UKM/go/174426567/direct/01/= --_3015250d-69d2-4b4c-b98c-29db4112b829_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi=2C

I was wondering whether anybody would be able to advise me on = how (if it is possible) to port some functionality from Oracle?

This= is just an example - in Oracle=2C I am able to do the following

--=
-- Create a data type which replicates the data structure of a single u= ser in my application.
-- I know that this can be done using PostgreSQL.=
--

CREATE TYPE TY_APP_USER AS OBJECT
(
 =3B aur_id&nbs= p=3B =3B =3B =3B =3B =3B =3B =3B =3B = =3B =3B =3B =3B =3B =3B =3B INT
=2C aur_username=  =3B =3B =3B =3B =3B =3B =3B =3B =3B&nb= sp=3B VARCHAR2(30 =3B CHAR)
=2C aur_is_account_enabled VARCHAR2(1&nb= sp=3B =3B CHAR)
=2C aur_created_date =3B =3B =3B =3B=  =3B =3B DATE
=2C aur_updated_date =3B =3B =3B = =3B =3B =3B TIMESTAMP
)
/

--
-- Create a data type = which can store many instances of a single 'TY_APP_USER'
-- [essentiall= y this is a table valued data type]. An instance of this data type can be-- created and populated by the client application [a java based one in m= y case].
--
-- I can't find any reference to something
-- similar= to this using postgreSQL.
--

CREATE TYPE TTY_APP_USER AS TABLE O= F TY_APP_USER
/

--
-- Next define a stored procedure which can= accept an instance of a TTY_APP_USER data
-- type=2C and treat that ins= tance as a table=2C for example ...
--

CREATE OR REPLACE PROCEDUR= E prAddUsers
(
 =3B p_in_users IN tty_app_user
)
IS
BEGI= N

 =3B INSERT
 =3B =3B =3B INTO
 =3B = =3B =3B =3B =3B =3B =3B =3B users
 =3B = =3B =3B =3B =3B =3B (
 =3B =3B =3B =3B&n= bsp=3B =3B =3B =3B aur_id
 =3B =3B =3B =3B&n= bsp=3B =3B =2C aur_username
 =3B =3B =3B =3B =3B=  =3B =2C aur_is_account_enabled
 =3B =3B =3B =3B&nbs= p=3B =3B =2C aur_created_by
 =3B =3B =3B =3B =3B=  =3B =2C aur_created_date
 =3B =3B =3B =3B =3B&n= bsp=3B )
 =3B SELECT
 =3B =3B =3B =3B =3B&nbs= p=3B =3B =3B aur_id
 =3B =3B =3B =3B =3B&nbs= p=3B =2C aur_username
 =3B =3B =3B =3B =3B =3B = =2C aur_is_account_enabled
 =3B =3B =3B =3B =3B = =3B =2C aur_created_by
 =3B =3B =3B =3B =3B =3B = =2C aur_created_date
 =3B =3B =3B FROM
 =3B =3B&n= bsp=3B =3B =3B =3B =3B =3B TABLE
 =3B =3B&nb= sp=3B =3B =3B =3B =3B =3B (
 =3B =3B =3B=  =3B =3B =3B =3B =3B =3B =3B CAST
 =3B&n= bsp=3B =3B =3B =3B =3B =3B =3B =3B =3B ( =3B =3B =3B =3B =3B =3B =3B =3B =3B&n= bsp=3B =3B =3B p_in_users AS tty_app_user
 =3B =3B = =3B =3B =3B =3B =3B =3B =3B =3B )
 =3B&n= bsp=3B =3B =3B =3B =3B =3B =3B )=3B

END prUp= dateUsers=3B

My motivation for doing this is to reduce network round= trips=2C instead of having 1 call per record to be sent to the db=2C I can= have 1 call passing all values which I wish to store in the database.
<= br>Sending multiple records to the database as a result of a single form su= bmission is a requirement that arises frequently [the example is just inten= ded to demonstrate the principle!]=2C and I would be grateful if anybody co= uld help me to arrive at an optimal solution.

Cheers=2C

Andre= w.






Download Messenger onto y= our mobile for free. Learn more. = --_3015250d-69d2-4b4c-b98c-29db4112b829_--