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: [email protected] >> To: [email protected] >> CC: [email protected] >> >> 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 ([email protected]) >> 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 ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Table Valued Parameters
>> > '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 ([email protected]) >> >> 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 ([email protected]) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-sql > > > Download Messenger onto your mobile for free. 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 ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Function Syntax Help
2009/10/30 Plugge, Joe R. > I am trying to create a function that will grind through a cdr table and > populate another table. I am trying to load the function and am getting the > following error: > > > > ERROR: function result type must be specified > > > > > > CREATE FUNCTION gen_simultaneous_calls(mystart timestamp, mystop timestamp) > AS $$ DECLARE > > you need to tell it the return type. If there is none, "returns void" e.g. CREATE FUNCTION gen_simultaneous_calls(mystart timestamp, mystop timestamp) returns void AS $$ > mycount integer; > > BEGIN > > WHILE mystart < mystop + INTERVAL '1 day' LOOP > > SELECT INTO mycount count(*) FROM log_calls WHERE startdate < mystart > and enddate > mystop; > > INSERT INTO simultaneous_calls_rpt (startdate,call_count) VALUES > (mystart,mycount); > > mystart := mystart + INTERVAL '1 minute'; > > mystop := mystop + INTERVAL '1 minute'; > > END LOOP; > > END; > > $$ LANGUAGE 'plpgsql' STABLE; > > > > > > > > > > > > [image: image001]*Joe R. Plugge* > > *Database Administrator, West Interactive Corporation* > > *11650 Miracle Hills Drive, Omaha NE 68154* > > *402-716-0349 | Cell 402-517-2710 | [email protected]** * > > > > *This electronic message transmission, including any attachments, contains > information from West Corporation which may be confidential or privileged. > The information is intended to be for the use of the individual or entity > named above. If you are not the intended recipient, be aware that any > disclosure, copying, distribution or use of the contents of this information > is prohibited. * > > * * > > *If you have received this electronic transmission in error, please notify > the sender immediately by a "reply to sender only" message and destroy all > electronic and hard copies of the communication, including attachments.* > -- 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/
Re: [SQL] Function Syntax Help
2009/10/30 Plugge, Joe R. > Thanks Brian, I changed it to this: > > > > CREATE FUNCTION gen_simultaneous_calls(mystart timestamp, mystop timestamp) > RETURNS VOID AS $$ > > DECLARE > > mycount integer; > > BEGIN > > WHILE mystart < mystop + INTERVAL '1 day' LOOP > > SELECT INTO mycount count(*) FROM log_calls WHERE startdate < mystart > and enddate > mystop; > > INSERT INTO simultaneous_calls_rpt (startdate,call_count) VALUES > (mystart,mycount); > > mystart := mystart + INTERVAL '1 minute'; > > mystop := mystop + INTERVAL '1 minute'; > > END LOOP; > > END; > > $$ LANGUAGE 'plpgsql' STABLE; > > > > But now am getting a different error: > > > > [postg...@linux1559 ~]$ cat gen_simultaneous_calls.sql | psql holly > > ERROR: "$1" is declared CONSTANT > > CONTEXT: compilation of PL/pgSQL function "gen_simultaneous_calls" near > line 7 > > mystart and mystop are constants... you could declare variables and copy those into them, and the modify the new variables... > > > > > > > *From:* [email protected] [mailto:[email protected]] *On Behalf > Of *Brian Modra > *Sent:* Friday, October 30, 2009 2:29 PM > *To:* Plugge, Joe R. > *Cc:* [email protected] > *Subject:* Re: [SQL] Function Syntax Help > > > > 2009/10/30 Plugge, Joe R. > > I am trying to create a function that will grind through a cdr table and > populate another table. I am trying to load the function and am getting the > following error: > > > > ERROR: function result type must be specified > > > > > > CREATE FUNCTION gen_simultaneous_calls(mystart timestamp, mystop timestamp) > AS $$ DECLARE > > > you need to tell it the return type. If there is none, "returns void" > > e.g. > CREATE FUNCTION gen_simultaneous_calls(mystart timestamp, mystop > timestamp) returns void AS $$ > > mycount integer; > > BEGIN > > WHILE mystart < mystop + INTERVAL '1 day' LOOP > > SELECT INTO mycount count(*) FROM log_calls WHERE startdate < mystart > and enddate > mystop; > > INSERT INTO simultaneous_calls_rpt (startdate,call_count) VALUES > (mystart,mycount); > > mystart := mystart + INTERVAL '1 minute'; > > mystop := mystop + INTERVAL '1 minute'; > > END LOOP; > > END; > > $$ LANGUAGE 'plpgsql' STABLE; > > > > > > > > > > > > [image: image001]*Joe R. Plugge* > > *Database Administrator, West Interactive Corporation* > > *11650 Miracle Hills Drive, Omaha NE 68154* > > *402-716-0349 | Cell 402-517-2710 | [email protected]** * > > > > *This electronic message transmission, including any attachments, contains > information from West Corporation which may be confidential or privileged. > The information is intended to be for the use of the individual or entity > named above. If you are not the intended recipient, be aware that any > disclosure, copying, distribution or use of the contents of this information > is prohibited. * > > * * > > *If you have received this electronic transmission in error, please notify > the sender immediately by a "reply to sender only" message and destroy all > electronic and hard copies of the communication, including attachments.* > > > > > -- > 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/ > -- 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/
Re: [SQL] How to get CURRENT_DATE in a pl/pgSQL function
On 19/05/2010, Tim Landscheidt wrote: > Kenneth Marshall wrote: > >> It works using 'now' and I assume that since curtime is >> of type DATE that the assignment casts the return automatically >> to type DATE. Thank you for the ideas. >> [...] > > What's wrong with Pavel's correct and to-the-point answer? No need actually to cast... just use current_date without the quotes. Its not a string. > > Tim > > > -- > Sent via pgsql-sql mailing list ([email protected]) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- 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 ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
