From: | Eric Brown <yogieric(at)mac(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | How can I expand serialized BLOBs into pseudo columns |
Date: | 2004-12-04 21:20:51 |
Message-ID: | 5FC7AC8C-463A-11D9-BE63-000A95C7176C@mac.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Mike Cox | 2004-12-04 22:07:50 | Preview of Fourth PostgreSQL RFD. |
Previous Message | Együd Csaba | 2004-12-04 21:10:34 | Posgres8 beta5 on WinXP won't start |