Re: Table Valued Parameters - Mailing list 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.