Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id C1C3F2E01BC for ; Mon, 25 Feb 2008 03:14:28 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 34016-01-9 for ; Mon, 25 Feb 2008 03:14:21 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from hu-out-0506.google.com (hu-out-0506.google.com [72.14.214.229]) by postgresql.org (Postfix) with ESMTP id A14C12E014F for ; Mon, 25 Feb 2008 03:12:40 -0400 (AST) Received: by hu-out-0506.google.com with SMTP id 22so1840142hug.23 for ; Sun, 24 Feb 2008 23:12:39 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references; bh=cv1K25nHTnlZObh4XFUTi5EBl06J3BdpLC9SiteWUTE=; b=XXkePVv24Wb4Wm852XM3pgiBDMKA0cYRVjub4yC47Z50+75VCIm7/pAkJQdwy7tCCbMRdj65NaASxtTIL//cCH/KRjIv5HudtcSDomspc3E2aOh6JRhe4oRZJEHUVMreYwlJN5kkipxCEql5wAqovSm48x4bi8Ym060iYBkPHuY= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references; b=L2hTMXxNMewkDQc6hngf36sppD+4qvXe21axF37gBLn6m2109LdPc+KSLzDowzVRbXI+Qxnwd3atmJC0D0FOdxTaUKdvinh0C7fen9KlAcNAAUpYGKv23ucq0piVIWu1zNgQSHUMnh+4jc2VLEhWtXLIjIBzzEybjfM4H1gXxaE= Received: by 10.82.146.10 with SMTP id t10mr5039716bud.8.1203922595337; Sun, 24 Feb 2008 22:56:35 -0800 (PST) Received: by 10.82.112.18 with HTTP; Sun, 24 Feb 2008 22:56:35 -0800 (PST) Message-ID: <36af4bed0802242256i7f0e7a36i4f0cf4b567dfadc2@mail.gmail.com> Date: Mon, 25 Feb 2008 12:26:35 +0530 From: "Robins Tharakan" To: pgsql-sql@postgresql.org Subject: Re: postgresql function not accepting null values inselect statement In-Reply-To: <36af4bed0802242256g7dd98934mc7bdbba1a6bf33b3@mail.gmail.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_13196_17691776.1203922595329" References: <001501c8752d$391c8e50$ab55aaf0$@com> <001a01c87534$84eea280$8ecbe780$@com> <47BEA582.A3DD.0030.0@indicator.be> <200802220830.56917.jfabiani@yolo.com> <002401c8776e$17698b80$463ca280$@com> <36af4bed0802242256g7dd98934mc7bdbba1a6bf33b3@mail.gmail.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200802/180 X-Sequence-Number: 30271 ------=_Part_13196_17691776.1203922595329 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline > > What version of PostGreSQL are you using ? > Are you sure there was no typing error ? This SQL should work in the most > recent version of PG.( at least version 8.1 onwards) > > *Robins* > > > On Mon, Feb 25, 2008 at 10:50 AM, Jyoti Seth > wrote: > > > 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 > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 4: Have you searched our list archives? > > > > http://archives.postgresql.org > > > > ------=_Part_13196_17691776.1203922595329 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline
What version of PostGreSQL are you using ?
Are you sure there was no typing error ? This SQL should work in the most recent version of PG.( at least version 8.1 onwards)

Robins


On Mon, Feb 25, 2008 at 10:50 AM, Jyoti Seth <jyotiseth2001@gmail.com> wrote:
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


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

              http://archives.postgresql.org


------=_Part_13196_17691776.1203922595329--