I've got a composite field:

CREATE TYPE my_type AS (
part1 varchar,
part2, varchar
);

and I'm going a db conversion that is taking an existing part1 value and based
on it, creating a my_type with a compiuted part2. I have a function to do it
like ...

CREATE FUNCTION fill_in_part2(part1 varchar) RETURNS my_type AS $$
DECLARE
new_part my_part;
BEGIN
IF part1 = NULL
THEN
RETURN NULL;
END IF;
new_part.part2 = 'ferret';
RETURN new_part;
END
$$ LANGUAGE 'plpgsql';


The problem is that if part1 is null I get (,), that is a non-null thing that
has each of it's parts null. I really really want to return "true" null rather
then "composite" null. Has anyone any ideas?

There are several fields involved so I can't do add a "where part1 is not
null" clause. So far the best I can come up with is doing, for each effected
field:

update table set my_new_part = null where (my_new_part).part1 = null;

Steve

On 2012-06-26, Steve Rogerson <steve (AT) nemodtcwey (DOT) co.uk> wrote:

....

1: your example code has obvious errors (won't run)

2: furthermore

you can't test for null using "="

IF part1 IS NULL

works for me.


3: Alternatively have you tried declaring the function as
"RETURNS NULL ON NULL INPUT"



--
⚂⚃ 100% natural

--- Posted via news://freenews.netfront.net/ - Complaints to news (AT) netfront (DOT) net ---