Re: [SQL] postgresql function not accepting null valuesinselect statement
In the documentation "IS NOT DISTINCT FROM" only appears in version 8.2 The 8.0 and 8.1 documentation only mentions "IS DISTINCT FROM". http://www.postgresql.org/docs/8.2/static/functions-comparisons.html http://www.postgresql.org/docs/8.1/static/functions-comparisons.html http://www.postgresql.org/docs/8.0/static/functions-comparisons.html I haven't tried it, but probably replacing WHERE f.statecd IS NOT DISTINCT FROM p_statecd) with WHERE not(f.statecd IS DISTINCT FROM p_statecd)) will do the same. >>> "Jyoti Seth" <[EMAIL PROTECTED]> 2008-02-25 6:20 >>> I have tried this, but it is showing following error: ERROR: syntax error at or near "DISTINCT" SQL state: 42601 Thanks, Jyoti -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of johnf Sent: Friday, February 22, 2008 10:01 PM To: pgsql-sql@postgresql.org Subject: Re: [SQL] postgresql function not accepting null values inselect statement On Friday 22 February 2008 01:35:47 am Bart Degryse wrote: > Can you try this... > > CREATE OR REPLACE FUNCTION getfunctionaries(p_statecd integer) > RETURNS SETOF t_functionaries AS > $BODY$ > DECLARE > rec t_functionaries%ROWTYPE; > BEGIN > FOR rec IN ( > SELECT f.functionaryid, f.category, f.description > FROM functionaries f > WHERE f.statecd IS NOT DISTINCT FROM p_statecd) > LOOP > return next rec; > END LOOP; > return; > END; > $BODY$ > LANGUAGE 'plpgsql' VOLATILE; > a newbie question. Could you explain why yours works? I don't understand how it works if p_statecd = NULL -- John Fabiani ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ( http://archives.postgresql.org/ )
Re: [SQL] Bouncing replies [was: SQL standards in Mysql]
On 2008-02-22 21:34, Scott Marlowe wrote: Bouncing messages from a public list is kinda rude. No more so, than sending two copies of your reply to me, because you don't go up to your mailer's "To:" line and manually delete the extra address (as I do on EVERY reply I send to this list). In fact, if you do that, you won't get the bounce. I do it out of consideration for others: since _I_ would prefer to not receive two copies of replies, I assume that _others_ prefer the same. I don't whine about it; I just do it. So, in return for that consideration, I get your reply. How did getting the bounce hurt you or your computer, or make any extra work for you, more than I would have to do when I get a duplicate message Perhaps I've been working with computers too long. I've been PAID as a full-time software developer for the past 40 years (45 if you count part-time employment in college), and I'm AMAZED at the amount of intolerance I see on the Internet with respect to eMails. Some people whine because the reply is at the top of the message rather than at the bottom. There are perfectly good reasons for replying at the top OR at the bottom, depending upon the circumstances. Other people whine because the sender does not wrap his/her eMail at 76 columns, or because the eMail is ALL CAPS, or some other imagined slight. Being an ADAPTABLE human being, rather than UPSET people who aren't similarly inclined, I simply ADAPT to the environment in most cases. In this particular case: Look, if you're going to send email to this list from an address that doesn't accept email from anything but this list, and since this list by default is a "reply to all" list (i.e. people hit reply to all) the LEAST you can do is reconfigure your Mail client to change the reply to field to point to whatever list it is you are sending to. A good idea, but I use this eMail address to reply to multiple pgsql-xxx lists. That would require a separate sender address for each list, but I can do that if I'm really hurting other people. It just means that I will have to delete the duplicate replies, rather than have to hear about the problems (which have yet to be identified) the bounces causes other people. I'll consider it. Or have the decency to program your MTA to just throw those messages away. That's a universally discredited idea among mail administrators: You either reject unwanted mail _during_the_SMTP_dialog_, or you forward it on to the recipient. The former is strongly preferred for dealing with spam (which is why I bounce non-list replies), to avoid the bandwidth, storage, and other issues. Now, for anyone who can't deal with the above, either: 1. don't reply to my posts; 2. edit the "To:" line in your replies to me; or 3. send me your eMail address and I will manually whitelist you (note that the whitelisting will disappear if some spammer uses your eMail address to circumvent the bounces). In fact, I'm surprised that spammers don't already do that using one of the administrator accounts here. My eMail address here has been one of the largest targets for the spam my SMTP server rejects over the past few years, and I haven't even been very active here. Sincerely, Dean -- Mail to my list address MUST be sent via the mailing list. All other mail to my list address will bounce.
Re: [SQL] Bouncing replies [was: SQL standards in Mysql]
On Mon, Feb 25, 2008 at 9:08 PM, Dean Gibson (DB Administrator) <[EMAIL PROTECTED]> wrote: > > On 2008-02-22 21:34, Scott Marlowe wrote: > > > Bouncing messages from a public list is kinda rude. > > No more so, than sending two copies of your reply to me, because you don't > go up to your mailer's "To:" line and manually delete the extra address (as > I do on EVERY reply I send to this list). It is considered polite to follow the customs of the groups in which one participates. For example, if you meet with Japanese people as part of your work you should bow whilst exchanging business cards, take care to read the card you are given, and not place it in the back pocket of your trousers. In this group we use the mj2 mailing list manager which by default will not send you a direct copy of any message which also has your email address explicitly listed. We prefer to use reply-all when responding to people, and that has become the custom here. It would be appreciated if you respected that custom during your time with us. Regards, Dave ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Bouncing replies [was: SQL standards in Mysql]
Dean Gibson (DB Administrator) wrote: On 2008-02-22 21:34, Scott Marlowe wrote: Bouncing messages from a public list is kinda rude. No more so, than sending two copies of your reply to me, because you don't go up to your mailer's "To:" line and manually delete the extra address (as I do on EVERY reply I send to this list). This is twice in as many days my old iLamp mail machine has been set aflame by the fires of a heated discussion about how a mailing list's reply-to is set. It gets hot enough just running Thunderbird. If you're going to continue this off-topic discussion, might I suggest taking it off-list? Interestingly, yesterday's flame-war took place because someone was adamant about just the opposite of your argument. Colin ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] Bouncing replies [was: SQL standards in Mysql]
On Mon, Feb 25, 2008 at 3:29 PM, Dave Page <[EMAIL PROTECTED]> wrote: > On Mon, Feb 25, 2008 at 9:08 PM, Dean Gibson (DB Administrator) > <[EMAIL PROTECTED]> wrote: > > > > On 2008-02-22 21:34, Scott Marlowe wrote: > > > > Bouncing messages from a public list is kinda rude. > > > > No more so, than sending two copies of your reply to me, because you don't > > go up to your mailer's "To:" line and manually delete the extra address (as > > I do on EVERY reply I send to this list). This is to Dean. I'm not editing my to: line on every single post. I'll hit reply to all. This list is designed to have it work that way, so that if the mailing list is running slow, the people participating in the discussion can keep up with it and if you're having problems now you don't have to wait 8 hours for the mailing list machine to get restarted or whatever it takes to make it behave sometimes. Also, every email client I've used in the last 5 years is smart enough to take the same message from two sources (i.e. the list / and the sender) and notice they're the same and NOT show them twice. It's not rocket science, and it's not uncommon, and it's not hard. If your client doesn't have that ability, I'd have to wonder what you're using. I think even pine can do this. It's just as easy to have your email program simply take anything that comes into it and toss it into the garbage and ignore it than to send a bounce message that uses up resources and bandwidth saying you don't want email from the people on the list, just the list. Especially since the way THIS list works, by generally accepted standards, is to hit reply to all. You'll notice I didn't answer you earlier. That's because you (Dean) have been put on my ignore list so I don't accidentally reply to you when you post, since it's the simplest and easiest way to stop getting bounce messages from you. Note this means I will miss your response, so don't bother sending one from your current email address to me, or to me through the list. You completely ignored my first post about this, and I really don't feel like having a complex involved conversation on basic email courtesy. Just one simple change in your software and voila it drops the dups instead of sending bounce messages. Good day. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] Bounce test
I have changed something in my eMail client regarding receiving messages. If a couple people (who don't mind getting bounces if this doesn't work) would just "Reply" and/or "Reply All" to this message, I'd appreciate it. Sincerely, Dean -- Mail to my list address should normally be sent via the mailing list. All other mail to my list address __MAY__ bounce. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Bounce test
On Monday 25 February 2008 7:13 pm, Dean Gibson (DB Administrator) wrote: > I have changed something in my eMail client regarding receiving > messages. If a couple people (who don't mind getting bounces if this > doesn't work) would just "Reply" and/or "Reply All" to this message, I'd > appreciate it. > > Sincerely, Dean Test -- Adrian Klaver [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Bounce test
On Monday 25 February 2008 7:13 pm, Dean Gibson (DB Administrator) wrote: > I have changed something in my eMail client regarding receiving > messages. If a couple people (who don't mind getting bounces if this > doesn't work) would just "Reply" and/or "Reply All" to this message, I'd > appreciate it. > > Sincerely, Dean It bounced. -- Adrian Klaver [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Bounce test
Adrian Klaver <[EMAIL PROTECTED]> writes: > On Monday 25 February 2008 7:13 pm, Dean Gibson (DB Administrator) wrote: >> I have changed something in my eMail client regarding receiving >> messages. If a couple people (who don't mind getting bounces if this >> doesn't work) would just "Reply" and/or "Reply All" to this message, I'd >> appreciate it. > It bounced. The OP should note that this is a good way to get shunned on these lists. regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] Bouncing replies [was: SQL standards in Mysql]
On Mon, Feb 25, 2008 at 9:01 PM, Dean K. Gibson <[EMAIL PROTECTED]> wrote: > You are missing the point of why I do this. If it weren't for spammers No, I'm not. You're tilting at windmills. If you post to a public list, you will get spammed on that email address and there's not a lot you can do about that. What you can do is use basic spam detection and deletion software, like spam assassin, to sort the wheat from the chaff. I use gmail to read the lists. It's not perfect by any means. But I see none of the thousands of spam messages that hit my inbox everyday. We had spam assassin the last place I worked and it was nearly 100% correct on getting spam, and it's fairly easy to set it up to be able to learn spam from you telling it any that slipped through. P.s. I've been using the internet since the mid 80s, and programming since the late 70s too. I remember a spam free internet, a spam free usenet even. Wish it could come back, but know that it won't. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Bounce test
On Mon, Feb 25, 2008 at 9:24 PM, Tom Lane <[EMAIL PROTECTED]> wrote: > Adrian Klaver <[EMAIL PROTECTED]> writes: > > On Monday 25 February 2008 7:13 pm, Dean Gibson (DB Administrator) wrote: > >> I have changed something in my eMail client regarding receiving > >> messages. If a couple people (who don't mind getting bounces if this > >> doesn't work) would just "Reply" and/or "Reply All" to this message, I'd > >> appreciate it. > > > It bounced. > > The OP should note that this is a good way to get shunned on these lists. In his defense, he's trying to fix the issue. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Bounce test
had to dig it outta the trash. Hit reply (there was no reply all) and it looks like it's going to the right place, pgsql-sql@postgresql.org (someone who hates top posting in technical discussions, but admits there's time, like these, when it makes perfect sense... :) On Mon, Feb 25, 2008 at 9:13 PM, Dean Gibson (DB Administrator) <[EMAIL PROTECTED]> wrote: > I have changed something in my eMail client regarding receiving > messages. If a couple people (who don't mind getting bounces if this > doesn't work) would just "Reply" and/or "Reply All" to this message, I'd > appreciate it. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Bounce test
On Mon, Feb 25, 2008 at 9:17 PM, Adrian Klaver <[EMAIL PROTECTED]> wrote: > On Monday 25 February 2008 7:13 pm, Dean Gibson (DB Administrator) wrote: > > I have changed something in my eMail client regarding receiving > > messages. If a couple people (who don't mind getting bounces if this > > doesn't work) would just "Reply" and/or "Reply All" to this message, I'd > > appreciate it. > > > > Sincerely, Dean > Test So, your email client puts Dean's email address back in? Might I ask what option you chose? And if you have more than one? On gmail there's the reply link only. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Bounce test
Scott Marlowe wrote: On Mon, Feb 25, 2008 at 9:17 PM, Adrian Klaver <[EMAIL PROTECTED]> wrote: On Monday 25 February 2008 7:13 pm, Dean Gibson (DB Administrator) wrote: > I have changed something in my eMail client regarding receiving > messages. If a couple people (who don't mind getting bounces if this > doesn't work) would just "Reply" and/or "Reply All" to this message, I'd > appreciate it. > > Sincerely, Dean Test So, your email client puts Dean's email address back in? Might I ask what option you chose? And if you have more than one? On gmail there's the reply link only. FWIW, if I hit "reply all" on the OP, the only address Thunderbird fills is [EMAIL PROTECTED] Seems "reply-to" works. Colin ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] Bouncing replies [was: SQL standards in Mysql]
On Mon, 25 Feb 2008 21:26:50 -0600 "Scott Marlowe" <[EMAIL PROTECTED]> wrote: > P.s. I've been using the internet since the mid 80s, and programming > since the late 70s too. I remember a spam free internet, a spam free > usenet even. Wish it could come back, but know that it won't. Damn that Canter and Siegel! -- D'Arcy J.M. Cain <[EMAIL PROTECTED]> | Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Bounce test
On Monday 25 February 2008 7:32 pm, Scott Marlowe wrote: > On Mon, Feb 25, 2008 at 9:17 PM, Adrian Klaver <[EMAIL PROTECTED]> wrote: > > On Monday 25 February 2008 7:13 pm, Dean Gibson (DB Administrator) wrote: > > > I have changed something in my eMail client regarding receiving > > > messages. If a couple people (who don't mind getting bounces if this > > > doesn't work) would just "Reply" and/or "Reply All" to this message, > > > I'd appreciate it. > > > > > > Sincerely, Dean > > > > Test > > So, your email client puts Dean's email address back in? Might I ask > what option you chose? And if you have more than one? On gmail > there's the reply link only. > I hit Reply All which sends to the list and to the OP. Same as this reply. -- Adrian Klaver [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Bounce test
On Mon, Feb 25, 2008 at 9:54 PM, Adrian Klaver <[EMAIL PROTECTED]> wrote: > > So, your email client puts Dean's email address back in? Might I ask > > what option you chose? And if you have more than one? On gmail > > there's the reply link only. > > > I hit Reply All which sends to the list and to the OP. Same as this reply. weird. what client are you using? ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Bounce test
On Monday 25 February 2008 8:07 pm, Scott Marlowe wrote: > On Mon, Feb 25, 2008 at 9:54 PM, Adrian Klaver <[EMAIL PROTECTED]> wrote: > > > So, your email client puts Dean's email address back in? Might I ask > > > what option you chose? And if you have more than one? On gmail > > > there's the reply link only. > > > > I hit Reply All which sends to the list and to the OP. Same as this > > reply. > > weird. what client are you using? > Kmail. I get four options Reply,Reply To Mailing-List (which both send to the mailing list only), Reply To Author , Reply All (send to author and mailing list). As I understand it Dean is trying to deal with the case where a reply is sent to his list address directly which happens with Reply All. -- Adrian Klaver [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
