Table Valued Parameters - Mailing list pgsql-sql

Hi,<br /><br />I was wondering whether anybody would be able to advise me on how (if it is possible) to port some
functionalityfrom Oracle?<br /><br />This is just an example - in Oracle, I am able to do the following <br /><br
/>--<br/>-- Create a data type which replicates the data structure of a single user in my application.<br />-- I know
thatthis can be done using PostgreSQL.<br />--<br /><br />CREATE TYPE TY_APP_USER AS OBJECT<br />(<br /> 
aur_id                INT<br />, aur_username           VARCHAR2(30  CHAR)<br />, aur_is_account_enabled VARCHAR2(1  
CHAR)<br/>, aur_created_date       DATE<br />, aur_updated_date       TIMESTAMP<br />)<br />/<br /><br />--<br />--
Createa data type which can store many instances of a single 'TY_APP_USER' <br />-- [essentially this is a table valued
datatype]. An instance of this data type can be<br />-- created and populated by the client application [a java based
onein my case].<br />--<br />-- I can't find any reference to something <br />-- similar to this using postgreSQL.<br
/>--<br/><br />CREATE TYPE TTY_APP_USER AS TABLE OF TY_APP_USER<br />/<br /><br />--<br />-- Next define a stored
procedurewhich can accept an instance of a TTY_APP_USER data<br />-- type, and treat that instance as a table, for
example...<br />--<br /><br />CREATE OR REPLACE PROCEDURE prAddUsers<br />(<br />  p_in_users IN tty_app_user<br />)<br
/>IS<br/>BEGIN<br /><br />  INSERT<br />    INTO<br />         users<br />       (<br />         aur_id<br />       ,
aur_username<br/>       , aur_is_account_enabled<br />       , aur_created_by<br />       , aur_created_date<br
/>      )<br />  SELECT<br />         aur_id<br />       , aur_username<br />       , aur_is_account_enabled<br
/>      , aur_created_by<br />       , aur_created_date<br />    FROM<br />         TABLE<br />         (<br
/>          CAST<br />           (<br />             p_in_users AS tty_app_user<br />           )<br />         );<br
/><br/>END prUpdateUsers;<br /><br />My motivation for doing this is to reduce network round trips, instead of having 1
callper record to be sent to the db, I can have 1 call passing all values which I wish to store in the database.<br
/><br/>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
meto arrive at an optimal solution.<br /><br />Cheers,<br /><br />Andrew.<br /><br /><br /><br /><br /><br /><br /><hr
/>DownloadMessenger onto your mobile for free. <a href="http://clk.atdmt.com/UKM/go/174426567/direct/01/"
target="_new">Learnmore.</a>