Re: Table Valued Parameters - Mailing list pgsql-sql

2009/10/24 Andrew Hall <andrewah@hotmail.com>:
> Thanks Bruce,
>
> 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.  (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
> procedure.
>
> Anyhow, Pavel Stehule has kindly explained that while there is no exact
> equivalent in postgreSQL - but has recommended that I investigate the array
> functionality, and the COPY command.

Maybe you could also use a temporary table, (create temporary table
... on commit drop)

> In retrospect, I should of just asked how one would go about submitting
> multiple records of the same type/signature to a plpgsql procedure with a
> single 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.

Its a pleasure.

> Cheers,
>
> Andrew.
>
>> Date: Fri, 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
>>
>> 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/
>>
>> --
>> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-sql
>
> ________________________________
> Download Messenger onto your mobile for free. 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/