CREATE TABLE mytable (id int, obj bytea);
When I'm trying to diagnose/debug things, I'd like to be able to expand the 'obj' column into multiple columns in a view. I created a type and wrote a plpgsql function that expands the object. i.e.:
CREATE TYPE myitem AS (val1 text, val2 text);
CREATE FUNCTION expandobj(bytea) returns myitem as '...' LANGUAGE plpgsql;
Then I tried: SELECT expandobj(obj), * from mytable; I get: ERROR: cannot display a value of type record
I think/hope I'm missing something pretty stupid, but I can't figure out what it might be. Any help would be appreciated. There might even be a quite better way.
Eric.
Here's a script to reproduce the problem: CREATE TABLE mytable (id int, obj text); INSERT INTO mytable VALUES (1, 'x,y'); CREATE TYPE myitem AS (val1 text, val2 text); CREATE or REPLACE FUNCTION expandobj(text) returns myitem as ' DECLARE items text[]; item myitem%rowtype; BEGIN items := string_to_array($1, '',''); item.val1 := items[1]; item.val2 := items[2]; return item; END ' LANGUAGE 'plpgsql';
SELECT * from expandobj('a,b'); -- this works SELECT expandobj(obj), * from mytable; -- this does not -- I'd like to see four columns: val1, val2, id, obj
---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])