Re: Table Valued Parameters

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(at)zwartberg(dot)com
> To: andrewah(at)hotmail(dot)com
> CC: pgsql-sql(at)postgresql(dot)org
>
> 2009/10/23 Andrew Hall <andrewah(at)hotmail(dot)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(dot)stehule(at)gmail(dot)com
> >> To: andrewah(at)hotmail(dot)com
> >> CC: pgsql-sql(at)postgresql(dot)org
> >>
> >> Hello
> >>
> >> 2009/10/23 Andrew Hall <andrewah(at)hotmail(dot)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(at)postgresql(dot)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(at)postgresql(dot)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/