How can I expand serialized BLOBs into pseudo columns

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

Responses

Browse pgsql-general by date

  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