Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 63864632A90 for ; Sat, 24 Oct 2009 08:26:23 -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 64865-06 for ; Sat, 24 Oct 2009 11:26:13 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from col0-omc3-s17.col0.hotmail.com (col0-omc3-s17.col0.hotmail.com [65.55.34.156]) by mail.postgresql.org (Postfix) with ESMTP id C758C6322F2 for ; Sat, 24 Oct 2009 08:26:12 -0300 (ADT) Received: from COL122-W6 ([65.55.34.135]) by col0-omc3-s17.col0.hotmail.com with Microsoft SMTPSVC(6.0.3790.3959); Sat, 24 Oct 2009 04:26:09 -0700 Message-ID: Content-Type: multipart/alternative; boundary="_f8de0450-426a-4ae0-9144-08cf72033704_" X-Originating-IP: [86.130.79.162] From: Andrew Hall To: CC: PostgreSQL Mailing List Subject: Re: Table Valued Parameters Date: Sat, 24 Oct 2009 11:26:09 +0000 Importance: Normal In-Reply-To: <5a9699850910231132t58f36453o735a8287c7aed64c@mail.gmail.com> References: <162867790910231110y3e86a5a4o1584e1f4c97b0625@mail.gmail.com> X-OriginalArrivalTime: 24 Oct 2009 11:26:09.0616 (UTC) FILETIME=[C8EE1D00:01CA549C] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.038 tagged_above=-10 required=5 tests=BAYES_00=-2.599, HTML_MESSAGE=0.001, MIME_HEADER_CTYPE_ONLY=0.56 X-Spam-Level: X-Archive-Number: 200910/124 X-Sequence-Number: 33323 <5a9699850910231132t58f36453o735a8287c7aed64c@mail.gmail.com> MIME-Version: 1.0 --_f8de0450-426a-4ae0-9144-08cf72033704_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Thanks Bruce=2C what I was looking for was the postgreSQL equivalent of table-valued parame= ters from SQL Server 2008 (http://technet.microsoft.com/en-us/library/bb510= 489.aspx) or tables of Oracle Objects from Oracle. (Or something that woul= d 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 proce= dure. Anyhow=2C Pavel Stehule has kindly explained that while there is no exact e= quivalent in postgreSQL - but has recommended that I investigate the array = functionality=2C and the COPY command. In retrospect=2C I should of just asked how one would go about submitting m= ultiple records of the same type/signature to a plpgsql procedure with a si= ngle 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=2C Andrew. > Date: Fri=2C 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 >=20 > 2009/10/23 Andrew Hall : > > Hi Pavel=2C > > > > 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=2C 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 databa= se=2C > > and have that stored procedure manipulate it as though it were a table = [be > > it inserting=2C updating or deleting based upon its contents]. > > > > Is this possible? > > > > I apologise for not making my question more clear. >=20 > Is the following too simplistic (maybe I have not understood your > question either=2C but it seems that postgresql makes it so simple=2C tha= t > "problems" you had to solve in ORACLKE=2C aren't a "problem" in > postgresql.) >=20 > create type ty_app_user as ( > aur_id integer=2C > ... etc > )=3B >=20 > 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=2C etc...)= =3B > end=3B > $$ language plpgsql=3B >=20 >=20 > > 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 > >> > >> Hello > >> > >> 2009/10/23 Andrew Hall : > >> > Hi=2C > >> > > >> > I was wondering whether anybody would be able to advise me on how (i= f it > >> > is > >> > possible) to port some functionality from Oracle? > >> > > >> > This is just an example - in Oracle=2C I am able to do the following > >> > > >> > >> Use refcursor=2C 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 singl= e > >> > user > >> > 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 th= is > >> > 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 ins= tead > >> > of > >> > having 1 call per record to be sent to the db=2C I can have 1 call p= assing > >> > all > >> > values which I wish to store in the database. > >> > > >> > Sending multiple records to the database as a result of a single for= m > >> > submission is a requirement that arises frequently [the example is j= ust > >> > intended to demonstrate the principle!]=2C and I would be grateful i= f > >> > anybody > >> > could help me to arrive at an optimal solution. > >> > > >> > Cheers=2C > >> > > >> > 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. >=20 >=20 >=20 > --=20 > Brian Modra Land line: +27 23 5411 462 > Mobile: +27 79 69 77 082 > 5 Jan Louw Str=2C Prince Albert=2C 6930 > Postal: P.O. Box 2=2C Prince Albert 6930 > South Africa > http://www.zwartberg.com/ >=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 _________________________________________________________________ Download Messenger onto your mobile for free http://clk.atdmt.com/UKM/go/174426567/direct/01/= --_f8de0450-426a-4ae0-9144-08cf72033704_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Thanks Bruce=2C

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.&n= bsp=3B (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 pro= cedure.

Anyhow=2C Pavel Stehule has kindly explained that while ther= e is no exact equivalent in postgreSQL - but has recommended that I investi= gate the array functionality=2C and the COPY command.

In retrospect= =2C I should of just asked how one would go about submitting multiple recor= ds of the same type/signature to a plpgsql procedure with a single invocati= on (of that plpgsql procedure) from a client application.

All the sa= me - I would like to express my thanks to you for taking the time to sugges= t an approach.

Cheers=2C

Andrew.

>=3B Date: Fri=2C 2= 3 Oct 2009 20:32:37 +0200
>=3B Subject: Re: FW: [SQL] Table Valued Par= ameters
>=3B From: brian@zwartberg.com
>=3B To: andrewah@hotmail.= com
>=3B CC: pgsql-sql@postgresql.org
>=3B
>=3B 2009/10/23 = Andrew Hall <=3Bandrewah@hotmail.com>=3B:
>=3B >=3B Hi Pavel=2C<= br>>=3B >=3B
>=3B >=3B many thanks for the very prompt reply.>=3B >=3B
>=3B >=3B I was under the impression that a refcursor= was a pointer to a dataset
>=3B >=3B already resident on the databa= se=2C and were used to return a reference to a
>=3B >=3B dataset res= ident in the database to a client application.
>=3B >=3B
>=3B &= gt=3B What I am trying to do is build a table valued variable in a client>=3B >=3B application then submit it to a stored procedure resident o= n the database=2C
>=3B >=3B and have that stored procedure manipulat= e it as though it were a table [be
>=3B >=3B it inserting=2C updatin= g or deleting based upon its contents].
>=3B >=3B
>=3B >=3B I= s this possible?
>=3B >=3B
>=3B >=3B I apologise for not maki= ng my question more clear.
>=3B
>=3B Is the following too simpli= stic (maybe I have not understood your
>=3B question either=2C but it = seems that postgresql makes it so simple=2C that
>=3B "problems" you h= ad to solve in ORACLKE=2C aren't a "problem" in
>=3B postgresql.)
&= gt=3B
>=3B create type ty_app_user as (
>=3B aur_id integer=2C>=3B ... etc
>=3B )=3B
>=3B
>=3B create or replace func= tion prAddUsers ( p_in_users tty_app_user )
>=3B returns void as $$>=3B declare
>=3B begin
>=3B insert into users (aur_id ... etc= ) values (p_in_users.aur_id=2C etc...)=3B
>=3B end=3B
>=3B $$ la= nguage plpgsql=3B
>=3B
>=3B
>=3B >=3B Thanks=2C
>= =3B >=3B
>=3B >=3B Andrew.
>=3B >=3B
>=3B >=3B>=3B= Date: Fri=2C 23 Oct 2009 20:10:48 +0200
>=3B >=3B>=3B Subject: Re= : [SQL] Table Valued Parameters
>=3B >=3B>=3B From: pavel.stehule@= gmail.com
>=3B >=3B>=3B To: andrewah@hotmail.com
>=3B >=3B&= gt=3B CC: pgsql-sql@postgresql.org
>=3B >=3B>=3B
>=3B >=3B&= gt=3B Hello
>=3B >=3B>=3B
>=3B >=3B>=3B 2009/10/23 Andrew= Hall <=3Bandrewah@hotmail.com>=3B:
>=3B >=3B>=3B >=3B Hi=2C=
>=3B >=3B>=3B >=3B
>=3B >=3B>=3B >=3B I was wonderin= g whether anybody would be able to advise me on how (if it
>=3B >=3B= >=3B >=3B is
>=3B >=3B>=3B >=3B possible) to port some funct= ionality from Oracle?
>=3B >=3B>=3B >=3B
>=3B >=3B>=3B = >=3B This is just an example - in Oracle=2C I am able to do the following=
>=3B >=3B>=3B >=3B
>=3B >=3B>=3B
>=3B >=3B>= =3B Use refcursor=2C please.
>=3B >=3B>=3B
>=3B >=3B>=3B = http://www.postgresql.org/docs/8.4/static/plpgsql-cursors.html
>=3B &g= t=3B>=3B
>=3B >=3B>=3B Regards
>=3B >=3B>=3B Pavel Steh= ule
>=3B >=3B>=3B
>=3B >=3B>=3B >=3B --
>=3B >= =3B>=3B >=3B -- Create a data type which replicates the data structure = of a single
>=3B >=3B>=3B >=3B user
>=3B >=3B>=3B >= =3B in my application.
>=3B >=3B>=3B >=3B -- I know that this ca= n be done using PostgreSQL.
>=3B >=3B>=3B >=3B --
>=3B >= =3B>=3B >=3B
>=3B >=3B>=3B >=3B CREATE TYPE TY_APP_USER AS O= BJECT
>=3B >=3B>=3B >=3B (
>=3B >=3B>=3B >=3B  = =3B aur_id =3B =3B =3B =3B =3B =3B =3B =3B&= nbsp=3B =3B =3B =3B =3B =3B =3B =3B INT
>= =3B >=3B>=3B >=3B =2C aur_username =3B =3B =3B =3B&nb= sp=3B =3B =3B =3B =3B =3B VARCHAR2(30 =3B CHAR)
= >=3B >=3B>=3B >=3B =2C aur_is_account_enabled VARCHAR2(1 =3B&nb= sp=3B CHAR)
>=3B >=3B>=3B >=3B =2C aur_created_date =3B = =3B =3B =3B =3B =3B DATE
>=3B >=3B>=3B >=3B =2C = aur_updated_date =3B =3B =3B =3B =3B =3B TIMESTAMP<= br>>=3B >=3B>=3B >=3B )
>=3B >=3B>=3B >=3B /
>=3B &= gt=3B>=3B >=3B
>=3B >=3B>=3B >=3B --
>=3B >=3B>=3B = >=3B -- Create a data type which can store many instances of a single
= >=3B >=3B>=3B >=3B 'TY_APP_USER'
>=3B >=3B>=3B >=3B -- [= essentially this is a table valued data type]. An instance of this
>= =3B >=3B>=3B >=3B data
>=3B >=3B>=3B >=3B type can be
&= gt=3B >=3B>=3B >=3B -- created and populated by the client applicatio= n [a java based one in
>=3B >=3B>=3B >=3B my
>=3B >=3B>= =3B >=3B case].
>=3B >=3B>=3B >=3B --
>=3B >=3B>=3B &= gt=3B -- I can't find any reference to something
>=3B >=3B>=3B >= =3B -- similar to this using postgreSQL.
>=3B >=3B>=3B >=3B -->=3B >=3B>=3B >=3B
>=3B >=3B>=3B >=3B CREATE TYPE TTY_= APP_USER AS TABLE OF TY_APP_USER
>=3B >=3B>=3B >=3B /
>=3B = >=3B>=3B >=3B
>=3B >=3B>=3B >=3B --
>=3B >=3B>=3B= >=3B -- Next define a stored procedure which can accept an instance of a=
>=3B >=3B>=3B >=3B TTY_APP_USER data
>=3B >=3B>=3B >= =3B -- type=2C and treat that instance as a table=2C for example ...
>= =3B >=3B>=3B >=3B --
>=3B >=3B>=3B >=3B
>=3B >=3B&g= t=3B >=3B CREATE OR REPLACE PROCEDURE prAddUsers
>=3B >=3B>=3B &= gt=3B (
>=3B >=3B>=3B >=3B  =3B p_in_users IN tty_app_user>=3B >=3B>=3B >=3B )
>=3B >=3B>=3B >=3B IS
>=3B &= gt=3B>=3B >=3B BEGIN
>=3B >=3B>=3B >=3B
>=3B >=3B>= =3B >=3B  =3B INSERT
>=3B >=3B>=3B >=3B  =3B =3B&n= bsp=3B INTO
>=3B >=3B>=3B >=3B  =3B =3B =3B =3B&= nbsp=3B =3B =3B =3B users
>=3B >=3B>=3B >=3B  = =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 =3B = =3B =2C aur_username
>=3B >=3B>=3B >=3B  =3B =3B =3B=  =3B =3B =3B =2C aur_is_account_enabled
>=3B >=3B>=3B = >=3B  =3B =3B =3B =3B =3B =3B =2C aur_created_by<= br>>=3B >=3B>=3B >=3B  =3B =3B =3B =3B =3B = =3B =2C aur_created_date
>=3B >=3B>=3B >=3B  =3B =3B&nbs= p=3B =3B =3B =3B )
>=3B >=3B>=3B >=3B  =3B SELEC= T
>=3B >=3B>=3B >=3B  =3B =3B =3B =3B =3B&nb= sp=3B =3B =3B aur_id
>=3B >=3B>=3B >=3B  =3B =3B=  =3B =3B =3B =3B =2C aur_username
>=3B >=3B>=3B &g= t=3B  =3B =3B =3B =3B =3B =3B =2C aur_is_account_en= abled
>=3B >=3B>=3B >=3B  =3B =3B =3B =3B = =3B =3B =2C aur_created_by
>=3B >=3B>=3B >=3B  =3B = =3B =3B =3B =3B =3B =2C aur_created_date
>=3B >=3B&g= t=3B >=3B  =3B =3B =3B FROM
>=3B >=3B>=3B >=3B &nb= sp=3B =3B =3B =3B =3B =3B =3B =3B TABLE
>= =3B >=3B>=3B >=3B  =3B =3B =3B =3B =3B =3B&nb= sp=3B =3B (
>=3B >=3B>=3B >=3B  =3B =3B =3B = =3B =3B =3B =3B =3B =3B =3B CAST
>=3B >=3B&g= t=3B >=3B  =3B =3B =3B =3B =3B =3B =3B = =3B =3B =3B (
>=3B >=3B>=3B >=3B  =3B =3B = =3B =3B =3B =3B =3B =3B =3B =3B =3B =3B= p_in_users AS tty_app_user
>=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 = =3B =3B )=3B
>=3B >=3B>=3B >=3B
>=3B >=3B>=3B >= =3B END prUpdateUsers=3B
>=3B >=3B>=3B >=3B
>=3B >=3B>= =3B >=3B My motivation for doing this is to reduce network round trips=2C= instead
>=3B >=3B>=3B >=3B of
>=3B >=3B>=3B >=3B hav= ing 1 call per record to be sent to the db=2C I can have 1 call passing
= >=3B >=3B>=3B >=3B all
>=3B >=3B>=3B >=3B values which I= wish to store in the database.
>=3B >=3B>=3B >=3B
>=3B >= =3B>=3B >=3B Sending multiple records to the database as a result of a = single form
>=3B >=3B>=3B >=3B submission is a requirement that = arises frequently [the example is just
>=3B >=3B>=3B >=3B intend= ed to demonstrate the principle!]=2C and I would be grateful if
>=3B &= gt=3B>=3B >=3B anybody
>=3B >=3B>=3B >=3B could help me to a= rrive at an optimal solution.
>=3B >=3B>=3B >=3B
>=3B >= =3B>=3B >=3B Cheers=2C
>=3B >=3B>=3B >=3B
>=3B >=3B&g= t=3B >=3B Andrew.
>=3B >=3B>=3B >=3B
>=3B >=3B>=3B &g= t=3B
>=3B >=3B>=3B >=3B
>=3B >=3B>=3B >=3B
>=3B = >=3B>=3B >=3B
>=3B >=3B>=3B >=3B
>=3B >=3B>=3B &g= t=3B ________________________________
>=3B >=3B>=3B >=3B Downloa= d Messenger onto your mobile for free. Learn more.
>=3B >=3B>=3B>=3B >=3B>=3B --
>=3B >=3B>=3B Sent via pgsql-sql mailing = list (pgsql-sql@postgresql.org)
>=3B >=3B>=3B To make changes to y= our subscription:
>=3B >=3B>=3B http://www.postgresql.org/mailpref= /pgsql-sql
>=3B >=3B
>=3B >=3B ______________________________= __
>=3B >=3B Chat to your friends for free on selected mobiles. Lear= n more.
>=3B >=3B ________________________________
>=3B >=3B = Chat to your friends for free on selected mobiles. Learn more.
>=3B >=3B
>=3B
>=3B --
>=3B Brian Modra Land line: +27 2= 3 5411 462
>=3B Mobile: +27 79 69 77 082
>=3B 5 Jan Louw Str=2C P= rince Albert=2C 6930
>=3B Postal: P.O. Box 2=2C Prince Albert 6930
= >=3B South Africa
>=3B http://www.zwartberg.com/
>=3B
>= =3B --
>=3B Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org= )
>=3B To make changes to your subscription:
>=3B http://www.post= gresql.org/mailpref/pgsql-sql

Download Messenger= onto your mobile for free. Learn more. = --_f8de0450-426a-4ae0-9144-08cf72033704_--