Re: Table Valued Parameters
От | Brian Modra |
---|---|
Тема | Re: Table Valued Parameters |
Дата | |
Msg-id | 5a9699850910240512x6756a2d8ka5f05eb980c76d91@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: Table Valued Parameters (Andrew Hall <andrewah@hotmail.com>) |
Список | pgsql-sql |
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/
В списке pgsql-sql по дате отправления: