pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedTable Valued Parameters
8+ messages / 5 participants
[nested] [flat]
* Table Valued Parameters
@ 2009-10-23 18:03 Andrew Hall <andrewah@hotmail.com>
2009-10-23 18:10 ` Re: Table Valued Parameters Pavel Stehule <pavel.stehule@gmail.com>
2009-10-23 18:22 ` FW: Table Valued Parameters Andrew Hall <andrewah@hotmail.com>
2009-10-24 12:01 ` Re: Table Valued Parameters Richard Huxton <dev@archonet.com>
0 siblings, 3 replies; 8+ messages in thread
From: Andrew Hall @ 2009-10-23 18:03 UTC (permalink / raw)
To: pgsql-sql
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
http://clk.atdmt.com/UKM/go/174426567/direct/01/=
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Table Valued Parameters
2009-10-23 18:03 Table Valued Parameters Andrew Hall <andrewah@hotmail.com>
@ 2009-10-23 18:10 ` Pavel Stehule <pavel.stehule@gmail.com>
2009-10-24 11:26 ` Re: Table Valued Parameters Andrew Hall <andrewah@hotmail.com>
2 siblings, 1 reply; 8+ messages in thread
From: Pavel Stehule @ 2009-10-23 18:10 UTC (permalink / raw)
To: Andrew Hall <andrewah@hotmail.com>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Table Valued Parameters
2009-10-23 18:03 Table Valued Parameters Andrew Hall <andrewah@hotmail.com>
2009-10-23 18:10 ` Re: Table Valued Parameters Pavel Stehule <pavel.stehule@gmail.com>
@ 2009-10-24 11:26 ` Andrew Hall <andrewah@hotmail.com>
2009-10-24 12:12 ` Re: Table Valued Parameters Brian Modra <brian@zwartberg.com>
0 siblings, 1 reply; 8+ messages in thread
From: Andrew Hall @ 2009-10-24 11:26 UTC (permalink / raw)
To: brian@zwartberg.com; +Cc: pgsql-sql
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@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
http://clk.atdmt.com/UKM/go/174426567/direct/01/=
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Table Valued Parameters
2009-10-23 18:03 Table Valued Parameters Andrew Hall <andrewah@hotmail.com>
2009-10-23 18:10 ` Re: Table Valued Parameters Pavel Stehule <pavel.stehule@gmail.com>
2009-10-24 11:26 ` Re: Table Valued Parameters Andrew Hall <andrewah@hotmail.com>
@ 2009-10-24 12:12 ` Brian Modra <brian@zwartberg.com>
0 siblings, 0 replies; 8+ messages in thread
From: Brian Modra @ 2009-10-24 12:12 UTC (permalink / raw)
To: Andrew Hall <andrewah@hotmail.com>; +Cc: 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/
^ permalink raw reply [nested|flat] 8+ messages in thread
* FW: Table Valued Parameters
2009-10-23 18:03 Table Valued Parameters Andrew Hall <andrewah@hotmail.com>
@ 2009-10-23 18:22 ` Andrew Hall <andrewah@hotmail.com>
2009-10-23 18:32 ` Re: FW: Table Valued Parameters Brian Modra <brian@zwartberg.com>
2 siblings, 1 reply; 8+ messages in thread
From: Andrew Hall @ 2009-10-23 18:22 UTC (permalink / raw)
To: pgsql-sql
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@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
http://clk.atdmt.com/UKM/go/174426567/direct/01/=
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: FW: Table Valued Parameters
2009-10-23 18:03 Table Valued Parameters Andrew Hall <andrewah@hotmail.com>
2009-10-23 18:22 ` FW: Table Valued Parameters Andrew Hall <andrewah@hotmail.com>
@ 2009-10-23 18:32 ` Brian Modra <brian@zwartberg.com>
0 siblings, 0 replies; 8+ messages in thread
From: Brian Modra @ 2009-10-23 18:32 UTC (permalink / raw)
To: Andrew Hall <andrewah@hotmail.com>; +Cc: 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/
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Table Valued Parameters
2009-10-23 18:03 Table Valued Parameters Andrew Hall <andrewah@hotmail.com>
@ 2009-10-24 12:01 ` Richard Huxton <dev@archonet.com>
2009-10-24 15:46 ` Re: Table Valued Parameters Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 1 reply; 8+ messages in thread
From: Richard Huxton @ 2009-10-24 12:01 UTC (permalink / raw)
To: Andrew Hall <andrewah@hotmail.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Table Valued Parameters
2009-10-23 18:03 Table Valued Parameters Andrew Hall <andrewah@hotmail.com>
2009-10-24 12:01 ` Re: Table Valued Parameters Richard Huxton <dev@archonet.com>
@ 2009-10-24 15:46 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 8+ messages in thread
From: Tom Lane @ 2009-10-24 15:46 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: Andrew Hall <andrewah@hotmail.com>; pgsql-sql
Richard Huxton <dev@archonet.com> 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
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2009-10-24 15:46 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-10-23 18:03 Table Valued Parameters Andrew Hall <andrewah@hotmail.com>
2009-10-23 18:10 ` Pavel Stehule <pavel.stehule@gmail.com>
2009-10-24 11:26 ` Andrew Hall <andrewah@hotmail.com>
2009-10-24 12:12 ` Brian Modra <brian@zwartberg.com>
2009-10-23 18:22 ` Andrew Hall <andrewah@hotmail.com>
2009-10-23 18:32 ` Brian Modra <brian@zwartberg.com>
2009-10-24 12:01 ` Richard Huxton <dev@archonet.com>
2009-10-24 15:46 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox