Getting the oid of an anyelement

Hello

please, try to look on function pg_typeof

postgres=# CREATE OR REPLACE FUNCTION x(anyelement)
RETURNS oid AS $$
SELECT pg_typeof($1)::oid; $$ LANGUAGE sql;
CREATE FUNCTION
postgres=# select x(10);
x
----
23
(1 row)

postgres=# select x(current_date);
x
------
1082
(1 row)

postgres=#

regards
Pavel Stehule

2009/9/10 Scott Bailey <artacus@comcast.net>:

Scott Bailey <artacus@comcast.net> writes:

Specifically, I'm trying to make a function like Oracle's dump that will
take anything as input and return the internal representation of it. So
I want to determine what type was passed in and call the appropriate
send function.

You would need to write that in C.

regards, tom lane

2009/9/10 Tom Lane <tgl@sss.pgh.pa.us>:

Scott Bailey <artacus@comcast.net> writes:

Specifically, I'm trying to make a function like Oracle's dump that will
take anything as input and return the internal representation of it. So
I want to determine what type was passed in and call the appropriate
send function.

You would need to write that in C.

You don't need write it. orafce has it.

http://archives.postgresql.org/pgsql-committers/2009-02/msg00197.php

regards
Pavel Stehule

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)

2009/9/10 Scott Bailey <artacus@comcast.net>:

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;

this code is correct - if you would to search some on disk.

regards
Pavel Stehule