Query to get name a data type of a view

Started by stanalmost 6 years ago2 messagesgeneral
Jump to latest
#1stan
stanb@panix.com

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

#2David G. Johnston
david.g.johnston@gmail.com
In reply to: stan (#1)
Re: Query to get name a data type of a view

On Friday, May 22, 2020, stan <stanb@panix.com> 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.