Query to get name a data type of a view

From: stan <stanb(at)panix(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Query to get name a data type of a view
Date: 2020-05-22 15:11:15
Message-ID: 20200522151115.GA18237@panix.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Nico De Ranter 2020-05-22 15:13:07 Re: pg_dump crashes
Previous Message Nico De Ranter 2020-05-22 15:11:14 Re: pg_dump crashes