Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 999662E00C4 for ; Mon, 25 Feb 2008 01:37:23 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 11868-07 for ; Mon, 25 Feb 2008 01:37:21 -0400 (AST) X-Greylist: delayed 00:15:50.160263 by SQLgrey-1.7.5 Received: from po-out-1718.google.com (po-out-1718.google.com [72.14.252.158]) by postgresql.org (Postfix) with ESMTP id 39FC32E00E7 for ; Mon, 25 Feb 2008 01:37:21 -0400 (AST) Received: by po-out-1718.google.com with SMTP id c31so2575086poi.3 for ; Sun, 24 Feb 2008 21:37:20 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:from:to:cc:references:in-reply-to:subject:date:message-id:mime-version:content-type:content-transfer-encoding:x-mailer:thread-index:content-language; bh=4nMCzq2/rY13vpUpe+aNUBlD5GQLbMJ5ct5/OAxwWCo=; b=QLwAvAe+1X1mpWfF1cWVcqTQTpkzIdLjVgGe+x2BgAwdDPVSWvDEpzMtJTfk2qVYmxMnJmFxQBMjmX6OIQQ4H+bg5K9RKUPJqEh8vCSY0k2eDcjC15EIOesY5K5r7+pzRwi332bXkHjrhi80GRRQnJIKzqOeMLJBJsVYxZKY6Zc= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=from:to:cc:references:in-reply-to:subject:date:message-id:mime-version:content-type:content-transfer-encoding:x-mailer:thread-index:content-language; b=aG3u7i7qYQ9a7jsDmjuSu3aXLdrqglpVHnzXumKpl8OHDUDRkwTopcVRJAEtN64kkotN5egJPbP1VszyinQRcsy5VP+ZmDl6gRX/hRwt/FYW9o32zzbg1Hkod+EHAvhxBg+XL4W2WVATeA5U6OltJwHo++RxEl2lsB7NUmiu/ZY= Received: by 10.141.164.13 with SMTP id r13mr1706325rvo.90.1203916889983; Sun, 24 Feb 2008 21:21:29 -0800 (PST) Received: from BITDLLT03 ( [122.162.21.57]) by mx.google.com with ESMTPS id k41sm11061524rvb.24.2008.02.24.21.21.25 (version=SSLv3 cipher=RC4-MD5); Sun, 24 Feb 2008 21:21:28 -0800 (PST) From: "Jyoti Seth" To: "'johnf'" Cc: References: <001501c8752d$391c8e50$ab55aaf0$@com> <001a01c87534$84eea280$8ecbe780$@com> <47BEA582.A3DD.0030.0@indicator.be> <200802220830.56917.jfabiani@yolo.com> In-Reply-To: <200802220830.56917.jfabiani@yolo.com> Subject: Re: postgresql function not accepting null values inselect statement Date: Mon, 25 Feb 2008 10:50:04 +0530 Message-ID: <002401c8776e$17698b80$463ca280$@com> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit X-Mailer: Microsoft Office Outlook 12.0 Thread-Index: Ach1nrdDgaqBWtTrQ0+nnKPizG0hZwBzzdXA Content-Language: en-us X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200802/176 X-Sequence-Number: 30267 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: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] 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