Обсуждение: 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
>