Обсуждение: Quick System Catalog Query Question

Поиск
Список
Период
Сортировка

Quick System Catalog Query Question

От
Terry Hampton
Дата:

All,

select attname, atttypmod, atttypid  from pg_attribute where
attrelid=3701787 and attnum > 0;

returns:

   attname   | atttypmod | atttypid
-------------+-----------+----------
  acct_name  |       132 |     1043
  grp               |        54 |     1043
  name           |       260 |     1043
  id                  |        54 |     1043
  proc_by        |        54 |     1043
  fldnames      |        -1 |       25
  flddata          |        -1 |       25


    I can't determine what SQL to which system catalog will associate
    "1043" to "varchar"   and  "25" to "text"


    many thanks for you help

        7.3.2  on Linux


            Terry





Terry L. Hampton
Project Manager
LimaCorp, LLC   www.limacorp.com
513.587.1874



Re: Quick System Catalog Query Question

От
Manuel Sugawara
Дата:
Terry Hampton <thampton@limacorp.com> writes:

> I can't determine what SQL to which system catalog will associate
> "1043" to "varchar" and "25" to "text"

pg_type where oid = 1043, ie:

SELECT * FROM pg_type WHERE oid = 1043;

Regards,
Manuel.

Re: Quick System Catalog Query Question

От
Manuel Sugawara
Дата:
Terry Hampton <thampton@limacorp.com> writes:

>     Manuel,
>
> Many thanks for your quick reply.  When I
> enter:      SELECT * FROM pg_type where typname='varchar';
>
>        I get:
>
> typname | typowner | typlen | typprtlen | typbyval | typtype | typisdefined
> | typdelim | typrelid | typelem | typinput  | typoutput  | typreceive |
> typsend   | typalign | typstorage | typdefault
>
>
---------+----------+--------+-----------+----------+---------+--------------+----------+----------+---------+-----------+------------+------------+------------+----------+------------+------------
>   varchar |        1 |     -1 |        -1 | f        | b       | t      | ,
> |        0 |       0 | varcharin | varcharout | varcharin  | varcharout | i
> | x          |
>
>
>
> I do not see "1043",  thus my original email.

OID, among others is a hidden column, you need to explicitly ask for
it in your query:

SELECT oid, * FROM pg_type WHERE typname='varchar';

>
> I'm just now starting to work with OID's.    Does every row
> in the system catalogs have an OID?

Most of them.

> reference to another tablename.oid - to get the actual name, simply
> use the known OID from the first table as a lookup value into the
> second ( reference table ), to get the textual vlaue.  ?  Correct ?

Correct.

Regards,
Manuel.