[SQL] Fw: Transactions ID
Hello. You may prompt me, what get the identifier to transactions? Best regards,Yaroslav Ulyanov[EMAIL PROTECTED]
Re: [SQL] Change column data type
On Tuesday 02 September 2003 10:50, Senthil Kumar S wrote: > Dear Friends, > > Using Postgres 7.3.4 over the linux server 7.3. > > Is it possible to alter/change the data type of a existing table's column, > with out dropping and recreating a column of same name. > > Thanks for ur knowledge sharing. If you mean changing a varchar to a date or int4 to text, then no. If you want to change the size of a varchar field, people seem to have done that in the past (check the archives for details). Otherwise, you'll have to wrap the drop/recreate in a transaction along with whatever supporting updates you need. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Fw: Transactions ID
On Friday 05 September 2003 08:10, Yaroslav Ulyanov wrote: > Hello. > > You may prompt me, what get the identifier to transactions? Not entirely sure what you're after, but does chapter 2.2 of the manuals - "System Columns" help you? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Porting from Oracl to Postgres
Hi, Thanks for your replys. We are facing another problem now. we need to find an alternative for Oracle's ADD_MONTHS in PostGres.. Regards Jomon -Original Message- From: Rod Taylor [mailto:[EMAIL PROTECTED] Sent: Friday, September 05, 2003 1:20 AM To: Jomon Skariah Cc: [EMAIL PROTECTED] Subject: Re: MINUS & ROWNUM in PostGres > 1)Do we have a replacement in PostGres for MINUS operator of Oracle . I believe MINUS is non-standard word for EXCEPT, correct? > 2 Also I need to find an alternative for ROWNUM in oracle.. If you are looking for a unique identifier, try using the OID. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Transactions ID
Hello, Michael. You may prompt me, what get the identifier to transactions? Best regards,Yaroslav Ulyanov[EMAIL PROTECTED]
Re: [SQL] Help me
Very thanks, George! - Original Message - From: George Weaver To: Yaroslav Ulyanov ; [EMAIL PROTECTED] Sent: Friday, September 05, 2003 12:48 AM Subject: Re: [SQL] Help me Hi Yaroslav, You must set the language as: LANGUAGE 'plpgsql'; Regards, George - Original Message - From: Yaroslav Ulyanov To: [EMAIL PROTECTED] Sent: Thursday, September 04, 2003 2:46 AM Subject: [SQL] Help me Hello I cannot write new function with local variables (see image in attachment). That I do wrong? Best regards,Yaroslav Ulyanov[EMAIL PROTECTED] ---(end of broadcast)---TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Porting from Oracl to Postgres
On Fri, 2003-09-05 at 00:24, Jomon Skariah wrote: > > Hi, > > Thanks for your replys. > > We are facing another problem now. > > we need to find an alternative for Oracle's ADD_MONTHS in PostGres.. Guessing based on the name that it adds a quantity of months to a timestamp. How about an SQL Interval? now() + interval '15 months' List of functions: http://www.postgresql.org/docs/7.3/interactive/functions.html signature.asc Description: This is a digitally signed message part
Re: [SQL] Porting from Oracl to Postgres
On Friday 05 September 2003 05:24, Jomon Skariah wrote: > Hi, > > Thanks for your replys. > > We are facing another problem now. > > we need to find an alternative for Oracle's ADD_MONTHS in PostGres.. SELECT now() + '1 hour'::interval; SELECT now() + '2 days'::interval; SELECT now() + '3 months':: interval; See the data-types section of the manual for details. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Porting from Oracl to Postgres
--- Rod Taylor <[EMAIL PROTECTED]> wrote: > On Fri, 2003-09-05 at 00:24, Jomon Skariah wrote: > > > > Hi, > > > > Thanks for your replys. > > > > We are facing another problem now. > > > > we need to find an alternative for Oracle's > ADD_MONTHS in PostGres.. > > Guessing based on the name that it adds a quantity > of months to a > timestamp. How about an SQL Interval? > > now() + interval '15 months' > > List of functions: > http://www.postgresql.org/docs/7.3/interactive/functions.html For compatibility in your application, you could do something like: CREATE FUNCTION add_months(date, integer) RETURNS date AS ' SELECT ($1 + ( $2::text || '' months'')::interval)::date; ' LANGUAGE 'sql'; I second the recommendation to spend some time looking over the available functions in PostgreSQL. __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] recursive sql
can anyone recommend a good reference source for doing recursive sql on postgresql? i want to do something similar to a BOM expansion. (i.e. i need to traverse a self-referencing table that stores a tree structure and answer a question like "Get me A and all of A's descendents") Regards, Floyd Shackelford 4 Peaks Technology Group, Inc. VOICE: 334.735.9428 FAX: 702.995.6462 EMAIL: [EMAIL PROTECTED] ICQ #: 161371538 PGP Key ID: 0x2E84F2F2 PGP Fone at private.fwshackelford.com on request Shackelford Motto: ACTA NON VERBA - Actions, not words Alabama StateMotto: AUDEMUS JURA NOSTRA DEFENDERE - We Dare Defend Our Rights The Philosophy of Liberty: http://www.isil.org/resources/introduction.swf "We have allowed our constitutional republic to deteriorate into a virtually unchecked direct democracy. Today's political process is nothing more than a street fight between various groups seeking to vote themselves other people's money. Individual voters tend to support the candidate that promises them the most federal loot in whatever form, rather than the candidate who will uphold the rule of law." --Rep. Ron Paul ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] Name the Parameters
Dear Gurus, Is it possible to name the IN parameters of the PLpgSQL Functions. Because naming the parameter will ease coding. See the following function for insert, having 10 parameters as a input. = CREATE FUNCTION InsertFn(int8, varchar, varchar, varchar, varchar, varchar, varchar, varchar, varchar, varchar, varchar, timestamp) RETURNS varchar AS 'BEGIN RETURN ''HAi'';-- Check if the address_id specified exist in the address tableIF NOT EXISTS ( SELECT address_id FROM address WHERE address_id = $1 AND rec_deleted_flag = ''N'' )THENRAISE EXCEPTION ''The Address Specified is Invalid'';END IF; -- Check if the timestamp is same for the given address_idIF NOT EXISTS ( SELECT address_id FROM "WATS".address WHERE address_id = $1 AND rec_modified_date = $12; )THENRAISE EXCEPTION ''The record has already been updated by another user.'';END IF; -- Else, update the recordUPDATE "WATS".addressSET address = $2 , city = $3 , state = $4 , country = $5 , zipcode = $6 , email = $7 , home_phone = $8 , work_phone = $9 , cell_phone = $10 , pager = $11WHERE address_id = $1;END;' LANGUAGE 'plpgsql' IMMUTABLE;GRANT EXECUTE ON FUNCTION sp_upd_add_001(int8, varchar, varchar, varchar, varchar, varchar, varchar, varchar, varchar, varchar, varchar, timestamp) TO PUBLIC;GRANT EXECUTE ON FUNCTION "WATS".sp_upd_add_001(int8, varchar, varchar, varchar, varchar, varchar, varchar, varchar, varchar, varchar, varchar, timestamp) TO wats;= See it is difficult to pass the parameters with out name them. (2) I am getting error at the code IF NOT EXISTS... Is that not supported at Postgres? Please shed ur light on this. pls Regards Kumar
Re: [SQL] recursive sql
Good day On Friday 05 September 2003 21:41, you wrote: > can anyone recommend a good reference source for doing recursive sql on > postgresql? i want to do something similar to a BOM expansion. (i.e. i need > to traverse a self-referencing table that stores a tree structure and > answer a question like "Get me A and all of A's descendents") "recursive queries" are much slower than queries to a nested-tree. please find something readable on subject "nested-tree" or ask me to send you this. You'll see that the maintaining of a nested-tree is covered by its good profit. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Name the Parameters
On Saturday 06 September 2003 06:42, Kumar wrote: > Dear Gurus, > > Is it possible to name the IN parameters of the PLpgSQL Functions. Because > naming the parameter will ease coding. See the following function for > insert, having 10 parameters as a input. No, though there is an item on the TODO list IIRC. Using ALIAS FOR might make things easier though, something along the lines of: DECLARE address_id ALIAS FOR $1; address ALIAS FOR $2; ... BEGIN ... END; Ian Barwick [EMAIL PROTECTED] ---(end of broadcast)--- TIP 8: explain analyze is your friend
