postgresql view с параметрами
Postgresql view с параметрами
CREATE VIEW — создать представление
Синтаксис
Описание
CREATE VIEW создаёт представление запроса. Создаваемое представление лишено физической материализации, поэтому указанный запрос будет выполняться при каждом обращении к представлению.
Команда CREATE OR REPLACE VIEW действует подобным образом, но если представление с этим именем уже существует, оно заменяется. Новый запрос должен выдавать те же столбцы, что выдавал запрос, ранее определённый для этого представления (то есть, столбцы с такими же именами должны иметь те же типы данных и следовать в том же порядке), но может добавить несколько новых столбцов в конце списка. Вычисления, в результате которых формируются столбцы представления, могут быть совершенно другими.
Параметры
С таким указанием представление создаётся как временное. Временные представления автоматически удаляются в конце сеанса. Существующее постоянное представление с тем же именем не будет видно в текущем сеансе, пока существует временное, однако к нему можно обратиться, дополнив имя указанием схемы.
Если в определении представления задействованы временные таблицы, представление так же создаётся как временное (вне зависимости от присутствия явного указания TEMPORARY ). RECURSIVE
Создаёт рекурсивное представление. Синтаксис
Для рекурсивного представления обязательно должен задаваться список с именами столбцов. имя
Имя создаваемого представления (возможно, дополненное схемой). имя_столбца
В этом предложении могут задаваться следующие необязательные параметры представления:
Этот параметр может принимать значение local (локально) или cascaded (каскадно) и равнозначен указанию WITH [ CASCADED | LOCAL ] CHECK OPTION (см. ниже). Изменить этот параметр у существующего представления с помощью ALTER VIEW нельзя. security_barrier ( boolean )
Этот параметр следует использовать, если представление должно обеспечивать защиту на уровне строк. За дополнительными подробностями обратитесь к Разделу 38.5.
Это указание управляет поведением автоматически изменяемых представлений. Если оно присутствует, при выполнении операций INSERT и UPDATE с этим представлением будет проверяться, удовлетворяют ли новые строки условию, определяющему представление (то есть, проверяется, будут ли новые строки видны через это представление). Если они не удовлетворяют условию, операция не будет выполнена. Если указание CHECK OPTION отсутствует, команды INSERT и UPDATE смогут создавать в этом представлении строки, которые не будут видны в нём. Поддерживаются следующие варианты проверки:
Новые строки проверяются только по условиям, определённым непосредственно в самом представлении. Любые условия, определённые в нижележащих базовых представлениях, не проверяются (если только в них нет указания CHECK OPTION ). CASCADED
Указание CHECK OPTION нельзя использовать с рекурсивными представлениями.
Замечания
Позаботьтесь о том, чтобы столбцы представления получили желаемые имена и типы. Например, такая команда:
Доступ к таблицам, задействованным в представлении, определяется правами владельца представления. В некоторых случаях это позволяет организовать безопасный, но ограниченный доступ к нижележащим таблицам. Однако учтите, что не все представления могут быть защищёнными; за подробностями обратитесь к Разделу 38.5. Функции, вызываемые в представлении, выполняются так, как будто они вызываются непосредственно из запроса, обращающегося к представлению. Поэтому пользователь представления должен иметь все права, необходимые для вызова всех функций, задействованных в представлении.
При выполнении CREATE OR REPLACE VIEW для существующего представления меняется только правило SELECT, определяющее представление. Другие свойства представления, включая владельца, права и правила, кроме SELECT, остаются неизменными. Чтобы изменить определение представления, необходимо быть его владельцем (или членом роли-владельца).
Изменяемые представления
Список FROM в запросе, определяющем представлении, должен содержать ровно один элемент, и это должна быть таблица или другое изменяемое представление.
Список выборки в запросе не должен содержать агрегатные и оконные функции, а также функции, возвращающие множества.
Автоматически обновляемое представление может содержать как изменяемые, так и не изменяемые столбцы. Столбец будет изменяемым, если это простая ссылка на изменяемый столбец нижележащего базового отношения; в противном случае этот столбец будет доступен только для чтения, и если команда INSERT или UPDATE попытается записать значение в него, возникнет ошибка.
Учтите, что пользователь, выполняющий операции добавления, изменения или удаления данных в представлении, должен иметь соответствующие права для этого представления. Кроме того, владелец представления должен иметь сопутствующие права в нижележащих базовых отношениях, хотя пользователь, собственно выполняющий эти операции, может этих прав не иметь (см. Раздел 38.5).
Примеры
Создание представления, содержащего все комедийные фильмы:
Создание представления с указанием LOCAL CHECK OPTION :
Создание представления с указанием CASCADED CHECK OPTION :
Создание представления с изменяемыми и неизменяемыми столбцами:
Создание рекурсивного представления, содержащего числа от 1 до 100:
Совместимость
Использование функций в PostgreSQL как параметризированных представлений
который достаточно ясно воспринимается как «берем активных пользователей и получаем по ним детальную информацию» и этот же запрос, но, так сказать, в развернутом виде:
Запросы подобного вида — с большим списком получаемых колонок и выражений на их основе, со сложными условиями и которые в реальной жизни нередко отягчены историческими напластованиями — зачастую совершенно нечитаемы и малопонятны. Наверное, стоит заметить, что само изменение понятия «активный» (например, убрать или добавить удаленных работников или сотрудников в декретном отпуске и т.п.) может стать не то чтобы нетривиальным, но очень утомительным занятием; да и на количестве ошибок оно вряд ли скажется достаточно благоприятно; и изменение списка колонок или просто выражения влечет за собой схожие последствия. Пожалуй, можно сказать, что если для таблиц выражение select * from table строго неприемлемо, то для представлений подобного вида оно, наверное, даже предпочтительно. Ну для некоторых, по крайней мере.
Рассмотрим другую задачу. Пусть у нас есть простая таблица пользователей:
Требуется:
Получить определенного пользователя со списком друзей.
Так как эта операция требуется достаточно часто, создаем для нее представление:
Все хорошо, но появилось новое требование: получить пользователя со списком друзей, которые одновременно являются друзьями другого пользователя (например, просматривающего):
К сожалению, создать представление на основе этого запроса невозможно — передать идентификатор второго пользователя как параметр нельзя; но есть возможность обойти это ограничение с помощью декартова произведения:
Использование получившегося представления совершенно естественно:
Поступает новое требование: требуется получать не просто общих друзей, но общих друзей, зарегистрировавшихся в указанный промежуток времени. Так как создать таблицу со всеми возможными временными промежутками не представляется возможным, то придется создать функцию:
Использование тоже достаточно удобно:
Казалось бы, запрос, использующий эту фунцию, будет работать незамысловато — сначала функция вернет все возможные строки, а потом они будут отфильтрованы по условию. Давайте посмотрим:
Удивительно, но это не так — сервер сумел развернуть функцию непосредственно в тело запроса. Да, Postgresql в ряде случаев умеет внедрять тело функции непосредственно в запрос.
В каких случаях это происходит?
Это может пригодиться для инкапсуляции несложной, но громоздкой логики, например:
Как видно, никакого вызова функции тут нет — код функции вставился непосредственно в тело запроса. Эту функцию можно рассматривать как своеобразный макрос.
У табличных функций похожие, но значительно более мягкие ограничения:
Таким образом, реализованное в Postgres встраивание тела функции непосредственно в запрос дает возможность эффективно реализовать отсутствующую в стандарте, но тем не менее востребованную и удобную конструкцию «представление с параметрами».
Интересно, что в DB2 и SQL Server для решения задачи «представление с параметрами» также используются функции, встраиваемые в запрос.
Postgresql view с параметрами
CREATE VIEW — создать представление
Синтаксис
Описание
CREATE VIEW создаёт представление запроса. Создаваемое представление лишено физической материализации, поэтому указанный запрос будет выполняться при каждом обращении к представлению.
Команда CREATE OR REPLACE VIEW действует подобным образом, но если представление с этим именем уже существует, оно заменяется. Новый запрос должен выдавать те же столбцы, что выдавал запрос, ранее определённый для этого представления (то есть, столбцы с такими же именами должны иметь те же типы данных и следовать в том же порядке), но может добавить несколько новых столбцов в конце списка. Вычисления, в результате которых формируются столбцы представления, могут быть совершенно другими.
Параметры
С таким указанием представление создаётся как временное. Временные представления автоматически удаляются в конце сеанса. Существующее постоянное представление с тем же именем не будет видно в текущем сеансе, пока существует временное, однако к нему можно обратиться, дополнив имя указанием схемы.
Если в определении представления задействованы временные таблицы, представление так же создаётся как временное (вне зависимости от присутствия явного указания TEMPORARY ). RECURSIVE
Создаёт рекурсивное представление. Синтаксис
Для рекурсивного представления обязательно должен задаваться список с именами столбцов. имя
Имя создаваемого представления (возможно, дополненное схемой). имя_столбца
В этом предложении могут задаваться следующие необязательные параметры представления:
Этот параметр может принимать значение local (локально) или cascaded (каскадно) и равнозначен указанию WITH [ CASCADED | LOCAL ] CHECK OPTION (см. ниже). Изменить этот параметр у существующего представления с помощью ALTER VIEW нельзя. security_barrier ( boolean )
Этот параметр следует использовать, если представление должно обеспечивать защиту на уровне строк. За дополнительными подробностями обратитесь к Разделу 37.5.
Это указание управляет поведением автоматически изменяемых представлений. Если оно присутствует, при выполнении операций INSERT и UPDATE с этим представлением будет проверяться, удовлетворяют ли новые строки условию, определяющему представление (то есть, проверяется, будут ли новые строки видны через это представление). Если они не удовлетворяют условию, операция не будет выполнена. Если указание CHECK OPTION отсутствует, команды INSERT и UPDATE смогут создавать в этом представлении строки, которые не будут видны в нём. Поддерживаются следующие варианты проверки:
Новые строки проверяются только по условиям, определённым непосредственно в самом представлении. Любые условия, определённые в нижележащих базовых представлениях, не проверяются (если только в них нет указания CHECK OPTION ). CASCADED
Указание CHECK OPTION нельзя использовать с рекурсивными представлениями.
Замечания
Будьте аккуратны в определении представления, чтобы получить желаемые имена и типы столбцов. Например, такая команда:
Доступ к таблицам, задействованным в представлении, определяется правами владельца представления. В некоторых случаях это позволяет организовать безопасный, но ограниченный доступ к нижележащим таблицам. Однако учтите, что не все представления могут быть защищёнными; за подробностями обратитесь к Разделу 37.5. Функции, вызываемые в представлении, выполняются так, как будто они вызываются непосредственно из запроса, обращающегося к представлению. Поэтому пользователь представления должен иметь все права, необходимые для вызова всех функций, задействованных в представлении.
При выполнении CREATE OR REPLACE VIEW для существующего представления меняется только правило SELECT, определяющее представление. Другие свойства представления, включая владельца, права и правила, кроме SELECT, остаются неизменными. Чтобы изменить определение представления, необходимо быть его владельцем (или членом роли-владельца).
Изменяемые представления
Список FROM в запросе, определяющем представлении, должен содержать ровно один элемент, и это должна быть таблица или другое изменяемое представление.
Список выборки в запросе не должен содержать агрегатные и оконные функции, а также функции, возвращающие множества.
Автоматически обновляемое представление может содержать как изменяемые, так и не изменяемые столбцы. Столбец будет изменяемым, если это простая ссылка на изменяемый столбец нижележащего базового отношения; в противном случае этот столбец будет доступен только для чтения, и если команда INSERT или UPDATE попытается записать значение в него, возникнет ошибка.
Учтите, что пользователь, выполняющий операции добавления, изменения или удаления данных в представлении, должен иметь соответствующие права для этого представления. Кроме того, владелец представления должен иметь сопутствующие права в нижележащих базовых отношениях, хотя пользователь, собственно выполняющий эти операции, может этих прав не иметь (см. Раздел 37.5).
Примеры
Создание представления, содержащего все комедийные фильмы:
Создание представления с указанием LOCAL CHECK OPTION :
Создание представления с указанием CASCADED CHECK OPTION :
Создание представления с изменяемыми и неизменяемыми столбцами:
Создание рекурсивного представления, содержащего числа от 1 до 100:
Совместимость
Postgresql view с параметрами
CREATE VIEW — создать представление
Синтаксис
Описание
CREATE VIEW создаёт представление запроса. Создаваемое представление лишено физической материализации, поэтому указанный запрос будет выполняться при каждом обращении к представлению.
Команда CREATE OR REPLACE VIEW действует подобным образом, но если представление с этим именем уже существует, оно заменяется. Новый запрос должен выдавать те же столбцы, что выдавал запрос, ранее определённый для этого представления (то есть, столбцы с такими же именами должны иметь те же типы данных и следовать в том же порядке), но может добавить несколько новых столбцов в конце списка. Вычисления, в результате которых формируются столбцы представления, могут быть совершенно другими.
Параметры
С таким указанием представление создаётся как временное. Временные представления автоматически удаляются в конце сеанса. Существующее постоянное представление с тем же именем не будет видно в текущем сеансе, пока существует временное, однако к нему можно обратиться, дополнив имя указанием схемы.
Если в определении представления задействованы временные таблицы, представление так же создаётся как временное (вне зависимости от присутствия явного указания TEMPORARY ). RECURSIVE
Создаёт рекурсивное представление. Синтаксис
Для рекурсивного представления обязательно должен задаваться список с именами столбцов. имя
Имя создаваемого представления (возможно, дополненное схемой). имя_столбца
В этом предложении могут задаваться следующие необязательные параметры представления:
Этот параметр может принимать значение local (локально) или cascaded (каскадно) и равнозначен указанию WITH [ CASCADED | LOCAL ] CHECK OPTION (см. ниже). Изменить этот параметр у существующего представления с помощью ALTER VIEW нельзя. security_barrier ( boolean )
Этот параметр следует использовать, если представление должно обеспечивать защиту на уровне строк. За дополнительными подробностями обратитесь к Разделу 40.5.
Это указание управляет поведением автоматически изменяемых представлений. Если оно присутствует, при выполнении операций INSERT и UPDATE с этим представлением будет проверяться, удовлетворяют ли новые строки условию, определяющему представление (то есть, проверяется, будут ли новые строки видны через это представление). Если они не удовлетворяют условию, операция не будет выполнена. Если указание CHECK OPTION отсутствует, команды INSERT и UPDATE смогут создавать в этом представлении строки, которые не будут видны в нём. Поддерживаются следующие варианты проверки:
Новые строки проверяются только по условиям, определённым непосредственно в самом представлении. Любые условия, определённые в нижележащих базовых представлениях, не проверяются (если только в них нет указания CHECK OPTION ). CASCADED
Указание CHECK OPTION нельзя использовать с рекурсивными представлениями.
Замечания
Позаботьтесь о том, чтобы столбцы представления получили желаемые имена и типы. Например, такая команда:
Доступ к таблицам, задействованным в представлении, определяется правами владельца представления. В некоторых случаях это позволяет организовать безопасный, но ограниченный доступ к нижележащим таблицам. Однако учтите, что не все представления могут быть защищёнными; за подробностями обратитесь к Разделу 40.5. Функции, вызываемые в представлении, выполняются так, как будто они вызываются непосредственно из запроса, обращающегося к представлению. Поэтому пользователь представления должен иметь все права, необходимые для вызова всех функций, задействованных в представлении.
При выполнении CREATE OR REPLACE VIEW для существующего представления меняется только правило SELECT, определяющее представление. Другие свойства представления, включая владельца, права и правила, кроме SELECT, остаются неизменными. Чтобы изменить определение представления, необходимо быть его владельцем (или членом роли-владельца).
Изменяемые представления
Список FROM в запросе, определяющем представлении, должен содержать ровно один элемент, и это должна быть таблица или другое изменяемое представление.
Список выборки в запросе не должен содержать агрегатные и оконные функции, а также функции, возвращающие множества.
Автоматически обновляемое представление может содержать как изменяемые, так и не изменяемые столбцы. Столбец будет изменяемым, если это простая ссылка на изменяемый столбец нижележащего базового отношения; в противном случае этот столбец будет доступен только для чтения, и если команда INSERT или UPDATE попытается записать значение в него, возникнет ошибка.
Учтите, что пользователь, выполняющий операции добавления, изменения или удаления данных в представлении, должен иметь соответствующие права для этого представления. Кроме того, владелец представления должен иметь сопутствующие права в нижележащих базовых отношениях, хотя пользователь, собственно выполняющий эти операции, может этих прав не иметь (см. Раздел 40.5).
Примеры
Создание представления, содержащего все комедийные фильмы:
Создание представления с указанием LOCAL CHECK OPTION :
Создание представления с указанием CASCADED CHECK OPTION :
Создание представления с изменяемыми и неизменяемыми столбцами:
Создание рекурсивного представления, содержащего числа от 1 до 100:
Совместимость
Postgresql view с параметрами
CREATE VIEW — создать представление
Синтаксис
Описание
CREATE VIEW создаёт представление запроса. Создаваемое представление лишено физической материализации, поэтому указанный запрос будет выполняться при каждом обращении к представлению.
Команда CREATE OR REPLACE VIEW действует подобным образом, но если представление с этим именем уже существует, оно заменяется. Новый запрос должен выдавать те же столбцы, что выдавал запрос, ранее определённый для этого представления (то есть, столбцы с такими же именами должны иметь те же типы данных и следовать в том же порядке), но может добавить несколько новых столбцов в конце списка. Вычисления, в результате которых формируются столбцы представления, могут быть совершенно другими.
Параметры
С таким указанием представление создаётся как временное. Временные представления автоматически удаляются в конце сеанса. Существующее постоянное представление с тем же именем не будет видно в текущем сеансе, пока существует временное, однако к нему можно обратиться, дополнив имя указанием схемы.
Если в определении представления задействованы временные таблицы, представление так же создаётся как временное (вне зависимости от присутствия явного указания TEMPORARY ). RECURSIVE
Создаёт рекурсивное представление. Синтаксис
Для рекурсивного представления обязательно должен задаваться список с именами столбцов. имя
Имя создаваемого представления (возможно, дополненное схемой). имя_столбца
В этом предложении могут задаваться следующие необязательные параметры представления:
Этот параметр может принимать значение local (локально) или cascaded (каскадно) и равнозначен указанию WITH [ CASCADED | LOCAL ] CHECK OPTION (см. ниже). Изменить этот параметр у существующего представления с помощью ALTER VIEW нельзя. security_barrier ( boolean )
Этот параметр следует использовать, если представление должно обеспечивать защиту на уровне строк. За дополнительными подробностями обратитесь к Разделу 40.5.
Это указание управляет поведением автоматически изменяемых представлений. Если оно присутствует, при выполнении операций INSERT и UPDATE с этим представлением будет проверяться, удовлетворяют ли новые строки условию, определяющему представление (то есть, проверяется, будут ли новые строки видны через это представление). Если они не удовлетворяют условию, операция не будет выполнена. Если указание CHECK OPTION отсутствует, команды INSERT и UPDATE смогут создавать в этом представлении строки, которые не будут видны в нём. Поддерживаются следующие варианты проверки:
Новые строки проверяются только по условиям, определённым непосредственно в самом представлении. Любые условия, определённые в нижележащих базовых представлениях, не проверяются (если только в них нет указания CHECK OPTION ). CASCADED
Указание CHECK OPTION нельзя использовать с рекурсивными представлениями.
Замечания
Позаботьтесь о том, чтобы столбцы представления получили желаемые имена и типы. Например, такая команда:
Доступ к таблицам, задействованным в представлении, определяется правами владельца представления. В некоторых случаях это позволяет организовать безопасный, но ограниченный доступ к нижележащим таблицам. Однако учтите, что не все представления могут быть защищёнными; за подробностями обратитесь к Разделу 40.5. Функции, вызываемые в представлении, выполняются так, как будто они вызываются непосредственно из запроса, обращающегося к представлению. Поэтому пользователь представления должен иметь все права, необходимые для вызова всех функций, задействованных в представлении.
При выполнении CREATE OR REPLACE VIEW для существующего представления меняется только правило SELECT, определяющее представление. Другие свойства представления, включая владельца, права и правила, кроме SELECT, остаются неизменными. Чтобы изменить определение представления, необходимо быть его владельцем (или членом роли-владельца).
Изменяемые представления
Список FROM в запросе, определяющем представлении, должен содержать ровно один элемент, и это должна быть таблица или другое изменяемое представление.
Список выборки в запросе не должен содержать агрегатные и оконные функции, а также функции, возвращающие множества.
Автоматически обновляемое представление может содержать как изменяемые, так и не изменяемые столбцы. Столбец будет изменяемым, если это простая ссылка на изменяемый столбец нижележащего базового отношения; в противном случае этот столбец будет доступен только для чтения, и если команда INSERT или UPDATE попытается записать значение в него, возникнет ошибка.
Учтите, что пользователь, выполняющий операции добавления, изменения или удаления данных в представлении, должен иметь соответствующие права для этого представления. Кроме того, владелец представления должен иметь сопутствующие права в нижележащих базовых отношениях, хотя пользователь, собственно выполняющий эти операции, может этих прав не иметь (см. Раздел 40.5).
Примеры
Создание представления, содержащего все комедийные фильмы:
Создание представления с указанием LOCAL CHECK OPTION :
Создание представления с указанием CASCADED CHECK OPTION :
Создание представления с изменяемыми и неизменяемыми столбцами:
Создание рекурсивного представления, содержащего числа от 1 до 100: