Thread: Table Valued Parameters
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>
Hello 2009/10/23 Andrew Hall <andrewah@hotmail.com>: > 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 > Use refcursor, please. http://www.postgresql.org/docs/8.4/static/plpgsql-cursors.html Regards Pavel Stehule > -- > -- 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. Learn more.
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>
2009/10/23 Andrew Hall <andrewah@hotmail.com>: > Hi Pavel, > > many thanks for the very prompt reply. > > I was under the impression that a refcursor was a pointer to a dataset > already resident on the database, and were used to return a reference to a > dataset resident in the database to a client application. > > What I am trying to do is build a table valued variable in a client > application then submit it to a stored procedure resident on the database, > and have that stored procedure manipulate it as though it were a table [be > it inserting, updating or deleting based upon its contents]. > > Is this possible? > > I apologise for not making my question more clear. Is the following too simplistic (maybe I have not understood your question either, but it seems that postgresql makes it so simple, that "problems" you had to solve in ORACLKE, aren't a "problem" in postgresql.) create type ty_app_user as ( aur_id integer, ... etc ); create or replace function prAddUsers ( p_in_users tty_app_user ) returns void as $$ declare begin insert into users (aur_id ... etc ) values (p_in_users.aur_id, etc...); end; $$ language plpgsql; > Thanks, > > Andrew. > >> Date: Fri, 23 Oct 2009 20:10:48 +0200 >> Subject: Re: [SQL] Table Valued Parameters >> From: pavel.stehule@gmail.com >> To: andrewah@hotmail.com >> CC: pgsql-sql@postgresql.org >> >> Hello >> >> 2009/10/23 Andrew Hall <andrewah@hotmail.com>: >> > 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 >> > >> >> Use refcursor, please. >> >> http://www.postgresql.org/docs/8.4/static/plpgsql-cursors.html >> >> Regards >> Pavel Stehule >> >> > -- >> > -- 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. Learn more. >> >> -- >> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-sql > > ________________________________ > Chat to your friends for free on selected mobiles. Learn more. > ________________________________ > Chat to your friends for free on selected mobiles. Learn more. -- Brian Modra Land line: +27 23 5411 462 Mobile: +27 79 69 77 082 5 Jan Louw Str, Prince Albert, 6930 Postal: P.O. Box 2, Prince Albert 6930 South Africa http://www.zwartberg.com/
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>
Andrew Hall wrote: > 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 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. The following may not do anything interesting, but it does show arrays of composite types, which is what you are after. To prevent quoting insanity, I recommend the ARRAY[] constructor rather than array literals. You do need the explicit typecasts. Oh - and version 8.3 or higher for arrays of compound types. BEGIN; CREATE TYPE typ1 AS (i integer, t text); CREATE FUNCTION print_array(a typ1[]) RETURNS void AS $$ DECLARE m int; n int; i int; e typ1; BEGIN m := array_lower(a, 1); n := array_upper(a, 1); FOR i IN m .. n LOOP e := a[i]; RAISE NOTICE '% -%', e.i, e.t; END LOOP; RETURN; END; $$ LANGUAGE plpgsql; SELECT print_array(ARRAY[ '(1,"abc")'::typ1, '(2,"def")'::typ1 ]); SELECT print_array(ARRAY[ '(1,"abc")', '(2,"def")' ]::typ1[]); ROLLBACK; -- Richard Huxton Archonet Ltd
2009/10/24 Andrew Hall <andrewah@hotmail.com>: > Thanks Bruce, > > what I was looking for was the postgreSQL equivalent of table-valued > parameters from SQL Server 2008 > (http://technet.microsoft.com/en-us/library/bb510489.aspx) or tables of > Oracle Objects from Oracle. (Or something that would allow me to achieve > the same effect). > > 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. > > Anyhow, Pavel Stehule has kindly explained that while there is no exact > equivalent in postgreSQL - but has recommended that I investigate the array > functionality, and the COPY command. Maybe you could also use a temporary table, (create temporary table ... on commit drop) > In retrospect, I should of just asked how one would 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. > > All the same - I would like to express my thanks to you for taking the time > to suggest an approach. Its a pleasure. > Cheers, > > Andrew. > >> Date: Fri, 23 Oct 2009 20:32:37 +0200 >> Subject: Re: FW: [SQL] Table Valued Parameters >> From: brian@zwartberg.com >> To: andrewah@hotmail.com >> CC: pgsql-sql@postgresql.org >> >> 2009/10/23 Andrew Hall <andrewah@hotmail.com>: >> > Hi Pavel, >> > >> > many thanks for the very prompt reply. >> > >> > I was under the impression that a refcursor was a pointer to a dataset >> > already resident on the database, and were used to return a reference to >> > a >> > dataset resident in the database to a client application. >> > >> > What I am trying to do is build a table valued variable in a client >> > application then submit it to a stored procedure resident on the >> > database, >> > and have that stored procedure manipulate it as though it were a table >> > [be >> > it inserting, updating or deleting based upon its contents]. >> > >> > Is this possible? >> > >> > I apologise for not making my question more clear. >> >> Is the following too simplistic (maybe I have not understood your >> question either, but it seems that postgresql makes it so simple, that >> "problems" you had to solve in ORACLKE, aren't a "problem" in >> postgresql.) >> >> create type ty_app_user as ( >> aur_id integer, >> ... etc >> ); >> >> create or replace function prAddUsers ( p_in_users tty_app_user ) >> returns void as $$ >> declare >> begin >> insert into users (aur_id ... etc ) values (p_in_users.aur_id, etc...); >> end; >> $$ language plpgsql; >> >> >> > Thanks, >> > >> > Andrew. >> > >> >> Date: Fri, 23 Oct 2009 20:10:48 +0200 >> >> Subject: Re: [SQL] Table Valued Parameters >> >> From: pavel.stehule@gmail.com >> >> To: andrewah@hotmail.com >> >> CC: pgsql-sql@postgresql.org >> >> >> >> Hello >> >> >> >> 2009/10/23 Andrew Hall <andrewah@hotmail.com>: >> >> > 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 >> >> > >> >> >> >> Use refcursor, please. >> >> >> >> http://www.postgresql.org/docs/8.4/static/plpgsql-cursors.html >> >> >> >> Regards >> >> Pavel Stehule >> >> >> >> > -- >> >> > -- 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. Learn more. >> >> >> >> -- >> >> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) >> >> To make changes to your subscription: >> >> http://www.postgresql.org/mailpref/pgsql-sql >> > >> > ________________________________ >> > Chat to your friends for free on selected mobiles. Learn more. >> > ________________________________ >> > Chat to your friends for free on selected mobiles. Learn more. >> >> >> >> -- >> Brian Modra Land line: +27 23 5411 462 >> Mobile: +27 79 69 77 082 >> 5 Jan Louw Str, Prince Albert, 6930 >> Postal: P.O. Box 2, Prince Albert 6930 >> South Africa >> http://www.zwartberg.com/ >> >> -- >> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-sql > > ________________________________ > Download Messenger onto your mobile for free. Learn more. -- Brian Modra Land line: +27 23 5411 462 Mobile: +27 79 69 77 082 5 Jan Louw Str, Prince Albert, 6930 Postal: P.O. Box 2, Prince Albert 6930 South Africa http://www.zwartberg.com/
Richard Huxton <dev@archonet.com> writes:
> To prevent quoting insanity, I recommend the ARRAY[] constructor rather
> than array literals. You do need the explicit typecasts.
By the same token, you might want to use ROW() rather than
composite-type literal syntax for the array elements.
> Oh - and version 8.3 or higher for arrays of compound types.
I think also that casting the array, rather than the individual rows,
only works as of 8.4; ie in 8.3 you have to follow the first example:
> SELECT print_array(ARRAY[ '(1,"abc")'::typ1, '(2,"def")'::typ1 ]);
> SELECT print_array(ARRAY[ '(1,"abc")', '(2,"def")' ]::typ1[]);
In this case there's not much real difference, but with a lot of
array elements the individual casts get tedious.
regards, tom lane