Обсуждение: passing null parameter to plpgsq functions
Hello I wrote a plpgsql function with few parameters ; when I call the function, some of the parameters can be null. In this case, all the parameters are considered as null in the function's body ! is it a feature ? how can I work around this ? Example : CREATE FUNCTION foo(text, text) RETURNS bool AS ' beginIF ($1 IS NULL) AND ($2 IS NULL) THEN RETURN FALSE;ELSE RETURN TRUE;END IF; end; ' LANGUAGE 'plpgsql'; SELECT foo(null,'bouh') => false (I expected true) SELECT foo('schfk',null) => false (I expected true) SELECT foo(null,null) => false (OK) SELECT foo('schfk','bouh') => true (OK)
From: "Picard, Cyril" <cyril.picard@eads-dsn.com> > Hello > I wrote a plpgsql function with few parameters ; when I call the function, > some of the parameters can be null. > In this case, all the parameters are considered as null in the function's > body ! is it a feature ? how can I work around this ? It's a feature in versions before 7.1 (or possibly 7.0.x) you can still get that behaviour in 7.1 by asking for strict null handling on a specific function. The only remedy AFAIK is to upgrade. - Richard Huxton
Thank you ! I planned to use the 7.1 to get the outer join capability. Is the 7.1 stable ? > -----Message d'origine----- > De: Richard Huxton [SMTP:dev@archonet.com] > Date: mardi 3 avril 2001 10:06 > À: Picard, Cyril; pgsql-sql@postgresql.org > Objet: Re: [SQL] passing null parameter to plpgsq functions > > From: "Picard, Cyril" <cyril.picard@eads-dsn.com> > > > Hello > > I wrote a plpgsql function with few parameters ; when I call the > function, > > some of the parameters can be null. > > In this case, all the parameters are considered as null in the > function's > > body ! is it a feature ? how can I work around this ? > > It's a feature in versions before 7.1 (or possibly 7.0.x) you can still > get > that behaviour in 7.1 by asking for strict null handling on a specific > function. > > The only remedy AFAIK is to upgrade. > > - Richard Huxton > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html
From: "Picard, Cyril" <cyril.picard@eads-dsn.com> > Thank you ! I planned to use the 7.1 to get the outer join capability. > > Is the 7.1 stable ? Pretty much - it's reached release candidate stage and I've been using it for development for a while now. - Richard Huxton