From: Andrew Hall <andrewah@hotmail.com>
To: pgsql-sql@postgresql.org
Subject: Table Valued Parameters
Date: Fri, 23 Oct 2009 18:03:00 +0000
Message-ID: <COL122-W14509AEC755F5710A3CA19CDBD0@phx.gbl> (raw)
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/=
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: andrewah@hotmail.com
Subject: Re: Table Valued Parameters
In-Reply-To: <COL122-W14509AEC755F5710A3CA19CDBD0@phx.gbl>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox