Hello 2009/10/23 Andrew Hall <andre...@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