Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.postgresql > #368 > unrolled thread

Composite fields and null

Back to article view | Back to comp.databases.postgresql

Contents

  Composite fields and null Steve Rogerson <steve@nemodtcwey.co.uk> - 2012-06-26 17:15 +0100
    Re: Composite fields and null Jasen Betts <jasen@xnet.co.nz> - 2012-06-28 12:09 +0000

#368 — Composite fields and null

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

[toc] | [next] | [standalone]

#369

On 2012-06-26, Steve Rogerson <steve@nemodtcwey.co.uk> wrote:

>
> 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 ...

...

> 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?

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

2: furthermore

>        IF part1 = NULL
	
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@netfront.net ---

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.postgresql

csiph-web