Thread: Type Name / Internal name returned by pg_catalog.format_type with/without prepended schema name?

Type Name / Internal name returned by pg_catalog.format_type with/without prepended schema name?

From

Glyn Astill

Date:

27 June 2012, 06:48:07

Hi Guys,

I was wondering if anyone could shed some light with type names returned by pg_catalog.format_type sometimes having the schema name prepended, and sometimes not? I'm calling it like format_type(pg_type.oid, NULL) .

I'm using pg9.0, but I remember seeing this years ago on older versions too - I just can't remember what I did about it.

I've got two types that appear to have been created in the same way, except that pg_catalog.format_type returns the schema name for one, but not the other.  I can't reproduce this just by running the sql used to create the types now though.

CREATE TYPE website.block_selection AS
   (block character varying,
    description character varying,
    amount numeric,
    "left" integer,
    best integer,
    type integer,
    singles_only boolean);

CREATE TYPE website.buy_with_detail AS
   (price numeric,
    must_buy_with integer[],
    limit_type text,
    multiplier integer);

SEE=# \dT+ website.block_selection
                              List of data types
 Schema  |      Name       |  Internal name  | Size  | Elements | Description
---------+-----------------+-----------------+-------+----------+-------------
 website | block_selection | block_selection | tuple |          |


SEE=# \dT+ website.buy_with_detail
                                  List of data types
 Schema  |          Name           |  Internal name  | Size  | Elements | Description
---------+-------------------------+-----------------+-------+----------+-------------
 website | website.buy_with_detail | buy_with_detail | tuple |          |


Any ideas how this could have come about? All the types were created on 9.0, and I've tried with and without the website schema in the search path etc, but I'm sure I'm just missing something simple?

Thanks
Glyn

Re: Type Name / Internal name returned by pg_catalog.format_type with/without prepended schema name?

From

Tom Lane

Date:

27 June 2012, 10:31:38

Glyn Astill <glynastill@yahoo.co.uk> writes:
> I was wondering if anyone could shed some light with type names returned by pg_catalog.format_type sometimes having
theschema name prepended, and sometimes not? I'm calling it like format_type(pg_type.oid, NULL) . 

General case: the schema name is used if the type wouldn't be found
without it, according to your current search_path.

There are some specific built-in types for which schema names are
never used, because their names are actually keywords according to SQL
(which a fortiori means there's no ambiguity on the lookup side).

            regards, tom lane

Re: Type Name / Internal name returned by pg_catalog.format_type with/without prepended schema name?

From

Glyn Astill

Date:

27 June 2012, 10:36:09

>________________________________
> From: Tom Lane <tgl@sss.pgh.pa.us>
>To: Glyn Astill <glynastill@yahoo.co.uk>
>Cc: "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
>Sent: Wednesday, 27 June 2012, 14:31
>Subject: Re: [GENERAL] Type Name / Internal name returned by pg_catalog.format_type with/without prepended schema
name?
>
>Glyn Astill <glynastill@yahoo.co.uk> writes:
>> I was wondering if anyone could shed some light with type names returned by pg_catalog.format_type sometimes having
theschema name prepended, and sometimes not? I'm calling it like format_type(pg_type.oid, NULL) . 
>
>General case: the schema name is used if the type wouldn't be found
>without it, according to your current search_path.
>
>There are some specific built-in types for which schema names are
>never used, because their names are actually keywords according to SQL
>(which a fortiori means there's no ambiguity on the lookup side).
>

However none of that explains why one of the types is returned with the schema name and the other is not, both are in
thesame schema which is in the current search_path.

Re: Type Name / Internal name returned by pg_catalog.format_type with/without prepended schema name?

From

Tom Lane

Date:

27 June 2012, 11:04:05

Glyn Astill <glynastill@yahoo.co.uk> writes:
>> From: Tom Lane <tgl@sss.pgh.pa.us>
>> General case: the schema name is used if the type wouldn't be found
>> without it, according to your current search_path.

> However none of that explains why one of the types is returned with the schema name and the other is not, both are in
thesame schema which is in the current search_path. 

It's difficult to comment on that without a concrete example, but
"visible according to search_path" does not only mean "in a schema
that's in search_path".  It also requires "not masked by a similarly
named object appearing earlier in the path".

            regards, tom lane