Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 3B1AA2E00BB for ; Fri, 22 Feb 2008 05:36:00 -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 90406-02 for ; Fri, 22 Feb 2008 05:35:52 -0400 (AST) Received: from interscan.indicator.com (mail.indicator.be [193.121.199.3]) by postgresql.org (Postfix) with ESMTP id 2174E2E00F6 for ; Fri, 22 Feb 2008 05:35:55 -0400 (AST) Received: from DOMEXT-MTA by webaccess.indicator.bewith Novell_GroupWise; Fri, 22 Feb 2008 10:35:53 +0100 Message-Id: <47BEA582.A3DD.0030.0@indicator.be> X-Mailer: Novell GroupWise Internet Agent 7.0.1 Date: Fri, 22 Feb 2008 10:35:47 +0100 From: "Bart Degryse" To: "'Richard Huxton'" , "Jyoti Seth" Cc: Subject: Re: postgresql function not accepting null values inselect statement References: <001501c8752d$391c8e50$ab55aaf0$@com><47BE9058.9030909@archonet.com> <001a01c87534$84eea280$8ecbe780$@com> In-Reply-To: <001a01c87534$84eea280$8ecbe780$@com> Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="=__Part65439163.2__=" 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/163 X-Sequence-Number: 30254 --=__Part65439163.2__= Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: quoted-printable Can you try this... =20 CREATE OR REPLACE FUNCTION getfunctionaries(p_statecd integer) RETURNS SETOF t_functionaries AS $BODY$ DECLARE rec t_functionaries%ROWTYPE;=20 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; >>> "Jyoti Seth" 2008-02-22 10:22 >>> Hi, I have a the following procedure=20 CREATE OR REPLACE FUNCTION getfunctionaries(p_statecd integer) RETURNS SETOF t_functionaries AS $BODY$ DECLARE rec t_functionaries%ROWTYPE;=20 begin FOR rec IN=20 SELECT f.functionaryid, f.category,f.description FROM functionaries f where f.statecd=3Dp_statecd LOOP return next rec; END LOOP; return; end; $BODY$ LANGUAGE 'plpgsql' VOLATILE; In the functionaries table statecd is a null field. When I pass some = integer value to the above procedure it works correctly but if I pass null value = in p_statecd it doesn't show anything whereas it has values and if I write = the select statement separately it gives values Thanks, Jyoti -----Original Message----- From: Richard Huxton [mailto:dev@archonet.com]=20 Sent: Friday, February 22, 2008 2:35 PM To: Jyoti Seth Cc: pgsql-sql@postgresql.org=20 Subject: Re: [SQL] postgresql function not accepting null values in select statement Jyoti Seth wrote: >=20 > If I pass null value as the parameter of postgresql function, which is used > in the where clause of select statement is not functioning properly. Either: 1. You're talking about frooble(), in which case it's supposed to do that. or 2. You'll need to tell us what function it is, how you're using it and=20 what you think should happen. My guess is that you're getting a null as the result and that's not=20 doing what you'd expect in your where clause. --=20 Richard Huxton Archonet Ltd ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend --=__Part65439163.2__= Content-Type: text/html; charset=US-ASCII Content-Transfer-Encoding: quoted-printable Content-Description: HTML

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;

>>> "Jyoti Seth" <jyotiseth20= 01@gmail.com> 2008-02-22 10:22 >>>
Hi,

I have a the = following procedure

CREATE OR REPLACE FUNCTION getfunctionaries(p_s= tatecd integer)
RETURNS SETOF t_functionaries AS
$BODY$
DEC= LARE rec t_functionaries%ROWTYPE;
begin
    &nbs= p; FOR rec IN
SELECT f.functionaryid, f.category,f.descriptionFROM functionaries f
     where f.statecd=3Dp_state= cd

LOOP
        return next = rec;
        END LOOP;
  = ;     return;
   end;
$BODY$
= LANGUAGE 'plpgsql' VOLATILE;

In the functionaries table statecd is = a null field. When I pass some integer
value to the above procedure it = works correctly but if I pass null value in
p_statecd it doesn't show = anything whereas it has values and if I write the
select statement = separately it gives values

Thanks,
Jyoti

-----Original = Message-----
From: Richard Huxton [mailto:dev@archonet.com]
Sent: = Friday, February 22, 2008 2:35 PM
To: Jyoti Seth
Cc: pgsql-sql@postgr= esql.org
Subject: Re: [SQL] postgresql function not accepting null = values in select
statement

Jyoti Seth wrote:
>
> If = I pass null value as the parameter of postgresql function, which is
used=
> in the where clause of select statement is not functioning = properly.

Either:

1. You're talking about frooble(), in = which case it's supposed to do that.

or

2. You'll need to = tell us what function it is, how you're using it and
what you think = should happen.

My guess is that you're getting a null as the result = and that's not
doing what you'd expect in your where clause.

--
   Richard Huxton
   Archonet Ltd

---------------------------(end of broadcast)---------------------------TIP 6: explain analyze is your friend

--=__Part65439163.2__=--