Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 4592C2E0101 for ; Mon, 25 Feb 2008 04:20:26 -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 89478-07 for ; Mon, 25 Feb 2008 04:20:18 -0400 (AST) X-Greylist: delayed 00:15:01.61156 by SQLgrey-1.7.5 Received: from interscan.indicator.com (mail.indicator.be [193.121.199.3]) by postgresql.org (Postfix) with ESMTP id DB2922E018F for ; Mon, 25 Feb 2008 04:20:20 -0400 (AST) Received: from DOMEXT-MTA by webaccess.indicator.bewith Novell_GroupWise; Mon, 25 Feb 2008 09:05:13 +0100 Message-Id: <47C284BF.A3DD.0030.0@indicator.be> X-Mailer: Novell GroupWise Internet Agent 7.0.1 Date: Mon, 25 Feb 2008 09:05:07 +0100 From: "Bart Degryse" To: "Jyoti Seth" Cc: Subject: Re: postgresql function not accepting null valuesinselect statement References: <001501c8752d$391c8e50$ab55aaf0$@com><001a01c87534$84eea280$8ecb e780$@com> <47BEA582.A3DD.0030.0@indicator.be><200802220830.56917.jfabiani@yolo.com><002401c8776e$17698b80$463ca280$@com> In-Reply-To: <002401c8776e$17698b80$463ca280$@com> Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="=__Part381EB0A3.1__=" X-imss-version: 2.050 X-imss-result: Passed X-imss-approveListMatch: *@indicator.be X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200802/181 X-Sequence-Number: 30272 --=__Part381EB0A3.1__= Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: quoted-printable 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=20 http://www.postgresql.org/docs/8.1/static/functions-comparisons.html=20 http://www.postgresql.org/docs/8.0/static/functions-comparisons.html=20 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. =20 >>> "Jyoti Seth" 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: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org= ]=20 On Behalf Of johnf Sent: Friday, February 22, 2008 10:01 PM To: pgsql-sql@postgresql.org=20 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=20 it works if p_statecd =3D NULL --=20 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/ ) --=__Part381EB0A3.1__= Content-Type: text/html; charset=US-ASCII Content-Transfer-Encoding: quoted-printable Content-Description: HTML
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".
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" <jyotiseth2001@gmail.com> = 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-----<= BR>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_state= cd 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
>&= nbsp;    return next rec;
>   END LOOP;
&= gt;   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 = =3D 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?

        &= nbsp;      http://archives.postgresql.org
--=__Part381EB0A3.1__=--