RE: [GENERAL] Two variable passed to PL/Function and on is NULL
От | Michael J Davis |
---|---|
Тема | RE: [GENERAL] Two variable passed to PL/Function and on is NULL |
Дата | |
Msg-id | 93C04F1F5173D211A27900105AA8FCFC1455CD@lambic.prevuenet.com обсуждение исходный текст |
Ответ на | [GENERAL] Two variable passed to PL/Function and on is NULL (Stuart Rison <stuart@ludwig.ucl.ac.uk>) |
Ответы |
Re: [SQL] RE: [GENERAL] Two variable passed to PL/Function and on is NULL
|
Список | pgsql-general |
Create an nz(int4) or nvl(int4) function that returns 0 if $1 is null. You could also create nz(text) that returns '' when $1 is null. Then do: select my_function(nz(value1), nz(value2)); This has worked for me. > -----Original Message----- > From: Stuart Rison [SMTP:stuart@ludwig.ucl.ac.uk] > Sent: Monday, June 14, 1999 10:50 AM > To: Herouth Maoz; pgsql-general@postgreSQL.org > Subject: Re: [GENERAL] Two variable passed to PL/Function and on is > NULL > > Hi Herouth, > > >At 15:44 +0300 on 14/06/1999, Stuart Rison wrote: > > > > > >> i) Is it the case that if you pass to variables to a postgres function > and > >> one is NULL, the function cannot tell which one? > >> ii) Is there a workaround of some kind (in particular in pl/pgsql)? > >> [Currently I am copying the table into a temp table and updating all > NULL > >> values to a token value.] > > > >I think you will do alright in pl/pgsql. Take for example the following > >function which returns 1000 when its argument is null: > > > > <snip - a function that works fine but take only one argument> > > The problem only occur when you have a function which takes more than one > argument. > > e.g. > > create function null1000(int4,int4) returns int4 as ' > DECLARE > first_arg alias for $1; > second_arg alias for $2; > BEGIN > IF first_arg IS NULL THEN > RETURN 1000; > ELSE > RETURN first_arg; > END IF; > END; > ' > language 'plpgsql'; > > cgh=> select * from test1; > nm|nm_two > ---+------ > 4| > 8| > | > 16| > 32| > 64| 6 > 128| 7 > 256| 8 > 512| 9 > | 10 > (10 rows) > > cgh=> select nm,nm_two,null1000(nm,nm_two) from test1; > nm|nm_two|null1000 > ---+------+-------- > 4| | 1000 > 8| | 1000 > | | 1000 > 16| | 1000 > 32| | 1000 > 64| 6| 64 > 128| 7| 128 > 256| 8| 256 > 512| 9| 512 > | 10| 1000 > (10 rows) > > as soon a NULL is passed as an argument, both values are treated as NULL > by > the function. I think the problem occurs before you even enter the > function itself; in other words, both values become NULL 'internally' and > not within the function so you can't trap them in the function itself with > 'IS NULL' checks. > > I think this is a know issue with 6.4 but there was some suggestion it may > have been corrected in 6.5 (fact, fiction?) > > furthermore, I'm unlikely to be moving to 6.5 in the near future so has > anyone got a workaround? > > cheers, > > S. > > +-------------------------+--------------------------------------+ > | Stuart Rison | Ludwig Institute for Cancer Research | > +-------------------------+ 91 Riding House Street | > | Tel. (0171) 878 4041 | London, W1P 8BT, UNITED KINGDOM. | > | Fax. (0171) 878 4040 | stuart@ludwig.ucl.ac.uk | > +-------------------------+--------------------------------------+
В списке pgsql-general по дате отправления:
Следующее
От: Tom LaneДата:
Сообщение: Re: [SQL] RE: [GENERAL] Two variable passed to PL/Function and on is NULL