Re: FW: Table Valued Parameters - Mailing list pgsql-sql

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 [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 <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
>> > (
>> >   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. 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/