Обсуждение: Getting the oid of an anyelement
If I've got a function (sql or plpgsql) that takes anyelement as a param, how do I determine the type name or oid that was actually passed in? I figure there is probably a function for this but darn if I can find it. 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. SELECT dump(current_date); dump ------------------------------------------ Type=date OID=1082 Len=4 Data=(0,0,13,212)
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>: > If I've got a function (sql or plpgsql) that takes anyelement as a param, > how do I determine the type name or oid that was actually passed in? I > figure there is probably a function for this but darn if I can find it. > > 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. > > SELECT dump(current_date); > > dump > ------------------------------------------ > Type=date OID=1082 Len=4 Data=(0,0,13,212) > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
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 > regards, tom lane > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
> 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 > > -- 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) > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >