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

Groups > comp.databases.postgresql > #368

Composite fields and null

Show all headers | View raw

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

Back to comp.databases.postgresql | Previous | Next — Next in thread | Find similar | Unroll thread

Thread

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

csiph-web