Re: [SQL] @@Error equivalent in Postgresql
Pavel Stehule wrote: > So the > programming based on returning state codes is very obsolete, and > little bit difficult. You can emulate, but any protected block creates > inner transaction and this should negative effect on speed - and it > are some lines more. I'd like to second and emphasise this. Attempting to use return codes will cause you unnecessary grief. It might be worth it to maintain compatibility in other code between Pg and MS-SQL, but I'm not convinced - there are enough other differences that you'll probably need to maintain fairly different versions of the functions anyway. In many cases (mainly where no flow control is required) your T-SQL functions can become simple SQL functions anyway. I suspect in the long run you'll be better off handling errors by letting exceptions bubble up so that the caller can handle it or let it bubble up in turn. The exception will include an informative error code. You can then get that error code via exception information if you trap the exception in PL/PgSQL or via your application driver interface (JDBC/ODBC/etc) as the SQLSTATE if it bubbles up to a top-level statement. I've found PostgreSQL's exception-based error handling a real life-saver, especially when working with programming languages that also use exceptions. When I'm working in Java, for example, a sanity check deep in some PL/PgSQL function may raise an exception that propagates through to the top-level SQL statement, causing the JDBC driver to throw a Java SQLException that in turn propagates up to code that's in a position to do something about the problem. -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Table Valued Parameters
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/
Re: [SQL] Table Valued Parameters
Hello 2009/10/23 Andrew Hall : > 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
FW: [SQL] Table Valued Parameters
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.steh...@gmail.com > To: andre...@hotmail.com > CC: pgsql-sql@postgresql.org > > Hello > > 2009/10/23 Andrew Hall : > > 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/
Re: FW: [SQL] Table Valued Parameters
2009/10/23 Andrew Hall : > 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.steh...@gmail.com >> To: andre...@hotmail.com >> CC: pgsql-sql@postgresql.org >> >> Hello >> >> 2009/10/23 Andrew Hall : >> > 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
