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. 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. Cheers, Andrew. > Date: Fri, 23 Oct 2009 20:32:37 +0200 > Subject: Re: FW: [SQL] Table Valued Parameters > From: br...@zwartberg.com > To: andre...@hotmail.com > CC: pgsql-sql@postgresql.org > > 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 _________________________________________________________________ Download Messenger onto your mobile for free http://clk.atdmt.com/UKM/go/174426567/direct/01/
