Thread: Query to get name a data type of a view

When I run the following query,


SELECT column_name,data_type
FROM information_schema.columns
WHERE table_name = 'mfg_part_view';


I get the following result:

 column_name  |     data_type     
 --------------+-------------------
  mfg          | USER-DEFINED
  mfg_part_no  | character varying
  unit         | USER-DEFINED
  descrip      | character varying
  mfg_part_key | integer
(5 rows)

The SQL that creates this view is as follows:

CREATE VIEW mfg_part_view as
select
    mfg.name as mfg ,
    mfg_part.mfg_part_no ,
    costing_unit.unit ,
    mfg_part.descrip ,
    mfg_part.mfg_part_key
from mfg_part
right join costing_unit on
    mfg_part.unit_key = costing_unit.costing_unit_key
inner join mfg on
    mfg.mfg_key = mfg_part.mfg_key
WHERE mfg_part is NOT NULL
ORDER BY
    mfg.name ,
    mfg_part.mfg_part_no ;

Clearly that is not what I expected :-)

I need to return the name, and data type of each column for the specified
view. 

How can I do this?



-- 
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
                        -- Benjamin Franklin

When I run the following query,


SELECT column_name,data_type
FROM information_schema.columns
WHERE table_name = 'mfg_part_view';


I get the following result:

 column_name  |     data_type     
 --------------+-------------------
  mfg          | USER-DEFINED
  mfg_part_no  | character varying
  unit         | USER-DEFINED


I need to return the name, and data type of each column for the specified
view.