Обсуждение: How can I expand serialized BLOBs into pseudo columns

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

Eric Brown wrote:
> 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.

What you're trying to do isn't supported in 7.4 and earlier. Works in
8.0.0 (which just went RC1) though:

regression=# SELECT expandobj(obj), * from mytable; -- this does not
  expandobj | id | obj
-----------+----+-----
  (x,y)     |  1 | x,y
(1 row)

and so does:
regression=# SELECT (expandobj(obj)).val1, (expandobj(obj)).val2, * from
mytable;
  val1 | val2 | id | obj
------+------+----+-----
  x    | y    |  1 | x,y
(1 row)

HTH,

Joe