Table Valued Parameters

Hi,

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, I am able to do the following

--
-- 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
, aur_username VARCHAR2(30 CHAR)
, aur_is_account_enabled VARCHAR2(1 CHAR)
, aur_created_date DATE
, aur_updated_date TIMESTAMP
)
/

--
-- Create a data type which can store many instances of a single 'TY_APP_USER'
-- [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
-- 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, and treat that instance as a table, for example ...
--

CREATE OR REPLACE PROCEDURE prAddUsers
(
p_in_users IN tty_app_user
)
IS
BEGIN

INSERT
INTO
users
(
aur_id
, aur_username
, aur_is_account_enabled
, aur_created_by
, aur_created_date
)
SELECT
aur_id
, aur_username
, aur_is_account_enabled
, aur_created_by
, aur_created_date
FROM
TABLE
(
CAST
(
p_in_users AS tty_app_user
)
);

END prUpdateUsers;

My motivation for doing this is to reduce network round trips, instead of having 1 call per record to be sent to the db, 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 submission is a requirement that arises frequently [the example is just intended to demonstrate the principle!], and I would be grateful if anybody could help me to arrive at an optimal solution.

Cheers,

Andrew.

_________________________________________________________________
Download Messenger onto your mobile for free
http://clk.atdmt.com/UKM/go/174426567/direct/01/