Re: [GENERAL] 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 arta...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Getting the oid of an anyelement
Scott Bailey arta...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Getting the oid of an anyelement
2009/9/10 Tom Lane t...@sss.pgh.pa.us: Scott Bailey arta...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Getting the oid of an anyelement
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)
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Getting the oid of an anyelement
2009/9/10 Scott Bailey arta...@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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
