> please, try to look on function pg_typeof
Thanks Pavel. Just what I needed. But you're too late on the orafce
recommendation. I had already written it by the time you posted. I would
have written it any way though because Tom said I couldn't :)
> You would need to write that in C.
Two problems with that Tom. First, and most importantly, I never learned
C. (Otherwise, I'd be helping you guys develop). Second, I've already
determined that I was going to do this and I'm pretty darn hard headed.
So here it is in pl/pgsql.
CREATE OR REPLACE FUNCTION dump(
p_value anyelement
) RETURNS text AS
$$
DECLARE
v_type TEXT;
v_oid INT;
v_data BYTEA;
v_send TEXT;
BEGIN
SELECT t.typname, t.oid, t.typsend::text
INTO v_type, v_oid, v_send
FROM pg_type t
WHERE t.oid = pg_typeof($1);
IF v_send IS NULL OR v_send = '-' THEN
RAISE EXCEPTION 'Found no send function for %', $1;
ELSE
EXECUTE 'SELECT ' || v_send || '(' ||
quote_literal($1) || '::' || v_type || ')'
INTO v_data;
END IF;
RETURN 'Type=' || v_type ||
' OID=' || v_oid ||
' Len=' || length(v_data) ||
' Data=(' || array_to_string(bytes, ',') || ')'
FROM (
SELECT array(
SELECT get_byte(v_data, i)
FROM generate_series(0, length(v_data) - 1) i
) AS bytes
) sub;
END;
$$ LANGUAGE 'plpgsql' IMMUTABLE STRICT;
-- Usage --
VALUES (dump(100)),
(dump(10.1)),
(dump(random())),
(dump('foo'::text)),
(dump(current_date)),
(dump(current_timestamp));
column1
--------------------------------------------------------------
Type=int4 OID=23 Len=4 Data=(0,0,0,100)
Type=numeric OID=1700 Len=12 Data=(0,2,0,0,0,0,0,1,0,10,3,232)
Type=float8 OID=701 Len=8 Data=(63,236,234,4,253,128,0,0)
Type=text OID=25 Len=3 Data=(102,111,111)
Type=date OID=1082 Len=4 Data=(0,0,13,212)
Type=timestamptz OID=1184 Len=8 Data=(0,1,22,61,166,87,22,96)