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.
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
http://clk.atdmt.com/UKM/go/174426567/direct/01/
