Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id D1689633475 for ; Fri, 23 Oct 2009 15:22:31 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 42521-07 for ; Fri, 23 Oct 2009 18:22:23 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from col0-omc2-s14.col0.hotmail.com (col0-omc2-s14.col0.hotmail.com [65.55.34.88]) by mail.postgresql.org (Postfix) with ESMTP id C0334633341 for ; Fri, 23 Oct 2009 15:22:22 -0300 (ADT) Received: from COL122-W41 ([65.55.34.73]) by col0-omc2-s14.col0.hotmail.com with Microsoft SMTPSVC(6.0.3790.3959); Fri, 23 Oct 2009 11:22:21 -0700 Message-ID: Content-Type: multipart/alternative; boundary="_966b6d69-92d5-45f9-b705-c8b1446ab623_" X-Originating-IP: [86.154.68.198] From: Andrew Hall To: PostgreSQL Mailing List Subject: FW: Table Valued Parameters Date: Fri, 23 Oct 2009 18:22:20 +0000 Importance: Normal In-Reply-To: <162867790910231110y3e86a5a4o1584e1f4c97b0625@mail.gmail.com> References: MIME-Version: 1.0 X-OriginalArrivalTime: 23 Oct 2009 18:22:21.0103 (UTC) FILETIME=[C2AEE3F0:01CA540D] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.598 tagged_above=-10 required=5 tests=BAYES_00=-2.599, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200910/121 X-Sequence-Number: 33320 --_966b6d69-92d5-45f9-b705-c8b1446ab623_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi Pavel=2C many thanks for the very prompt reply. I was under the impression that a refcursor was a pointer to a dataset alre= ady resident on the database=2C and were used to return a reference to a da= taset resident in the database to a client application. What I am trying to do is build a table valued variable in a client applica= tion then submit it to a stored procedure resident on the database=2C and h= ave that stored procedure manipulate it as though it were a table [be it in= serting=2C updating or deleting based upon its contents]. Is this possible? I apologise for not making my question more clear. Thanks=2C Andrew. > Date: Fri=2C 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 >=20 > Hello >=20 > 2009/10/23 Andrew Hall : > > Hi=2C > > > > I was wondering whether anybody would be able to advise me on how (if i= t is > > possible) to port some functionality from Oracle? > > > > This is just an example - in Oracle=2C I am able to do the following > > >=20 > Use refcursor=2C please. >=20 > http://www.postgresql.org/docs/8.4/static/plpgsql-cursors.html >=20 > Regards > Pavel Stehule >=20 > > -- > > -- Create a data type which replicates the data structure of a single u= ser > > in my application. > > -- I know that this can be done using PostgreSQL. > > -- > > > > CREATE TYPE TY_APP_USER AS OBJECT > > ( > > aur_id INT > > =2C aur_username VARCHAR2(30 CHAR) > > =2C aur_is_account_enabled VARCHAR2(1 CHAR) > > =2C aur_created_date DATE > > =2C 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=2C and treat that instance as a table=2C for example ... > > -- > > > > CREATE OR REPLACE PROCEDURE prAddUsers > > ( > > p_in_users IN tty_app_user > > ) > > IS > > BEGIN > > > > INSERT > > INTO > > users > > ( > > aur_id > > =2C aur_username > > =2C aur_is_account_enabled > > =2C aur_created_by > > =2C aur_created_date > > ) > > SELECT > > aur_id > > =2C aur_username > > =2C aur_is_account_enabled > > =2C aur_created_by > > =2C aur_created_date > > FROM > > TABLE > > ( > > CAST > > ( > > p_in_users AS tty_app_user > > ) > > )=3B > > > > END prUpdateUsers=3B > > > > My motivation for doing this is to reduce network round trips=2C instea= d of > > having 1 call per record to be sent to the db=2C I can have 1 call pass= ing 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!]=2C and I would be grateful if a= nybody > > could help me to arrive at an optimal solution. > > > > Cheers=2C > > > > Andrew. > > > > > > > > > > > > > > ________________________________ > > Download Messenger onto your mobile for free. Learn more. >=20 > --=20 > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql =20 Chat to your friends for free on selected mobiles. Learn more. =20 _________________________________________________________________ Chat to your friends for free on selected mobiles http://clk.atdmt.com/UKM/go/174426567/direct/01/= --_966b6d69-92d5-45f9-b705-c8b1446ab623_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi Pavel=2C

many thanks for the very prompt reply.

I was unde= r the impression that a refcursor was a pointer to a dataset already reside= nt on the database=2C and were used to return a reference to a dataset resi= dent 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=2C and have that stored proced= ure manipulate it as though it were a table [be it inserting=2C updating or= deleting based upon its contents].

Is this possible?

I apolo= gise for not making my question more clear.

Thanks=2C

Andrew.=

>=3B Date: Fri=2C 23 Oct 2009 20:10:48 +0200
>=3B Subject: R= e: [SQL] Table Valued Parameters
>=3B From: pavel.stehule@gmail.com>=3B To: andrewah@hotmail.com
>=3B CC: pgsql-sql@postgresql.org
= >=3B
>=3B Hello
>=3B
>=3B 2009/10/23 Andrew Hall <=3Ba= ndrewah@hotmail.com>=3B:
>=3B >=3B Hi=2C
>=3B >=3B
>= =3B >=3B I was wondering whether anybody would be able to advise me on ho= w (if it is
>=3B >=3B possible) to port some functionality from Orac= le?
>=3B >=3B
>=3B >=3B This is just an example - in Oracle= =2C I am able to do the following
>=3B >=3B
>=3B
>=3B Use= refcursor=2C please.
>=3B
>=3B http://www.postgresql.org/docs/8= .4/static/plpgsql-cursors.html
>=3B
>=3B Regards
>=3B Pavel= Stehule
>=3B
>=3B >=3B --
>=3B >=3B -- Create a data t= ype which replicates the data structure of a single user
>=3B >=3B i= n my application.
>=3B >=3B -- I know that this can be done using Po= stgreSQL.
>=3B >=3B --
>=3B >=3B
>=3B >=3B CREATE TYPE= TY_APP_USER AS OBJECT
>=3B >=3B (
>=3B >=3B  =3B aur_id&= nbsp=3B =3B =3B =3B =3B =3B =3B =3B =3B&nbs= p=3B =3B =3B =3B =3B =3B =3B INT
>=3B >=3B = =2C aur_username =3B =3B =3B =3B =3B =3B =3B&nb= sp=3B =3B =3B VARCHAR2(30 =3B CHAR)
>=3B >=3B =2C aur_is= _account_enabled VARCHAR2(1 =3B =3B CHAR)
>=3B >=3B =2C aur_= created_date =3B =3B =3B =3B =3B =3B DATE
>=3B= >=3B =2C aur_updated_date =3B =3B =3B =3B =3B = =3B TIMESTAMP
>=3B >=3B )
>=3B >=3B /
>=3B >=3B
>= =3B >=3B --
>=3B >=3B -- Create a data type which can store many i= nstances of a single
>=3B >=3B 'TY_APP_USER'
>=3B >=3B -- [es= sentially this is a table valued data type]. An instance of this data
&g= t=3B >=3B type can be
>=3B >=3B -- created and populated by the cl= ient application [a java based one in my
>=3B >=3B case].
>=3B = >=3B --
>=3B >=3B -- I can't find any reference to something
&g= t=3B >=3B -- similar to this using postgreSQL.
>=3B >=3B --
>= =3B >=3B
>=3B >=3B CREATE TYPE TTY_APP_USER AS TABLE OF TY_APP_USE= R
>=3B >=3B /
>=3B >=3B
>=3B >=3B --
>=3B >=3B = -- Next define a stored procedure which can accept an instance of a
>= =3B >=3B TTY_APP_USER data
>=3B >=3B -- type=2C and treat that ins= tance as a table=2C for example ...
>=3B >=3B --
>=3B >=3B>=3B >=3B CREATE OR REPLACE PROCEDURE prAddUsers
>=3B >=3B (>=3B >=3B  =3B p_in_users IN tty_app_user
>=3B >=3B )
&g= t=3B >=3B IS
>=3B >=3B BEGIN
>=3B >=3B
>=3B >=3B &nb= sp=3B INSERT
>=3B >=3B  =3B =3B =3B INTO
>=3B >= =3B  =3B =3B =3B =3B =3B =3B =3B =3B users<= br>>=3B >=3B  =3B =3B =3B =3B =3B =3B (
>= =3B >=3B  =3B =3B =3B =3B =3B =3B =3B =3B= aur_id
>=3B >=3B  =3B =3B =3B =3B =3B =3B = =2C aur_username
>=3B >=3B  =3B =3B =3B =3B =3B&= nbsp=3B =2C aur_is_account_enabled
>=3B >=3B  =3B =3B = =3B =3B =3B =3B =2C aur_created_by
>=3B >=3B  =3B&nb= sp=3B =3B =3B =3B =3B =2C aur_created_date
>=3B >=3B=  =3B =3B =3B =3B =3B =3B )
>=3B >=3B  = =3B SELECT
>=3B >=3B  =3B =3B =3B =3B =3B = =3B =3B =3B aur_id
>=3B >=3B  =3B =3B =3B = =3B =3B =3B =2C aur_username
>=3B >=3B  =3B =3B = =3B =3B =3B =3B =2C aur_is_account_enabled
>=3B >=3B &nb= sp=3B =3B =3B =3B =3B =3B =2C aur_created_by
>=3B = >=3B  =3B =3B =3B =3B =3B =3B =2C aur_created_dat= e
>=3B >=3B  =3B =3B =3B FROM
>=3B >=3B  =3B&= nbsp=3B =3B =3B =3B =3B =3B =3B TABLE
>=3B >= =3B  =3B =3B =3B =3B =3B =3B =3B =3B (
&= gt=3B >=3B  =3B =3B =3B =3B =3B =3B =3B = =3B =3B =3B CAST
>=3B >=3B  =3B =3B =3B =3B&= nbsp=3B =3B =3B =3B =3B =3B (
>=3B >=3B  =3B=  =3B =3B =3B =3B =3B =3B =3B =3B =3B&nb= sp=3B =3B p_in_users AS tty_app_user
>=3B >=3B  =3B =3B&= nbsp=3B =3B =3B =3B =3B =3B =3B =3B )
>=3B= >=3B  =3B =3B =3B =3B =3B =3B =3B =3B )= =3B
>=3B >=3B
>=3B >=3B END prUpdateUsers=3B
>=3B >=3B=
>=3B >=3B My motivation for doing this is to reduce network round t= rips=2C instead of
>=3B >=3B having 1 call per record to be sent to = the db=2C I can have 1 call passing all
>=3B >=3B values which I wis= h to store in the database.
>=3B >=3B
>=3B >=3B Sending multi= ple records to the database as a result of a single form
>=3B >=3B s= ubmission is a requirement that arises frequently [the example is just
&= gt=3B >=3B intended to demonstrate the principle!]=2C and I would be grat= eful if anybody
>=3B >=3B could help me to arrive at an optimal solu= tion.
>=3B >=3B
>=3B >=3B Cheers=2C
>=3B >=3B
>= =3B >=3B Andrew.
>=3B >=3B
>=3B >=3B
>=3B >=3B
&g= t=3B >=3B
>=3B >=3B
>=3B >=3B
>=3B >=3B ____________= ____________________
>=3B >=3B Download Messenger onto your mobile f= or free. Learn more.
>=3B
>=3B --
>=3B Sent via pgsql-sql = mailing list (pgsql-sql@postgresql.org)
>=3B To make changes to your s= ubscription:
>=3B 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. Lear= n more. = --_966b6d69-92d5-45f9-b705-c8b1446ab623_--