Query to get name a data type of a view

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

Query to get name a data type of a view

stan-9
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


Reply | Threaded
Open this post in threaded view
|

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

David G Johnston
On Friday, May 22, 2020, stan <[hidden email]> wrote:
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.

Use the pg_catalog schema “tables” directly instead of the SQL standard information_schema view.  The later doesn’t provide detail of PostgreSQL-specific features by definition.

David J.