Re: Table Valued Parameters - Mailing list pgsql-sql

Thanks Bruce,<br /><br />what I was looking for was the postgreSQL equivalent of table-valued parameters from SQL
Server2008 (http://technet.microsoft.com/en-us/library/bb510489.aspx) or tables of Oracle Objects from Oracle.  (Or
somethingthat would allow me to achieve the same effect).<br /><br />The example that you've provided only allows a
'type'variable containing 1 record to be submitted to a plpgsql procedure per invocation of that procedure.<br /><br
/>Anyhow,Pavel Stehule has kindly explained that while there is no exact equivalent in postgreSQL - but has recommended
thatI investigate the array functionality, and the COPY command.<br /><br />In retrospect, I should of just asked how
onewould go about submitting multiple records of the same type/signature to a plpgsql procedure with a single
invocation(of that plpgsql procedure) from a client application.<br /><br />All the same - I would like to express my
thanksto you for taking the time to suggest an approach.<br /><br />Cheers,<br /><br />Andrew.<br /><br />> Date:
Fri,23 Oct 2009 20:32:37 +0200<br />> Subject: Re: FW: [SQL] Table Valued Parameters<br />> From:
brian@zwartberg.com<br/>> To: andrewah@hotmail.com<br />> CC: pgsql-sql@postgresql.org<br />> <br />>
2009/10/23Andrew Hall <andrewah@hotmail.com>:<br />> > Hi Pavel,<br />> ><br />> > many thanks
forthe very prompt reply.<br />> ><br />> > I was under the impression that a refcursor was a pointer to a
dataset<br/>> > already resident on the database, and were used to return a reference to a<br />> > dataset
residentin the database to a client application.<br />> ><br />> > What I am trying to do is build a table
valuedvariable in a client<br />> > application then submit it to a stored procedure resident on the database,<br
/>>> and have that stored procedure manipulate it as though it were a table [be<br />> > it inserting,
updatingor deleting based upon its contents].<br />> ><br />> > Is this possible?<br />> ><br />>
>I apologise for not making my question more clear.<br />> <br />> Is the following too simplistic (maybe I
havenot understood your<br />> question either, but it seems that postgresql makes it so simple, that<br />>
"problems"you had to solve in ORACLKE, aren't a "problem" in<br />> postgresql.)<br />> <br />> create type
ty_app_useras (<br />> aur_id integer,<br />> ... etc<br />> );<br />> <br />> create or replace
functionprAddUsers ( p_in_users tty_app_user )<br />> returns void as $$<br />> declare<br />> begin<br />>
insertinto users (aur_id ... etc ) values (p_in_users.aur_id, etc...);<br />> end;<br />> $$ language plpgsql;<br
/>><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
wonderingwhether anybody would be able to advise me on how (if it<br />> >> > is<br />> >> >
possible)to port some functionality 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
replicatesthe data structure of a single<br />> >> > 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_enabledVARCHAR2(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
this<br/>> >> > data<br />> >> > type can be<br />> >> > -- created and populated
bythe client application [a java based one in<br />> >> > my<br />> >> > case].<br />>
>>> --<br />> >> > -- I can't find any reference to something<br />> >> > -- similar
tothis using postgreSQL.<br />> >> > --<br />> >> ><br />> >> > CREATE TYPE
TTY_APP_USERAS TABLE OF TY_APP_USER<br />> >> > /<br />> >> ><br />> >> > --<br
/>>>> > -- Next define a stored procedure which can accept an instance of a<br />> >> >
TTY_APP_USERdata<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
motivationfor doing this is to reduce network round trips, instead<br />> >> > of<br />> >> >
having1 call per record to be sent to the db, I can have 1 call passing<br />> >> > all<br />> >>
>values which I wish to store in the database.<br />> >> ><br />> >> > Sending multiple
recordsto the database as a result 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<br/>> >> > anybody<br />> >> > could help me to arrive at an optimal solution.<br />>
>>><br />> >> > Cheers,<br />> >> ><br />> >> > Andrew.<br />> >>
><br/>> >> ><br />> >> ><br />> >> ><br />> >> ><br />> >>
><br/>> >> > ________________________________<br />> >> > Download Messenger onto your
mobilefor 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 />> > ________________________________<br />>
>Chat to your friends for free on selected mobiles. Learn more.<br />> > ________________________________<br
/>>> Chat to your friends for free on selected mobiles. Learn more.<br />> <br />> <br />> <br />> --
<br/>> Brian Modra Land line: +27 23 5411 462<br />> Mobile: +27 79 69 77 082<br />> 5 Jan Louw Str, Prince
Albert,6930<br />> Postal: P.O. Box 2, Prince Albert 6930<br />> South Africa<br />>
http://www.zwartberg.com/<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 />Download Messenger onto your mobile for free. <a
href="http://clk.atdmt.com/UKM/go/174426567/direct/01/"target="_new">Learn more.</a>