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=DsQunmYpPb0CVD5dTHSuPDsLvFTLEG4CciwRHypTKTdoaUynUvPl1+d8RIeis6zS0a
	5GRWhy+/0tKgdT7Gjgn023JhKDDXI0VIKwgwkAzWioHLh/JYoKPfNmsJrXO0ygiGeiCw
	Zw8lj7Wf7eVLYpYcWKiQNQeNU331puIqnVGsM=
MIME-Version: 1.0
In-Reply-To: <COL122-W4133E0C04C09033EF47D7CCDBD0@phx.gbl>
References: <COL122-W14509AEC755F5710A3CA19CDBD0@phx.gbl>
	<162867790910231110y3e86a5a4o1584e1f4c97b0625@mail.gmail.com>
	<COL122-W4133E0C04C09033EF47D7CCDBD0@phx.gbl>
Date: Fri, 23 Oct 2009 20:32:37 +0200
Message-ID: <5a9699850910231132t58f36453o735a8287c7aed64c@mail.gmail.com>
Subject: Re: FW: Table Valued Parameters
From: Brian Modra <brian@zwartberg.com>
To: Andrew Hall <andrewah@hotmail.com>
Cc: PostgreSQL Mailing List <pgsql-sql@postgresql.org>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

2009/10/23 Andrew Hall <andrewah@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 [b=
e
> 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 <andrewah@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
>> > (
>> > =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 one i=
n
>> > 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 passi=
ng
>> > 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 jus=
t
>> > 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.


--=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/