PostgreSQL ビュー定義の確認
ビュー作成後の定義
SQL
--テーブル foo とテーブル bar からビュー foobar を作成
CREATE OR REPLACE VIEW foobar AS
SELECT
FOO.*,
BAR.bar_name
FROM ( SELECT
*
FROM foo) FOO
JOIN ( SELECT
*
FROM bar) BAR ON FOO.foo_category = BAR.bar_id;
ビューの定義を確認
pgdb=# \d+ foobar
ビュー "public.foobar"
列 | 型 | 修飾語 | ストレージ | 説明
--------------+---------+--------+------------+------
foo_id | integer | | plain |
foo_name | text | | extended |
foo_category | integer | | plain |
foo_flg | boolean | | plain |
bar_name | text | | extended |
ビュー定義:
SELECT foo.foo_id,
foo.foo_name,
foo.foo_category,
foo.foo_flg,
bar.bar_name
FROM ( SELECT foo_1.foo_id,
foo_1.foo_name,
foo_1.foo_category,
foo_1.foo_flg
FROM foo foo_1) foo
JOIN ( SELECT bar_1.bar_id,
bar_1.bar_name
FROM bar bar_1) bar ON foo.foo_category = bar.bar_id;
pgdb=# SELECT definition FROM pg_views WHERE viewname = 'foobar';
definition
-----------------------------------------------------------------------
SELECT foo.foo_id, +
foo.foo_name, +
foo.foo_category, +
foo.foo_flg, +
bar.bar_name +
FROM (( SELECT foo_1.foo_id, +
foo_1.foo_name, +
foo_1.foo_category, +
foo_1.foo_flg +
FROM foo foo_1) foo +
JOIN ( SELECT bar_1.bar_id, +
bar_1.bar_name +
FROM bar bar_1) bar ON ((foo.foo_category = bar.bar_id)));
(1 行)
dumpでビューの定義を確認
$ pg_dump -U postgres -t foobar pgdb
--
-- PostgreSQL database dump
--
SET statement_timeout = 0;
SET lock_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET search_path = public, pg_catalog;
--
-- Name: foobar; Type: VIEW; Schema: public; Owner: postgres
--
CREATE VIEW foobar AS
SELECT foo.foo_id,
foo.foo_name,
foo.foo_category,
foo.foo_flg,
bar.bar_name
FROM (( SELECT foo_1.foo_id,
foo_1.foo_name,
foo_1.foo_category,
foo_1.foo_flg
FROM foo foo_1) foo
JOIN ( SELECT bar_1.bar_id,
bar_1.bar_name
FROM bar bar_1) bar ON ((foo.foo_category = bar.bar_id)));
ALTER TABLE public.foobar OWNER TO postgres;
--
-- PostgreSQL database dump complete
--
