Table Valued Parameters

[Andrew Hall-6]

Some parts of this message have been removed. Learn more about Nabble's security policy.

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

--
-- 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.

[Pavel Stehule]

Hello

2009/10/23 Andrew Hall <andrewah@...>:
> 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@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[Andrew Hall-6]

Some parts of this message have been removed. Learn more about Nabble's security policy.

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.stehule@...
> To: andrewah@...
> CC: pgsql-sql@...
>
> Hello
>
> 2009/10/23 Andrew Hall <andrewah@...>:
> > 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@...)
> 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-2]

2009/10/23 Andrew Hall <andrewah@...>:

> 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@...
>> To: andrewah@...
>> CC: pgsql-sql@...
>>
>> Hello
>>
>> 2009/10/23 Andrew Hall <andrewah@...>:
>> > 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@...)
>> 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: [phone]
Mobile: [phone]
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@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[Andrew Hall-6]

Some parts of this message have been removed. Learn more about Nabble's security policy.

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.

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.

Cheers,

Andrew.

> Date: Fri, 23 Oct 2009 20:32:37 +0200
> Subject: Re: FW: [SQL] Table Valued Parameters
> From: brian@...
> To: andrewah@...
> CC: pgsql-sql@...
>
> 2009/10/23 Andrew Hall <andrewah@...>:
> > 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@...
> >> To: andrewah@...
> >> CC: pgsql-sql@...
> >>
> >> Hello
> >>
> >> 2009/10/23 Andrew Hall <andrewah@...>:
> >> > 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@...)
> >> 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: [phone]
> Mobile: [phone]
> 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@...)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql

---

Download Messenger onto your mobile for free. Learn more.

[Richard Huxton]

Andrew Hall wrote:
> 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
>
> --
> -- 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 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.

The following may not do anything interesting, but it does show arrays
of composite types, which is what you are after.

To prevent quoting insanity, I recommend the ARRAY[] constructor rather
than array literals. You do need the explicit typecasts.

Oh - and version 8.3 or higher for arrays of compound types.


BEGIN;

CREATE TYPE typ1 AS (i integer, t text);

CREATE FUNCTION print_array(a typ1[]) RETURNS void AS $$
DECLARE
    m int;
    n int;
    i int;
    e typ1;
BEGIN
    m := array_lower(a, 1);
    n := array_upper(a, 1);
    FOR i IN m .. n LOOP
        e := a[i];
        RAISE NOTICE '% - %', e.i, e.t;
    END LOOP;
    RETURN;
END;
$$ LANGUAGE plpgsql;

SELECT print_array(ARRAY[ '(1,"abc")'::typ1, '(2,"def")'::typ1 ]);
SELECT print_array(ARRAY[ '(1,"abc")', '(2,"def")' ]::typ1[]);

ROLLBACK;

--
  Richard Huxton
  Archonet Ltd

--
Sent via pgsql-sql mailing list (pgsql-sql@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[Brian Modra-2]

2009/10/24 Andrew Hall <andrewah@...>:

> 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@...
>> To: andrewah@...
>> CC: pgsql-sql@...
>>
>> 2009/10/23 Andrew Hall <andrewah@...>:
>> > 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@...
>> >> To: andrewah@...
>> >> CC: pgsql-sql@...
>> >>
>> >> Hello
>> >>
>> >> 2009/10/23 Andrew Hall <andrewah@...>:
>> >> > 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@...)
>> >> 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: [phone]
>> Mobile: [phone]
>> 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@...)
>> 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: [phone]
Mobile: [phone]
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@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[Tom Lane-2]

Richard Huxton <dev@...> writes:
> To prevent quoting insanity, I recommend the ARRAY[] constructor rather
> than array literals. You do need the explicit typecasts.

By the same token, you might want to use ROW() rather than
composite-type literal syntax for the array elements.

> Oh - and version 8.3 or higher for arrays of compound types.

I think also that casting the array, rather than the individual rows,
only works as of 8.4; ie in 8.3 you have to follow the first example:

> SELECT print_array(ARRAY[ '(1,"abc")'::typ1, '(2,"def")'::typ1 ]);
> SELECT print_array(ARRAY[ '(1,"abc")', '(2,"def")' ]::typ1[]);

In this case there's not much real difference, but with a lot of
array elements the individual casts get tedious.

                        regards, tom lane

--
Sent via pgsql-sql mailing list (pgsql-sql@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql