FW: Table Valued Parameters - Mailing list pgsql-sql

Hi Pavel,<br /><br />many thanks for the very prompt reply.<br /><br />I was under the impression that a refcursor was
apointer to a dataset already resident on the database, and were used to return a reference to a dataset resident in
thedatabase to a client application.<br /><br />What I am trying to do is build a table valued variable in a client
applicationthen submit it to a stored procedure resident on the database, and have that stored procedure manipulate it
asthough it were a table [be it inserting, updating or deleting based upon its contents].<br /><br />Is this
possible?<br/><br />I apologise for not making my question more clear.<br /><br />Thanks,<br /><br />Andrew.<br /><br
/>>Date: Fri, 23 Oct 2009 20:10:48 +0200<br />> Subject: Re: [SQL] Table Valued Parameters<br />> From:
pavel.stehule@gmail.com<br/>> To: andrewah@hotmail.com<br />> CC: pgsql-sql@postgresql.org<br />> <br />>
Hello<br/>> <br />> 2009/10/23 Andrew Hall <andrewah@hotmail.com>:<br />> > Hi,<br />> ><br
/>>> I was wondering whether anybody would be able to advise me on how (if it is<br />> > possible) to port
somefunctionality from Oracle?<br />> ><br />> > This is just an example - in Oracle, I am able to do the
following<br/>> ><br />> <br />> Use refcursor, please.<br />> <br />>
http://www.postgresql.org/docs/8.4/static/plpgsql-cursors.html<br/>> <br />> Regards<br />> Pavel Stehule<br
/>><br />> > --<br />> > -- Create a data type which replicates the data structure of a single user<br
/>>> in my application.<br />> > -- I know that this 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 />> > -- Create a data type which can store many instances of a
single<br/>> > 'TY_APP_USER'<br />> > -- [essentially this is a table valued data type]. An instance of
thisdata<br />> > type can be<br />> > -- created and populated by the client application [a java based one
inmy<br />> > 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
OFTY_APP_USER<br />> > /<br />> ><br />> > --<br />> > -- Next define a stored procedure which
canaccept an instance of a<br />> > TTY_APP_USER data<br />> > -- type, and treat that instance as a table,
forexample ...<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,
insteadof<br />> > having 1 call per record to be sent to the db, I can have 1 call passing all<br />> >
valueswhich I wish to store in the database.<br />> ><br />> > Sending multiple records to the database as
aresult of a single form<br />> > submission is a requirement that arises frequently [the example is just<br
/>>> intended to demonstrate the principle!], and I would be grateful if anybody<br />> > could help me to
arriveat an optimal solution.<br />> ><br />> > Cheers,<br />> ><br />> > Andrew.<br />>
><br/>> ><br />> ><br />> ><br />> ><br />> ><br />> >
________________________________<br/>> > Download Messenger onto your mobile for free. Learn more.<br />> <br
/>>-- <br />> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)<br />> To make changes to your
subscription:<br/>> http://www.postgresql.org/mailpref/pgsql-sql<br /><br /><hr />Chat to your friends for free on
selectedmobiles. <a href="http://clk.atdmt.com/UKM/go/174426567/direct/01/">Learn more.</a><br /><hr />Chat to your
friendsfor free on selected mobiles. <a href="http://clk.atdmt.com/UKM/go/174426567/direct/01/" target="_new">Learn
more.</a>