Предопределённые константы pgsql


Содержание

Константы в PHP

Константы по принципу работы похожи на переменные, но имеют одно важное отличие — записать какое-либо значение в константу можно только 1 раз, перезаписать это значение уже не получится.

Создать константу можно с помощью функции define() :

Первым значением мы передаём название константы (в нашем случае LOGIN ), вторым — значение 12345 .

Как вы заметили, у константы нет значка $ в начале. Чтобы не путаться, PHP разработчики взяли за правило всегда писать константы большими буквами, а слова разделять нижним подчёркиванием _ :

Константы используются не так часто, в основном для хранения логинов, паролей и прочих подобных конфигурационных настроек.

Строковые константы в PHP

Бывают ситуации, когда нам нужно получить значение константы, но само имя константы лежит в переменной. В этом случае мы можем получить значение константы с помощью функции constant() :

Встроенные или магические константы

В PHP существует несколько встроенных констант. Магическими они называются потому что их значение меняется в зависимости от контекста:

  • __LINE__ — текущая строка в скрипте
  • __FILE__ — полный путь и имя текущего скрипта
  • __DIR__ — полный путь к директории, в которой расположен скрипт

Есть и другие константы, но их назначение вы поймёте немного позже:

  • __FUNCTION__ — имя текущей функции
  • __CLASS__ — имя текущего класса с пространством имён
  • __TRAIT__ — имя текущего трейта с пространством имён
  • __METHOD__ — имя текущего метода
  • __NAMESPACE__ — имя текущего пространства имён
  • ClassName::class — имя классу, к которому мы обращаемся, с пространством имён

Напишите скрипт, который:
1. Создаёт константу PASSWORD со значением ioef84Fe# .
2. Выводит значение константы внутри тега div.

LXXXI. PostgreSQL functions

PostgreSQL database is Open Source product and available without cost. Postgres, developed originally in the UC Berkeley Computer Science Department, pioneered many of the object-relational concepts now becoming available in some commercial databases. It provides SQL92/SQL99 language support, transaction integrity and type extensibility. PostgreSQL is an open source descendant of this original Berkeley code.

To use PostgreSQL support, you need PostgreSQL 6.5 or later, PostgreSQL 7.0 or later to enable all PostgreSQL module features. PostgreSQL supports many character encoding including multibyte character encoding. The current version and more information about PostgreSQL is available at http://www.postgresql.org/ .

In order to enable PostgreSQL support, —with-pgsql[=DIR] is required when you compile PHP. DIR is the PostgreSQL base install directory, defaults to /usr/local/pgsql . If shared object module is available, PostgreSQL module may be loaded using extension directive in php.ini or dl() function.

Поведение этих функций находится в зависимости от установок в php.ini .

Таблица 1. PostgreSQL configuration options

Name Default Changeable
pgsql.allow_persistent «1» PHP_INI_SYSTEM
pgsql.max_persistent «-1» PHP_INI_SYSTEM
pgsql.max_links «-1» PHP_INI_SYSTEM
pgsql.auto_reset_persistent «0» PHP_INI_SYSTEM
pgsql.ignore_notice «0» PHP_INI_ALL
pgsql.log_notice «0» PHP_INI_ALL

Here is a short explanation of the configuration directives.

Whether to allow persistent Postgres connections.

The maximum number of persistent Postgres connections per process.

The maximum number of Postgres connections per process, including persistent connections.

Using the PostgreSQL module with PHP 4.0.6 is not recommended due to a bug in the notice message handling code. Use 4.1.0 or later.

Внимание

PostgreSQL function names will be changed in 4.2.0 release to confirm to current coding standards. Most of new names will have additional underscores, e.g. pg_lo_open(). Some functions are renamed to different name for consistency. e.g. pg_exec() to pg_query(). Older names can be used in 4.2.0 and a few releases from 4.2.0, but they may be deleted in the future.

Таблица 2. Function names changed

Внимание
Old name New name
pg_exec() pg_query()
pg_getlastoid() pg_last_oid()
pg_cmdtuples() pg_affected_rows()
pg_numrows() pg_num_rows()
pg_numfields() pg_num_fields()
pg_fieldname() pg_field_name()
pg_fieldsize() pg_field_size()
pg_fieldnum() pg_field_num()
pg_fieldprtlen() pg_field_prtlen()
pg_fieldisnull() pg_field_is_null()
pg_freeresult() pg_free_result()
pg_result() pg_fetch_result()
pg_loreadall() pg_lo_read_all()
pg_locreate() pg_lo_create()
pg_lounlink() pg_lo_unlink()
pg_loopen() pg_lo_open()
pg_loclose() pg_lo_close()
pg_loread() pg_lo_read()
pg_lowrite() pg_lo_write()
pg_loimport() pg_lo_import()
pg_loexport() pg_lo_export()

Not all functions are supported by all builds. It depends on your libpq (The PostgreSQL C Client interface) version and how libpq is compiled. If there is missing function, libpq does not support the feature required for the function.

It is also important that you do not use an older libpq than the PostgreSQL Server to which you will be connecting. If you use libpq older than PostgreSQL Server expects, you may have problems.

Since version 6.3 (03/02/1998) PostgreSQL uses unix domain sockets by default. TCP port will NOT be opened by default. A table is shown below describing these new connection possibilities. This socket will be found in /tmp/.s.PGSQL.5432 . This option can be enabled with the ‘-i’ flag to postmaster and it’s meaning is: «listen on TCP/IP sockets as well as Unix domain sockets».

Таблица 3. Postmaster and PHP

Postmaster PHP Status
postmaster & pg_connect(«dbname=MyDbName»); OK
postmaster -i & pg_connect(«dbname=MyDbName»); OK
postmaster & pg_connect(«host=localhost dbname=MyDbName»); Unable to connect to PostgreSQL server: connectDB() failed: Is the postmaster running and accepting TCP/IP (with -i) connection at ‘localhost’ on port ‘5432’? in /path/to/file.php on line 20.
postmaster -i & pg_connect(«host=localhost dbname=MyDbName»); OK

A connection to PostgreSQL server can be established with the following value pairs set in the command string: $conn = pg_connect(«host=myHost port=myPort tty=myTTY options=myOptions dbname=myDB user=myUser password=myPassword «);

The previous syntax of: $conn = pg_connect («host», «port», «options», «tty», «dbname») has been deprecated.

Environmental variables affect PostgreSQL server/client behavior. For example, PostgreSQL module will lookup PGHOST environment variable when the hostname is omitted in the connection string. Supported environment variables are different from version to version. Refer to PostgreSQL Programmer’s Manual (libpq — Environment Variables) for details.

Make sure you set environment variables for appropriate user. Use $_ENV or getenv() to check which environment variables are available to the current process.

Пример 1. Setting default parameters

PGHOST=pgsql.example.com PGPORT=7890 PGDATABASE=web-system PGUSER=web-user PGPASSWORD=secret PGDATESTYLE=ISO PGTZ=JST PGCLIENTENCODING=EUC-JP export PGHOST PGPORT PGDATABASE PGUSER PGPASSWORD PGDATESTYLE PGTZ PGCLIENTENCODING

Перечисленные ниже константы определены данным расширением и могут быть доступны только в том случае, если PHP был собран с поддержкой этого расширения или же в том случае, если данное расширение подгружается во время выполнения.

PostgreSQL Functions

Руководство по PHP
Пред. След.

CXV. PostgreSQL Functions

PostgreSQL database is Open Source product and available without cost. Postgres, developed originally in the UC Berkeley Computer Science Department, pioneered many of the object-relational concepts now becoming available in some commercial databases. It provides SQL92/SQL99 language support, transactions, referential integrity, stored procedures and type extensibility. PostgreSQL is an open source descendant of this original Berkeley code.

In order to enable PostgreSQL support, —with-pgsql[=DIR] is required when you compile PHP. DIR is the PostgreSQL base install directory, defaults to /usr/local/pgsql . If shared object module is available, PostgreSQL module may be loaded using extension directive in php.ini or dl() function.

Поведение этих функций зависит от установок в php.ini .

Таблица 1. PostgreSQL configuration options

Name Default Changeable Changelog
pgsql.allow_persistent «1» PHP_INI_SYSTEM
pgsql.max_persistent «-1» PHP_INI_SYSTEM
pgsql.max_links «-1» PHP_INI_SYSTEM
pgsql.auto_reset_persistent «0» PHP_INI_SYSTEM Available since PHP 4.2.0.
pgsql.ignore_notice «0» PHP_INI_ALL Available since PHP 4.3.0.
pgsql.log_notice «0» PHP_INI_ALL Available since PHP 4.3.0.

Краткое разъяснение конфигурационных директив.

Whether to allow persistent Postgres connections.

The maximum number of persistent Postgres connections per process.

The maximum number of Postgres connections per process, including persistent connections.

Detect broken persistent links with pg_pconnect() . Needs a little overhead.

Whether or not to ignore PostgreSQL backend notices.

Whether or not to log PostgreSQL backends notice messages. The PHP directive pgsql.ignore_notice must be off in order to log notice messages.

There are two resource types used in the PostgreSQL module. The first one is the link identifier for a database connection, the second a resource which holds the result of a query.

Перечисленные ниже константы определены данным расширением и могут быть доступны только в том случае, если PHP был собран с поддержкой этого расширения или же в том случае, если данное расширение подгружается во время выполнения.

Passed to pg_fetch_array() . Return an associative array of field names and values.


Passed to pg_fetch_array() . Return a numerically indexed array of field numbers and values.

Passed to pg_fetch_array() . Return an array of field values that is both numerically indexed (by field number) and associated (by field name).

Passed to pg_connect() to force the creation of a new connection, rather then re-using an existing identical connection.

Returned by pg_connection_status() indicating that the database connection is in an invalid state.

Returned by pg_connection_status() indicating that the database connection is in a valid state.

Passed to pg_lo_seek() . Seek operation is to begin from the start of the object.

Passed to pg_lo_seek() . Seek operation is to begin from the current position.

Passed to pg_lo_seek() . Seek operation is to begin from the end of the object.

Returned by pg_result_status() . The string sent to the server was empty.

Returned by pg_result_status() . Successful completion of a command returning no data.

Returned by pg_result_status() . Successful completion of a command returning data (such as a SELECT or SHOW ).

Returned by pg_result_status() . Copy Out (from server) data transfer started.

Returned by pg_result_status() . Copy In (to server) data transfer started.

Returned by pg_result_status() . The server’s response was not understood.

Returned by pg_result_status() . A nonfatal error (a notice or warning) occurred.

Returned by pg_result_status() . A fatal error occurred.

Returned by pg_transaction_status() . Connection is currently idle, not in a transaction.

Returned by pg_transaction_status() . A command is in progress on the connection. A query has been sent via the connection and not yet completed.

Returned by pg_transaction_status() . The connection is idle, in a transaction block.

Returned by pg_transaction_status() . The connection is idle, in a failed transaction block.

Returned by pg_transaction_status() . The connection is bad.

Passed to pg_result_error_field() . The severity; the field contents are ERROR , FATAL , or PANIC (in an error message), or WARNING , NOTICE , DEBUG , INFO , or LOG (in a notice message), or a localized translation of one of these. Always present.

Passed to pg_result_error_field() . The SQLSTATE code for the error. The SQLSTATE code identifies the type of error that has occurred; it can be used by front-end applications to perform specific operations (such as error handling) in response to a particular database error. This field is not localizable, and is always present.

Passed to pg_result_error_field() . The primary human-readable error message (typically one line). Always present.

Passed to pg_result_error_field() . Detail: an optional secondary error message carrying more detail about the problem. May run to multiple lines.

Passed to pg_result_error_field() . Hint: an optional suggestion what to do about the problem. This is intended to differ from detail in that it offers advice (potentially inappropriate) rather than hard facts. May run to multiple lines.

Passed to pg_result_error_field() . A string containing a decimal integer indicating an error cursor position as an index into the original statement string. The first character has index 1, and positions are measured in characters not bytes.

Passed to pg_result_error_field() . This is defined the same as the PG_DIAG_STATEMENT_POSITION field, but it is used when the cursor position refers to an internally generated command rather than the one submitted by the client. The PG_DIAG_INTERNAL_QUERY field will always appear when this field appears.

Passed to pg_result_error_field() . The text of a failed internally-generated command. This could be, for example, a SQL query issued by a PL/pgSQL function.

Passed to pg_result_error_field() . An indication of the context in which the error occurred. Presently this includes a call stack traceback of active procedural language functions and internally-generated queries. The trace is one entry per line, most recent first.

Passed to pg_result_error_field() . The file name of the PostgreSQL source-code location where the error was reported.

Passed to pg_result_error_field() . The line number of the PostgreSQL source-code location where the error was reported.

Passed to pg_result_error_field() . The name of the PostgreSQL source-code function reporting the error.

Passed to pg_set_error_verbosity() . Specified that returned messages include severity, primary text, and position only; this will normally fit on a single line.

Passed to pg_set_error_verbosity() . The default mode produces messages that include the above plus any detail, hint, or context fields (these may span multiple lines).

Passed to pg_set_error_verbosity() . The verbose mode includes all available fields.

Passed to pg_result_status() . Indicates that numerical result code is desired.

Passed to pg_result_status() . Indicates that textual result command tag is desired.

Passed to pg_convert() . Ignore default values in the table during conversion.

Passed to pg_convert() . Use SQL NULL in place of an empty string .

Passed to pg_convert() . Ignore conversion of NULL into SQL NOT NULL columns.

Замечание: Not all functions are supported by all builds. It depends on your libpq (The PostgreSQL C client library) version and how libpq is compiled. If PHP PostgreSQL extensions are missing, then it is because your libpq version does not support them.

Замечание: Most PostgreSQL functions accept connection as the first optional parameter. If it is not prov > FALSE .

Замечание: PostgreSQL automatically folds all identifiers (e.g. table/column names) to lower-case values at object creation time and at query time. To force the use of mixed or upper case identifiers, you must escape the identifier using double quotes («»).

This simple example shows how to connect, execute a query, print resulting rows and disconnect from a PostgreSQL database.

Пример 1. PostgreSQL extension overview example

// Connecting, selecting database
$dbconn = pg_connect ( «host=localhost dbname=publishing user=www password=foo» )
or die( ‘Could not connect: ‘ . pg_last_error ());

// Performing SQL query
$query = ‘SELECT * FROM authors’ ;
$result = pg_query ( $query ) or die( ‘Query failed: ‘ . pg_last_error ());

// Printing results in HTML
echo »

\n» ;
while ( $line = pg_fetch_array ( $result , null , PGSQL_ASSOC )) <
echo «\t \n» ;
foreach ( $line as $col_value ) <
echo «\t\t

\n» ;
>
echo «\t

\n» ;
>
echo «

$col_value

\n» ;

// Free resultset
pg_free_result ( $result );

Иллюстрированный самоучитель по PostgreSQL

Константы

При работе с базами данных многие объекты хранятся на диске, а для обращения к ним используются идентификаторы (имена таблиц, полей и функций). Однако неизбежно настанет момент, когда в систему потребуется передать новые данные – например, при вставке новых записей, при формировании секций с критериями удаления или модификации или при вычислениях на базе существующих записей. Такие данные передаются в виде констант, также иногда называемых «литералами». Константы предназначены для «буквального» представления данных в командах SQL (вместо ссылки на них по идентификатору).

Константы с косвенной типизацией автоматически распознаются модулем лексического разбора PostgreSQL по их синтаксису. В PostgreSQL поддерживаются пять вариантов констант с косвенной типизацией:

  • строковые константы;
  • битовые последовательности;
  • целочисленные константы;
  • вещественные константы;
  • логические константы.

Строковые константы

Строковая константа представляет собой произвольную последовательность символов, заключенную в апострофы. Строковые константы часто используются при вставке новых данных в таблицу и при передаче символьной информации другим объектам базы данных. Ниже приведен пример использования строковых констант при обновлении имен и фамилий таблицы authors базы данных booktown:

Из результатов запроса видно, что поле firstjiame с кодом >

Листинг 3.4. Использование строковых констант.

Команда UPDATE в листинге 3.4 использует строковые константы Louisa May и Luoisa May в сочетании с ключевыми словами SET и WHERE. Как видно из результатов запроса, команда обновляет содержимое таблицы, заданной идентификатором authors, и исправляет опечатку.

Тот факт, что строковые константы заключаются между апострофами, порождает очевидную семантическую проблему: если в самой последовательности символов встречается апостроф, граница строковой константы будет определена неверно. Чтобы экранировать апостроф в строке (то есть обеспечить его интерпретацию как литерала), следует поставить два апострофа подряд. Модуль лексического анализа воспринимает удвоенный апостроф в строковой константе как один апостроф-литерал. PostgreSQL также позволяет экранировать апострофы обратной косой чертой, в стиле языка С:


В PostgreSQL также поддерживаются служебные последовательности языка С, перечисленные в табл. 3.3.

Is there a way to define a named constant in a PostgreSQL query?

Is there a way to define a named constant in a PostgreSQL query? For example:

5 Answers 5

This question has been asked before (How do you use script variables in PostgreSQL?). However, there is a trick that I use for queries sometimes:

That is, I define a CTE called const that has the constants defined there. I can then cross join this into my query, any number of times at any level. I have found this particularly useful when I’m dealing with dates, and need to handle date constants across many subqueries.

PostgreSQL has no built-in way to define (global) variables like MySQL or Oracle. (There is a limited workaround using «customized options»). Depending on what you want exactly there are other ways:

For one query

You can provide values at the top of a query in a CTE like @Gordon already provided.

Global, persistent constant:

You could create a simple IMMUTABLE function for that:

It has to live in a schema that is visible to the current user, i.e. is in the respective search_path . Like the schema public , by default. If security is an issue, make sure it’s the first schema in the search_path or schema-qualify it in your call:

Visible for all users in the database (that are allowed to access schema public ).

Multiple values for current session:

Since plpgsql checks the existence of a table on creation, you need to create a (temporary) table val before you can create the function — even if a temp table is dropped at the end of the session while the function persists. The function will raise an exception if the underlying table is not found at call time.

The current schema for temporary objects comes before the rest of your search_path per default — if not instructed otherwise explicitly. You cannot exclude the temporary schema from the search_path , but you can put other schemas first.
Evil creatures of the night (with the necessary privileges) might tinker with the search_path and put another object of the same name in front:

It’s not much of a threat, since only privileged users can alter global settings. Other users can only do it for their own session. To prevent this from happening at all, set the search_path for your function and schema-qualify it in the call:

This would allow you (or anyone with the necessary privileges) to provide global (persistent) defaults in a table param.val .

Consider the related chapter of manual on creating functions with SECURITY DEFINER.

However, this super-secure function cannot be «inlined» and may perform slower that a simpler alternative with hard-wired schema:

15 полезных команд PostgreSQL

В сети много руководств по PostgreSQL, которые описывают основные команды. Но при погружении глубже в работу возникают такие практические вопросы, для которых требуются продвинутые команды.

Такие команды, или сниппеты, редко описаны в документации. Рассмотрим несколько на примерах, полезных как для разработчиков, так и для администраторов баз данных.

Получение информации о базе данных

Размер базы данных

Чтобы получить физический размер файлов (хранилища) базы данных, используем следующий запрос:

Результат будет представлен как число вида 41809016 .

current_database() — функция, которая возвращает имя текущей базы данных. Вместо неё можно ввести имя текстом:

Для того, чтобы получить информацию в человекочитаемом виде, используем функцию pg_size_pretty :

В результате получим информацию вида 40 Mb .

Перечень таблиц

Иногда требуется получить перечень таблиц базы данных. Для этого используем следующий запрос:

information_schema — стандартная схема базы данных, которая содержит коллекции представлений (views), таких как таблицы, поля и т.д. Представления таблиц содержат информацию обо всех таблицах баз данных.

Запрос, описанный ниже, выберет все таблицы из указанной схемы текущей базы данных:

В последнем условии IN можно указать имя определенной схемы.

Размер таблицы

По аналогии с получением размера базы данных размер данных таблицы можно вычислить с помощью соответствующей функции:

Функция pg_relation_size возвращает объём, который занимает на диске указанный слой заданной таблицы или индекса.

Имя самой большой таблицы

Для того, чтобы вывести список таблиц текущей базы данных, отсортированный по размеру таблицы, выполним следующий запрос:

Для того, чтобы вывести информацию о самой большой таблице, ограничим запрос с помощью LIMIT :

relname — имя таблицы, индекса, представления и т.п.
relpages — размер представления этой таблицы на диске в количествах страниц (по умолчанию одна страницы равна 8 Кб).
pg_class — системная таблица, которая содержит информацию о связях таблиц базы данных.

Перечень подключенных пользователей

Чтобы узнать имя, IP и используемый порт подключенных пользователей, выполним следующий запрос:

Активность пользователя

Чтобы узнать активность соединения конкретного пользователя, используем следующий запрос:

Работа с данными и полями таблиц

Удаление одинаковых строк

Если так получилось, что в таблице нет первичного ключа (primary key), то наверняка среди записей найдутся дубликаты. Если для такой таблицы, особенно большого размера, необходимо поставить ограничения (constraint) для проверки целостности, то удалим следующие элементы:

  • дублирующиеся строки,
  • ситуации, когда одна или более колонок дублируются (если эти колонки предполагается использовать в качестве первичного ключа).

Рассмотрим таблицу с данными покупателей, где задублирована целая строка (вторая по счёту).

Удалить все дубликаты поможет следующий запрос:

Уникальное для каждой записи поле ctid по умолчанию скрыто, но оно есть в каждой таблице.

Последний запрос требователен к ресурсам, поэтому будьте аккуратны при его выполнении на рабочем проекте.

18 ноября – 20 декабря, Москва, 43 990 ₽

Теперь рассмотрим случай, когда повторяются значения полей.

Если допустимо удаление дубликатов без сохранения всех данных, выполним такой запрос:

Если данные важны, то сначала нужно найти записи с дубликатами:

Перед удалением такие записи можно перенести во временную таблицу или заменить в них значение customer_id на другое.

Общая форма запроса на удаление описанных выше записей выглядит следующим образом:

Безопасное изменение типа поля

Может возникнуть вопрос о включении в этот список такой задачи. Ведь в PostgreSQL изменить тип поля очень просто с помощью команды ALTER . Давайте для примера снова рассмотрим таблицу с покупателями.


Для поля customer_id используется строковый тип данных varchar . Это ошибка, так как в этом поле предполагается хранить идентификаторы покупателей, которые имеют целочисленный формат integer . Использование varchar неоправданно. Попробуем исправить это недоразумение с помощью команды ALTER :

Но в результате выполнения получим ошибку:

ERROR: column “customer_id” cannot be cast automatically to type integer
SQL state: 42804
Hint: Specify a USING expression to perform the conversion.

Это значит, что нельзя просто так взять и изменить тип поля при наличии данных в таблице. Так как использовался тип varchar , СУБД не может определить принадлежность значения к integer . Хотя данные соответствуют именно этому типу. Для того, чтобы уточнить этот момент, в сообщении об ошибке предлагается использовать выражение USING , чтобы корректно преобразовать наши данные в integer :

В результате всё прошло без ошибок:

Обратите внимание, что при использовании USING кроме конкретного выражения возможно использование функций, других полей и операторов.

Например, преобразуем поле customer_id обратно в varchar , но с преобразованием формата данных:

В результате таблица примет следующий вид:

Поиск «потерянных» значений

Будьте внимательны при использовании последовательностей (sequence) в качестве первичного ключа (primary key): при назначении некоторые элементы последовательности случайно пропускаются, в результате работы с таблицей некоторые записи удаляются. Такие значения можно использовать снова, но найти их в больших таблицах сложно.

Рассмотрим два варианта поиска.

Первый способ
Выполним следующий запрос, чтобы найти начало интервала с «потерянным» значением:

В результате получим значения: 5 , 9 и 11 .

Если нужно найти не только первое вхождение, а все пропущенные значения, используем следующий (ресурсоёмкий!) запрос:

В результате видим следующий результат: 5 , 9 и 6 .

Второй способ
Получаем имя последовательности, связанной с customer_id :

И находим все пропущенные идентификаторы:

Подсчёт количества строк в таблице

Количество строк вычисляется стандартной функцией count , но её можно использовать с дополнительными условиями.

Общее количество строк в таблице:

Количество строк при условии, что указанное поле не содержит NULL :

Количество уникальных строк по указанному полю:

Использование транзакций

Транзакция объединяет последовательность действий в одну операцию. Её особенность в том, что при ошибке в выполнении транзакции ни один из результатов действий не сохранится в базе данных.

Начнём транзакцию с помощью команды BEGIN .

Для того, чтобы откатить все операции, расположенные после BEGIN , используем команду ROLLBACK .

А чтобы применить — команду COMMIT .

Просмотр и завершение исполняемых запросов

Для того, чтобы получить информацию о запросах, выполним следующую команду:

Для того, чтобы остановить конкретный запрос, выполним следующую команду, с указанием id процесса (pid):

Для того, чтобы прекратить работу запроса, выполним:

Работа с конфигурацией

Поиск и изменение расположения экземпляра кластера

Возможна ситуация, когда на одной операционной системе настроено несколько экземпляров PostgreSQL, которые «сидят» на различных портах. В этом случае поиск пути к физическому размещению каждого экземпляра — достаточно нервная задача. Для того, чтобы получить эту информацию, выполним следующий запрос для любой базы данных интересующего кластера:

Изменим расположение на другое с помощью команды:

Но для того, чтобы изменения вступили в силу, требуется перезагрузка.

Получение перечня доступных типов данных

Получим перечень доступных типов данных с помощью команды:

typname — имя типа данных.
typlen — размер типа данных.

Изменение настроек СУБД без перезагрузки

Настройки PostgreSQL находятся в специальных файлах вроде postgresql.conf и pg_hba.conf . После изменения этих файлов нужно, чтобы СУБД снова получила настройки. Для этого производится перезагрузка сервера баз данных. Понятно, что приходится это делать, но на продакшн-версии проекта, которым пользуются тысячи пользователей, это очень нежелательно. Поэтому в PostgreSQL есть функция, с помощью которой можно применить изменения без перезагрузки сервера:

Но, к сожалению, она применима не ко всем параметрам. В некоторых случаях для применения настроек перезагрузка обязательна.

Мы рассмотрели команды, которые помогут упростить работу разработчикам и администраторам баз данных, использующим PostgreSQL. Но это далеко не все возможные приёмы. Если вы сталкивались с интересными задачами, напишите о них в комментариях. Поделимся полезным опытом!

Использование функций в PostgreSQL как параметризированных представлений

В ежедневной работе часто встает задача ясно и просто ссылаться на большие списки колонок и выражений в выборке, и/или обходиться с громоздкими и неясными условиями в предложении where . Обычно для этих целей используются представления, что вполне удобно и наглядно. Можно сравнить запрос:

который достаточно ясно воспринимается как «берем активных пользователей и получаем по ним детальную информацию» и этот же запрос, но, так сказать, в развернутом виде:

Запросы подобного вида — с большим списком получаемых колонок и выражений на их основе, со сложными условиями и которые в реальной жизни нередко отягчены историческими напластованиями — зачастую совершенно нечитаемы и малопонятны. Наверное, стоит заметить, что само изменение понятия «активный» (например, убрать или добавить удаленных работников или сотрудников в декретном отпуске и т.п.) может стать не то чтобы нетривиальным, но очень утомительным занятием; да и на количестве ошибок оно вряд ли скажется достаточно благоприятно; и изменение списка колонок или просто выражения влечет за собой схожие последствия. Пожалуй, можно сказать, что если для таблиц выражение select * from table строго неприемлемо, то для представлений подобного вида оно, наверное, даже предпочтительно. Ну для некоторых, по крайней мере.

Рассмотрим другую задачу. Пусть у нас есть простая таблица пользователей:

и таблица друзей:

Требуется:
Получить определенного пользователя со списком друзей.

Так как эта операция требуется достаточно часто, создаем для нее представление:

Все хорошо, но появилось новое требование: получить пользователя со списком друзей, которые одновременно являются друзьями другого пользователя (например, просматривающего):

К сожалению, создать представление на основе этого запроса невозможно — передать идентификатор второго пользователя как параметр нельзя; но есть возможность обойти это ограничение с помощью декартова произведения:

Использование получившегося представления совершенно естественно:

Поступает новое требование: требуется получать не просто общих друзей, но общих друзей, зарегистрировавшихся в указанный промежуток времени. Так как создать таблицу со всеми возможными временными промежутками не представляется возможным, то придется создать функцию:

Использование тоже достаточно удобно:

Казалось бы, запрос, использующий эту фунцию, будет работать незамысловато — сначала функция вернет все возможные строки, а потом они будут отфильтрованы по условию. Давайте посмотрим:

Удивительно, но это не так — сервер сумел развернуть функцию непосредственно в тело запроса. Да, Postgresql в ряде случаев умеет внедрять тело функции непосредственно в запрос.
В каких случаях это происходит?

  1. Функция реализована на SQL ( LANGUAGE SQL ) как простой select , возвращающий скалярный тип
  2. Функция помечена как immutable или stable
  3. Функция не содержит подзапросов
  4. Функция не помечена как security definer
  5. У функции нет специфических set (н., set enable_seqscan=off и т.п.)
  6. Функция возвращает только одну колонку
  7. Возвращаемый тип должен совпадать с типом функции
  8. И еще ряд ограничений (полный список см. по ссылке ниже)

Это может пригодиться для инкапсуляции несложной, но громоздкой логики, например:

Как видно, никакого вызова функции тут нет — код функции вставился непосредственно в тело запроса. Эту функцию можно рассматривать как своеобразный макрос.


Хотелось бы заодно обратить внимание на компактный синтаксис записи вызова функции — в качестве параметра передается сразу запись, причем принимается не как строго определенный тип ( pg_class в данном случае), а как произвольный тип с колонкой relname .

У табличных функций похожие, но значительно более мягкие ограничения:

  1. Функция реализована на SQL ( LANGUAGE SQL )
  2. Функция immutable или stable
  3. Функция не security definer
  4. Функция не strict
  5. Нет специфических set
  6. Тело функции содержит единственный select (и только select , insert / update / delete не допускаются)
  7. Типы возвращаемых колонок должны соответствовать типам в объявлении функции
  8. И еще ряд достаточно специфичных ограничений

Таким образом, реализованное в Postgres встраивание тела функции непосредственно в запрос дает возможность эффективно реализовать отсутствующую в стандарте, но тем не менее востребованную и удобную конструкцию «представление с параметрами».

Интересно, что в DB2 и SQL Server для решения задачи «представление с параметрами» также используются функции, встраиваемые в запрос.

Есть ли способ определить именованную константу в запросе PostgreSQL?

Есть ли способ определить именованную константу в запросе PostgreSQL? Например:

Этот вопрос задан раньше (Как вы используете переменные script в PostgreSQL?). Тем не менее, есть трюк, который иногда используется для запросов:

То есть, я определяю CTE, называемый const, который имеет определенные там константы. Затем я могу пересечь это в свой запрос, любое количество раз на любом уровне. Я нашел это особенно полезным, когда я занимаюсь датами, и мне нужно обрабатывать константы даты во многих подзапросах.

PostgreSQL не имеет встроенного способа определения (глобальных) переменных, таких как MySQL или Oracle. (Существует ограниченное обходное решение, использующее «настраиваемые параметры»). В зависимости от того, что вы хотите, есть другие способы:

Для одного запроса

Вы можете предоставить значения в верхней части запроса в CTE, например, уже предоставленном GGordon.

Глобальная постоянная постоянная:

Вы можете создать для IMMUTABLE функцию IMMUTABLE :

Он должен жить в схеме, которая видна текущему пользователю, то есть находится в соответствующем пути search_path . Как и public схема, по умолчанию. Если проблема search_path безопасностью, убедитесь, что она первая схема в пути search_path или schema — квалифицирует ее в вашем вызове:

Видимый для всех пользователей в базе данных (которым разрешен доступ к public схеме).

Несколько значений для текущего сеанса:

Поскольку plpgsql проверяет существование таблицы при создании, вам нужно создать (временную) таблицу val прежде чем вы сможете создать эту функцию, даже если временная таблица отбрасывается в конце сеанса, пока функция сохраняется. Функция вызовет исключение, если базовая таблица не будет найдена во время вызова.

Текущая схема для временных объектов идет до остальной части вашего пути search_path по умолчанию — если не указано явно явно. Вы не можете исключить временную схему из пути search_path , но сначала можете поместить другие схемы.
Злые существа ночи (с необходимыми привилегиями) могут search_path с помощью search_path и ставить другой объект с таким же именем спереди:

Это не большая угроза, так как только привилегированные пользователи могут изменять глобальные настройки. Другие пользователи могут делать это только для своей собственной сессии. Чтобы этого не происходило, установите для параметра search_path для вашей функции и схемы — укажите его в вызове:

Или используйте вместо этого:

Это позволит вам (или кому-либо с необходимыми привилегиями) предоставлять глобальные (постоянные) значения по умолчанию в таблице param.val .

Рассмотрим соответствующую главу руководства по созданию функций с ОПРЕДЕЛЕНИЕМ БЕЗОПАСНОСТИ.

Однако эта суперзащищенная функция не может быть «встроена» и может работать медленнее, чем более простая альтернатива жесткой схеме:

Похожие ответы с большим количеством опций:

LXXXI. PostgreSQL functions

PostgreSQL database is Open Source product and available without cost. Postgres, developed originally in the UC Berkeley Computer Science Department, pioneered many of the object-relational concepts now becoming available in some commercial databases. It provides SQL92/SQL99 language support, transaction integrity and type extensibility. PostgreSQL is an open source descendant of this original Berkeley code.

To use PostgreSQL support, you need PostgreSQL 6.5 or later, PostgreSQL 7.0 or later to enable all PostgreSQL module features. PostgreSQL supports many character encoding including multibyte character encoding. The current version and more information about PostgreSQL is available at http://www.postgresql.org/ .

In order to enable PostgreSQL support, —with-pgsql[=DIR] is required when you compile PHP. DIR is the PostgreSQL base install directory, defaults to /usr/local/pgsql . If shared object module is available, PostgreSQL module may be loaded using extension directive in php.ini or dl() function.

Поведение этих функций находится в зависимости от установок в php.ini .

Таблица 1. PostgreSQL configuration options

Name Default Changeable
pgsql.allow_persistent «1» PHP_INI_SYSTEM
pgsql.max_persistent «-1» PHP_INI_SYSTEM
pgsql.max_links «-1» PHP_INI_SYSTEM
pgsql.auto_reset_persistent «0» PHP_INI_SYSTEM
pgsql.ignore_notice «0» PHP_INI_ALL
pgsql.log_notice «0» PHP_INI_ALL

Here is a short explanation of the configuration directives.

Whether to allow persistent Postgres connections.

The maximum number of persistent Postgres connections per process.

The maximum number of Postgres connections per process, including persistent connections.

Using the PostgreSQL module with PHP 4.0.6 is not recommended due to a bug in the notice message handling code. Use 4.1.0 or later.

Внимание

PostgreSQL function names will be changed in 4.2.0 release to confirm to current coding standards. Most of new names will have additional underscores, e.g. pg_lo_open(). Some functions are renamed to different name for consistency. e.g. pg_exec() to pg_query(). Older names can be used in 4.2.0 and a few releases from 4.2.0, but they may be deleted in the future.

Таблица 2. Function names changed

Внимание
Old name New name
pg_exec() pg_query()
pg_getlastoid() pg_last_oid()
pg_cmdtuples() pg_affected_rows()
pg_numrows() pg_num_rows()
pg_numfields() pg_num_fields()
pg_fieldname() pg_field_name()
pg_fieldsize() pg_field_size()
pg_fieldnum() pg_field_num()
pg_fieldprtlen() pg_field_prtlen()
pg_fieldisnull() pg_field_is_null()
pg_freeresult() pg_free_result()
pg_result() pg_fetch_result()
pg_loreadall() pg_lo_read_all()
pg_locreate() pg_lo_create()
pg_lounlink() pg_lo_unlink()
pg_loopen() pg_lo_open()
pg_loclose() pg_lo_close()
pg_loread() pg_lo_read()
pg_lowrite() pg_lo_write()
pg_loimport() pg_lo_import()
pg_loexport() pg_lo_export()

Not all functions are supported by all builds. It depends on your libpq (The PostgreSQL C Client interface) version and how libpq is compiled. If there is missing function, libpq does not support the feature required for the function.

It is also important that you do not use an older libpq than the PostgreSQL Server to which you will be connecting. If you use libpq older than PostgreSQL Server expects, you may have problems.

Since version 6.3 (03/02/1998) PostgreSQL uses unix domain sockets by default. TCP port will NOT be opened by default. A table is shown below describing these new connection possibilities. This socket will be found in /tmp/.s.PGSQL.5432 . This option can be enabled with the ‘-i’ flag to postmaster and it’s meaning is: «listen on TCP/IP sockets as well as Unix domain sockets».

Таблица 3. Postmaster and PHP

Postmaster PHP Status
postmaster & pg_connect(«dbname=MyDbName»); OK
postmaster -i & pg_connect(«dbname=MyDbName»); OK
postmaster & pg_connect(«host=localhost dbname=MyDbName»); Unable to connect to PostgreSQL server: connectDB() failed: Is the postmaster running and accepting TCP/IP (with -i) connection at ‘localhost’ on port ‘5432’? in /path/to/file.php on line 20.
postmaster -i & pg_connect(«host=localhost dbname=MyDbName»); OK

A connection to PostgreSQL server can be established with the following value pairs set in the command string: $conn = pg_connect(«host=myHost port=myPort tty=myTTY options=myOptions dbname=myDB user=myUser password=myPassword «);

The previous syntax of: $conn = pg_connect («host», «port», «options», «tty», «dbname») has been deprecated.

Environmental variables affect PostgreSQL server/client behavior. For example, PostgreSQL module will lookup PGHOST environment variable when the hostname is omitted in the connection string. Supported environment variables are different from version to version. Refer to PostgreSQL Programmer’s Manual (libpq — Environment Variables) for details.

Make sure you set environment variables for appropriate user. Use $_ENV or getenv() to check which environment variables are available to the current process.

Пример 1. Setting default parameters

PGHOST=pgsql.example.com PGPORT=7890 PGDATABASE=web-system PGUSER=web-user PGPASSWORD=secret PGDATESTYLE=ISO PGTZ=JST PGCLIENTENCODING=EUC-JP export PGHOST PGPORT PGDATABASE PGUSER PGPASSWORD PGDATESTYLE PGTZ PGCLIENTENCODING

Перечисленные ниже константы определены данным расширением и могут быть доступны только в том случае, если PHP был собран с поддержкой этого расширения или же в том случае, если данное расширение подгружается во время выполнения.

Цикл по результату запроса в PostgreSQL без использования предопределенного таблицы

Я создал функцию цикла по результатам запроса и она отлично работает. Я хотел бы, чтобы он работать без моего того, чтобы предварительно создать таблицу для хранения результатов. То есть, я просто хочу, чтобы перебрать строки. Дело в том, что я не знаю, как объявить запись, которая к тому же типу, как строка возвращается, когда нет ничего, чтобы проверить против.

Вот моя текущая функция:

Работает хорошо, но она нуждается в предопределенную таблицу для того, чтобы мне сделать это: (DECLARE Rec myschema.tmpfiles% ROWTYPE;) и получить тип строки.

Как цикл по этому запросу без предварительного определения таблицы результатов?

Тип записи является переменной строкой типа без какой-либо заранее определенной структуры.

Вы объявляете его, выполнив name RECORD;

Переменные записи аналогичны строкам типа переменных, но они не имеют предопределенную структуру. Они берут на фактическую структуру строки в строке они назначены во время SELECT, или для команды. Подструктура переменных записей может меняться каждый раз, когда он назначен. Следствие этого является то, что пока запись переменный не первым назначено, он не имеет подструктуры, и любую попытку получить доступ к полю в нем нарисует ошибку времени выполнения.

Илон Маск рекомендует:  Что такое код drop trigger
Понравилась статья? Поделиться с друзьями:
Кодинг, CSS и SQL