Thread: getting domain information from query results

hi,

i have this schema:

  create domain almost_a_string AS text;
  create table object ( name_like_thing almost_a_string );

and i'm trying to go from the results, using postgresql's
frontend/backend protocol, of this query:

  select name_like_thing from object;

to the domain of the column name_like_thing, almost_a_string.

it seems, if my understanding of the protocol is correct, that the oid
of the underlying type, text in this case, is returned and not the oid
of the domain. i could get at the domain with an extra query, since the
originating table and column of each field in the result row is
included, but i'd really rather have to make that extra query. is there
something obvious that i'm missing?

finally, i don't think i'm the first person to have to answer this
question this way, both psql's \d command and the function pg_typeof
return almost_a_string (the domain) and not text (the type).

----

if it helps, here's what i'm trying to do: i would like a type, or
something like a type that my app, which talks to postgresql over the
frontend/backend protocol, can use to distinguish the values from one
column from the values of another, even though both columns are indexed,
queried and operated on (as far as =, <>, ilike, etc. are concerned)
just like text values. this isn't a new type, it's basically a column of
type text plus an enum column with a "class name" or "type" (or
something equally app specific) which i can no longer, given the age and
size of the schema, and how this ORM currently works, add in.

i tried creating a new type (which have solved my problem with the
"wrong" oid getting returned with the query results), but then realized
i'd have to implement all of the associated operators for my new type as
well (they'd just be cut 'n pastes of the equivalent operators for
varchar, but that's still a bit more work than i'd like to do at this
point).

thanks for the help,
--
-marco

Marco Baringer <mb@bese.it> writes:
> it seems, if my understanding of the protocol is correct, that the oid
> of the underlying type, text in this case, is returned and not the oid
> of the domain.

That's correct.  This was an intentional decision long ago, and we're
unlikely to reconsider now for fear of breaking applications.  (IIRC,
the argument was that client-side code was most likely to be concerned
with matters like the textual representation of the value, and so getting
told about domains would just complicate life.)

> i tried creating a new type (which have solved my problem with the
> "wrong" oid getting returned with the query results), but then realized
> i'd have to implement all of the associated operators for my new type as
> well (they'd just be cut 'n pastes of the equivalent operators for
> varchar, but that's still a bit more work than i'd like to do at this
> point).

If you'd dug a bit more deeply, you'd have noticed that varchar doesn't
*have* any operators; it relies on text's operators via an implicit cast
to text.  You could likely do the same for a custom string type.  This
answer doesn't scale to non-string data types of course, but it might
be good enough for your situation.

            regards, tom lane