2009/10/23 Andrew Hall <andre...@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.steh...@gmail.com >> To: andre...@hotmail.com >> CC: pgsql-sql@postgresql.org >> >> Hello >> >> 2009/10/23 Andrew Hall <andre...@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