6

For a physical table, I have been using the following SQL:

select column_name, data_type, character_maximum_length
from INFORMATION_SCHEMA.COLUMNS
where table_name = 'a_table_name'

I found that this doesn't work for a view. Is there a way to get the schema of a view by running a SQL command (not via psql).

halfer
  • 19,824
  • 17
  • 99
  • 186
kee
  • 10,969
  • 24
  • 107
  • 168

2 Answers2

8

Postgres has dedicated System Catalog Information Functions to help with that.

To get the full view definition:

SELECT pg_get_viewdef('public.view_name');

Schema-qualification is optional. If no schema is prefixed, the current search_path setting decides visibility.

A quick hack would be to just:

SELECT * FROM public.view_name LIMIT 0;

Depending on your client, column names and types should still be displayed. Or LIMIT n to get some sample values, too. The underlying query is actually executed then (unlike with LIMIT 0).

To list columns and their data type, in order, you might base the query on pg_attribute:

SELECT attname AS column_name, format_type(atttypid, atttypmod) AS data_type
FROM   pg_attribute
WHERE  attrelid = 'public.view_name'::regclass
-- AND    NOT attisdropped
-- AND    attnum > 0
ORDER  BY attnum;

Type modifiers like maximum length are included in data_type this way.

Internally, a VIEW is implemented as special table with a rewrite rule. Details in the manual here. The table is saved in the system catalogs much like any regular table.

About the cast to regclass:

The same query works for tables or materialized views as well. Uncomment the additional filters above to only get visible user columns for tables.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
4
SELECT
    a.attname,
    t.typname,
    a.atttypmod
FROM pg_class c
INNER JOIN pg_attribute a ON a.attrelid = c.oid
INNER JOIN pg_type t ON t.oid = a.atttypid
WHERE c.relkind = 'v'
    AND c.relname = 'put_viewname_here';

ATTENTION: Since the viewname is unique only in the schema, you might also want to add an INNER JOIN to pg_namespace and add a condition to the where-clause.

For the first version of your question:

SELECT n.nspname
FROM pg_class c
INNER JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'v'
    AND c.relname = 'put_viewname_here';

ATTENTION: This might give you multiple schemas, since a viewname is only unique inside a schema and thus a viewname does not always identify one view.

Islingre
  • 2,030
  • 6
  • 18