[SQL] mail + rfc822, rfc2822 + schema
Hi, i am thinking of doing a remote MUA web-based system, based on postgresql. The idea is to let uucp/sendmail do the job of transporting the smtp mail to the remote site, and then using an mbox parser at the site (i have already done that in java), to offload (remotely) the mail messages to the remote postgresql. (I didnt want an IMAP, POP3 solution since POP3 dont make no sense at all since the web and mail server will be the same machine, and IMAP adds administrative overhead and deviates from our wierdish custom J2EE jboss/postgresql authentication mechanism, plus it gives features that we dont want at all - shared folders, etc..). So since we want the email system to fit seemlessly into the rest of the system, just like yet another app, the obvious was to think of an postgresql based Store. So, i'd like to know if any of you has designed a schema serving as an mail storage. If anything of caution arrose to you, and if you had something to recommend. By default i'll create a schema sufficient enuf to hold as much as the javax.mail.internet.MimeMessage can give, and the attachments. Thanx. -- -Achilleus ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] query configuration for validate empty quote to zero
Did you try to explicitly give the type for the literal? e.g. ''::varchar -Original Message-From: Yudie [mailto:[EMAIL PROTECTED]Sent: Montag, 17. Jänner 2005 19:44To: pgsql-sql@postgresql.orgSubject: [SQL] query configuration for validate empty quote to zero We just changed the postgresql server and got some problem with sql command in our old server we can exexute this query: SELECT * FROM product WHERE prdtnumber = '' field prdtnumber data type is integer and the empty quote somehow can be assumed into 0 (zero) Now, if I use the same query it return an error: invalid input syntax for integer: "" Anyone know what's the problem?? Thanks Yudie
[SQL] Returning a bool on DELETE in a proc.
Hi, I'm currently writing a function which encapsulates a delete and should return a bool as indicator for success. I tried: DELETE FROM "TariffDetails" WHERE "TariffId"=$1 and "BNumberPrefix"=$2; SELECT TRUE; but this makes me not happy. How can I distingruish wehter DELETE affected 0 or more rows and return that while DELETE is not capable of returning any value? And is the whole function executed if the DELETE fails or will it stop bevore the select? I had a read throu SQL-functions while nothing matched me. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Returning a bool on DELETE in a proc.
> Hi, > > I'm currently writing a function which encapsulates a delete and should > return a bool as indicator for success. > > I tried: > > DELETE FROM "TariffDetails" WHERE "TariffId"=$1 and "BNumberPrefix"=$2; > SELECT TRUE; > > but this makes me not happy. > How can I distingruish wehter DELETE affected 0 or more rows and > return that while DELETE is not capable of returning any value? > > And is the whole function executed if the DELETE fails or will it > stop bevore the select? > > I had a read throu SQL-functions while nothing matched me. > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster Robert, I do not know what version you are working with but here is the 7.4 documentation on result status. http://www.postgresql.org/docs/7.4/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS Kind Regards, Keith PS I am a novice myself so don't put too much faith in my post. ;-) __ 99main Internet Services http://www.99main.com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Returning a bool on DELETE in a proc.
On Tue, Jan 18, 2005 at 05:04:58PM +0100, KÖPFERL Robert wrote: > I'm currently writing a function which encapsulates a delete and should > return a bool as indicator for success. How do you define success? When the delete affects one or more rows? > DELETE FROM "TariffDetails" WHERE "TariffId"=$1 and "BNumberPrefix"=$2; > SELECT TRUE; > > but this makes me not happy. > How can I distingruish wehter DELETE affected 0 or more rows and return that > while DELETE is not capable of returning any value? If you're using PL/pgSQL then see "Obtaining the Result Status" in the "Basic Statements" section of the PL/pgSQL documentation. > And is the whole function executed if the DELETE fails or will it stop > bevore the select? A PL/pgSQL function should exit immediately with an error if the DELETE fails. But understand that PL/pgSQL's notion of a failure might not match yours: trying to delete from a non-existent table is a failure, but deleting zero rows from an existing table isn't. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] postgres stored procedures
Any advice on converting my mssql to postgres? Points of reference would be great! All 290 tables moved over ok, some data loss, and I will look at that much closer next time I run my conversion app. Now Views and Stored procedures. Joel Fradkin Wazagua, LLC 2520 Trailmate Dr Sarasota, Florida 34243 Tel. 941-753-7111 ext 305 [EMAIL PROTECTED] www.wazagua.com Powered by Wazagua Providing you with the latest Web-based technology & advanced tools. © 2004. WAZAGUA, LLC. All rights reserved. WAZAGUA, LLC This email message is for the use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email and delete and destroy all copies of the original message, including attachments. ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] URGENT: Using function inside query
I need some help on the following problem. I have an account info table that has a hierarchy of accounts. The grouping goes from end user to organization to reseller. I have a PL/SQL function written that gives me the tree, so I just whittle it down by selecting the one id for a reseller: select account_id from roll_account(1186) where hierarchy_type_id = 2 The problem is I have data like this: account idname 1Company A 2Company B 3Company C 4Person 1 (in Company A) 5Person 2 (in Company A) If I pass it the number of let's say Person 2 -- the above query wold give me the account id of 1.How can I do a query using that function so that it automatically inserts the account_id to show the "rolled up" account id ? Bradley Miller NUVIO CORPORATION Phone: 816-444-4422 ext. 6757 Fax: 913-498-1810 http://www.nuvio.com [EMAIL PROTECTED]
Re: [SQL] mail + rfc822, rfc2822 + schema
On Tue, Jan 18, 2005 at 05:05:55PM +0200, Achilleus Mantzios wrote: > i am thinking of doing a remote MUA web-based system, based on > postgresql. > ... > So, i'd like to know if any of you has designed a schema serving as an > mail storage. If anything of caution arrose to you, and if you had > something to recommend. You might want to look at dbmail. http://www.dbmail.org/index.php?page=overview -- Ron Peterson Network & Systems Manager Mount Holyoke College http://www.mtholyoke.edu/~rpeterso ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] URGENT: Using function inside query
On Tue, Jan 18, 2005 at 02:51:01PM -0600, Bradley Miller wrote: > select account_id from roll_account(1186) where hierarchy_type_id = 2 > > The problem is I have data like this: > > account idname > > 1Company A > 2Company B > 3Company C > 4Person 1 (in Company A) > 5Person 2 (in Company A) > > If I pass it the number of let's say Person 2 -- the above query wold > give me the account id of 1.How can I do a query using that > function so that it automatically inserts the account_id to show the > "rolled up" account id ? I'm not sure I follow -- what account_id are you trying to insert and where are you trying to insert it? Do you want something like this? SELECT account_id FROM roll_account( (SELECT account_id FROM account_info WHERE name = 'Person 2') ) WHERE hierarchy_type_id = 2; If that's not what you're after then please provide an example of the kind of query you'd like to make and what results you expect. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
