I have a table (quite a few of them actually) where python objects are
serialized into a column. So a table might look like:
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
