How can I expand serialized BLOBs into pseudo columns
От | Eric Brown |
---|---|
Тема | How can I expand serialized BLOBs into pseudo columns |
Дата | |
Msg-id | 5FC7AC8C-463A-11D9-BE63-000A95C7176C@mac.com обсуждение исходный текст |
Ответы |
Re: How can I expand serialized BLOBs into pseudo columns
|
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: