From: Pavel Stehule <pavel.stehule@gmail.com>
To: Andrew Hall <andrewah@hotmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Table Valued Parameters
Date: Fri, 23 Oct 2009 20:10:48 +0200
Message-ID: <162867790910231110y3e86a5a4o1584e1f4c97b0625@mail.gmail.com> (raw)
In-Reply-To: <COL122-W14509AEC755F5710A3CA19CDBD0@phx.gbl>
References: <COL122-W14509AEC755F5710A3CA19CDBD0@phx.gbl>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: pavel.stehule@gmail.com, andrewah@hotmail.com
Subject: Re: Table Valued Parameters
In-Reply-To: <162867790910231110y3e86a5a4o1584e1f4c97b0625@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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