Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id B65CE632A90 for ; Sat, 24 Oct 2009 09:12:29 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 06382-06 for ; Sat, 24 Oct 2009 12:12:12 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mail-gx0-f218.google.com (mail-gx0-f218.google.com [209.85.217.218]) by mail.postgresql.org (Postfix) with ESMTP id DA6E1632471 for ; Sat, 24 Oct 2009 09:12:18 -0300 (ADT) Received: by gxk10 with SMTP id 10so9006610gxk.3 for ; Sat, 24 Oct 2009 05:12:18 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=googlemail.com; s=gamma; h=domainkey-signature:mime-version:sender:received:in-reply-to :references:date:x-google-sender-auth:message-id:subject:from:to:cc :content-type:content-transfer-encoding; bh=CxXROZz6/m2en8UsjV7RfW7ZNsL0jhME4f/nC9ck81Q=; b=QSVSfV9/X0QvDZHIykMm9jt563BJWicL0oXACRcj7/mqa8HTSlXVHnUPjjPzzhxMXp PtuLGK0pE0RfPb5QTwCZN7LQWCETTXJ4M1k/oGLeviYMXkL2I3P7eEBUeycFO5nFFjrf fV/CLw7nGCo4xvI+hA7YcfrYnERrBdNz1O9wY= DomainKey-Signature: a=rsa-sha1; c=nofws; d=googlemail.com; s=gamma; h=mime-version:sender:in-reply-to:references:date :x-google-sender-auth:message-id:subject:from:to:cc:content-type :content-transfer-encoding; b=rFnGcqOmxHNmq4E02C5dwslNkmRTxB58E8Yse6RyQfOTfy7ftZB9hMFWgsHNMw98wt gQLCKLe6/0JdDx7wMvj2gxAf9+GZVZIfJckc6U6YEpN5uBqK75Gh0s+kQWbPu78HzS/h NT55V5cN3SMcGX7hidK+I2KJWyi9KPvNGhfWY= MIME-Version: 1.0 Received: by 10.150.129.4 with SMTP id b4mr4404021ybd.193.1256386338042; Sat, 24 Oct 2009 05:12:18 -0700 (PDT) In-Reply-To: References: <162867790910231110y3e86a5a4o1584e1f4c97b0625@mail.gmail.com> <5a9699850910231132t58f36453o735a8287c7aed64c@mail.gmail.com> Date: Sat, 24 Oct 2009 14:12:18 +0200 X-Google-Sender-Auth: fcfca844736b7afa Message-ID: <5a9699850910240512x6756a2d8ka5f05eb980c76d91@mail.gmail.com> Subject: Re: Table Valued Parameters From: Brian Modra To: Andrew Hall Cc: PostgreSQL Mailing List Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.599 tagged_above=-10 required=5 tests=BAYES_00=-2.599 X-Spam-Level: X-Archive-Number: 200910/126 X-Sequence-Number: 33325 2009/10/24 Andrew Hall : > 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.=A0 (Or something that would allow me to achie= ve > 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 arr= ay > 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 ti= me > 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 : >> > 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 : >> >> > 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 sing= le >> >> > user >> >> > in my application. >> >> > -- I know that this can be done using PostgreSQL. >> >> > -- >> >> > >> >> > CREATE TYPE TY_APP_USER AS OBJECT >> >> > ( >> >> > =A0 aur_id=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 INT >> >> > , aur_username=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 VARCHAR2(30=A0 CHAR) >> >> > , aur_is_account_enabled VARCHAR2(1=A0=A0 CHAR) >> >> > , aur_created_date=A0=A0=A0=A0=A0=A0 DATE >> >> > , aur_updated_date=A0=A0=A0=A0=A0=A0 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 on= e >> >> > 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 >> >> > ( >> >> > =A0 p_in_users IN tty_app_user >> >> > ) >> >> > IS >> >> > BEGIN >> >> > >> >> > =A0 INSERT >> >> > =A0=A0=A0 INTO >> >> > =A0=A0=A0=A0=A0=A0=A0=A0 users >> >> > =A0=A0=A0=A0=A0=A0 ( >> >> > =A0=A0=A0=A0=A0=A0=A0=A0 aur_id >> >> > =A0=A0=A0=A0=A0=A0 , aur_username >> >> > =A0=A0=A0=A0=A0=A0 , aur_is_account_enabled >> >> > =A0=A0=A0=A0=A0=A0 , aur_created_by >> >> > =A0=A0=A0=A0=A0=A0 , aur_created_date >> >> > =A0=A0=A0=A0=A0=A0 ) >> >> > =A0 SELECT >> >> > =A0=A0=A0=A0=A0=A0=A0=A0 aur_id >> >> > =A0=A0=A0=A0=A0=A0 , aur_username >> >> > =A0=A0=A0=A0=A0=A0 , aur_is_account_enabled >> >> > =A0=A0=A0=A0=A0=A0 , aur_created_by >> >> > =A0=A0=A0=A0=A0=A0 , aur_created_date >> >> > =A0=A0=A0 FROM >> >> > =A0=A0=A0=A0=A0=A0=A0=A0 TABLE >> >> > =A0=A0=A0=A0=A0=A0=A0=A0 ( >> >> > =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 CAST >> >> > =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 ( >> >> > =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 p_in_users AS tty_app_user >> >> > =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 ) >> >> > =A0=A0=A0=A0=A0=A0=A0=A0 ); >> >> > >> >> > 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 fo= rm >> >> > 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. --=20 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/