PostgreSQLLa base de données la plus sophistiquée au monde.
Documentation PostgreSQL 12.18 » Langage SQL » Fonctions et opérateurs » Fonctions et opérateurs d'informations système

9.25. Fonctions et opérateurs d'informations système

Le Tableau 9.63 présente diverses fonctions qui extraient des informations de session et système.

En plus des fonctions listées dans cette section, il existe plusieurs fonctions relatives au système de statistiques qui fournissent aussi des informations système. Voir Section 27.2.3 pour plus d'informations.

Tableau 9.63. Fonctions d'information de session

NomType de retourDescription
current_catalognamenom de la base de données en cours (appelée « catalog » dans le standard SQL)
current_database()nomnom de la base de données courante
current_query()texttexte de la requête en cours d'exécution, telle qu'elle a été soumise par le client (pourrait contenir plus d'une instruction)
current_rolenameéquivalent à current_user
current_schema[()]nomnom du schéma courant
current_schemas(boolean)nom[]nom des schémas dans le chemin de recherche, avec optionnellement les schémas implicites
current_usernomnom d'utilisateur du contexte d'exécution courant
inet_client_addr()inetadresse de la connexion distante
inet_client_port()intport de la connexion distante
inet_server_addr()inetadresse de la connexion locale
inet_server_port()intport de la connexion locale
pg_backend_pid()int identifiant du processus serveur attaché à la session en cours
pg_blocking_pids(int)int[]identifiants des processus (PID) qui empêchent l'identifiant de processus (PID) spécifié d'acquérir un verrou
pg_conf_load_time()timestamp with time zonedate et heure du dernier chargement de la configuration
pg_notification_queue_usage()doublefraction de la queue de notification asynchrone actuellement occupée (0-1)
pg_is_other_temp_schema(oid)booleans'agit-il du schéma temporaire d'une autre session ?
pg_jit_available()booleanest-ce qu'une extension de compilation JIT (voir Chapitre 31) est disponible dans cette session et que le paramètre de configuration jit est configuré à false ?
pg_listening_channels()setof textnoms des canaux que la session est en train d'écouter
pg_current_logfile([text])textnom de fichier principal de traces, ou traces dans le format demandé, actuellement en cours d'utilisation par le collecteur de traces
pg_my_temp_schema()oidOID du schéma temporaire de la session, 0 si aucun
pg_postmaster_start_time()timestamp with time zonedate et heure du démarrage du serveur
pg_safe_snapshot_blocking_pids(int)int[]identifiants de processus (PID) qui empêchent l'identifiant de processus serveur spécifié d'acquérir un instantané sûr
pg_trigger_depth()intniveau d'empilement actuel de triggers PostgreSQL (0 si la fonction n'est pas appelé à partir d'un trigger)
session_usernamenom de l'utilisateur de session
usernameéquivalent à current_user
version()textinformations de version de PostgreSQL. Voir aussi server_version_num pour une version exploitable par une machine

Note

current_catalog, current_role, current_schema, current_user, session_user, ont un statut syntaxique spécial en SQL : elles doivent être appelées sans parenthèses à droite (optionnel avec PostgreSQL dans le cas de current_schema).

session_user est habituellement l'utilisateur qui a initié la connexion à la base de données ; mais les superutilisateurs peuvent modifier ce paramétrage avec SET SESSION AUTHORIZATION. current_user est l'identifiant de l'utilisateur, utilisable pour les vérifications de permissions. Il est habituellement identique à l'utilisateur de la session, mais il peut être modifié avec SET ROLE. Il change aussi pendant l'exécution des fonctions comprenant l'attribut SECURITY DEFINER. En langage Unix, l'utilisateur de la session est le « real user » (NdT : l'utilisateur réel) et l'utilisateur courant est l'« effective user » (NdT : l'utilisateur effectif). current_role et user sont des synonymes de current_user. (Le standard SQL fait une distinction entre current_role et current_user, mais PostgreSQL ne la fait pas, car il unifie les utilisateurs et les rôles en un seul type d'entité.)

current_schema renvoie le nom du premier schéma dans le chemin de recherche (ou une valeur NULL si ce dernier est vide). C'est le schéma utilisé pour toute création de table ou autre objet nommé sans précision d'un schéma cible. current_schemas(boolean) renvoie un tableau qui contient les noms de tous les schémas du chemin de recherche. L'option booléenne indique si les schémas système implicitement inclus, comme pg_catalog, doivent être inclus dans le chemin de recherche retourné.

Note

Le chemin de recherche est modifiable à l'exécution. La commande est :

SET search_path TO schema [, schema, ...]

inet_client_addr renvoie l'adresse IP du client courant et inet_client_port le numéro du port. inet_server_addr renvoie l'adresse IP sur laquelle le serveur a accepté la connexion courante et inet_server_port le numéro du port. Toutes ces fonctions renvoient NULL si la connexion courante est établie via une socket de domaine Unix.

pg_blocking_pids renvoie un tableau d'identifiants de processus (PID) pour les sessions bloquant le processus serveur dont le PID est fourni en argument. Un tableau vide est renvoyé si le PID n'existe pas ou s'il n'est pas bloqué. Un processus serveur en bloque un autre s'il détient un verrou qui entre en conflit avec la demande de verrou d'un autre processus (blocage dur) ou s'il attend un verrou qui entrerait en conflit avec la demande de verrou d'un processus bloqué et qui est devant lui dans la queue d'attente (verrou léger). Lors de l'utilisation de requêtes parallélisées, le résultat liste toujours les PID visibles par le client (autrement dit, les résultats de pg_backend_pid) même si le verrou réel est détenu ou en attente par un processus fils. De ce fait, des PID pourraient apparaître plusieurs fois dans le résultat. De plus, il faut noter que, quand une transaction préparée détient un verrou en conflit, elle sera représentée par un identifiant de processus 0 dans le résultat de cette fonction. Les appels fréquents à cette fonction peuvent avoir un impact sur les performances de la base de données, car cette fonction a besoin d'un accès exclusif à l'état partagé du gestionnaire de verrous pendant un court instant.

pg_conf_load_time renvoie timestamp with time zone indiquant à quel moment les fichiers de configuration du serveur ont été chargés. (Si la session en cours était déjà là à ce moment, ce sera le moment où la session elle-même a relu les fichiers de configurations. Cela veut dire que ce que renvoie cette fonction peut varier un peu suivant les sessions. Sinon, c'est le temps où le processus maître a relu les fichiers de configuration.)

pg_current_logfile retourne, sous forme de text, le chemin du ou des fichiers de traces actuellement en cours d'utilisation par le collecteur de traces. Le chemin inclut le répertoire log_directory et le nom du fichier de traces. La récupération des traces doit être activée ou la valeur retournée est NULL. Quand plusieurs fichiers de traces existent, chacun dans un format différent, pg_current_logfile appelé sans argument retourne le chemin du fichier ayant le premier des formats trouvés dans la liste ordonnée : stderr, csvlog. NULL est retourné si aucun des fichiers de traces n'a un de ces formats. Pour demander un format de traces spécifique, fournissez, comme text, soit csvlog, soit stderr comme valeur de l'argument facultatif. La valeur retournée est NULL quand le format de traces demandé n'est pas configuré dans log_destination. pg_current_logfile reflète le contenu du fichier current_logfiles.

pg_my_temp_schema renvoie l'identifiant (OID) du schéma temporaire de la session en cours ou 0 si ce schéma n'existe pas (parce que la session n'a pas créé de tables temporaires). pg_is_other_temp_schema renvoie true si l'OID indiqué est l'OID d'un schéma temporaire d'une autre session. (Ceci peut être utile pour exclure les tables temporaires d'autres sessions lors d'un affichage du catalogue.)

pg_listening_channels renvoie un ensemble de noms de canaux asynchrones de notifications que la session en cours écoute. pg_notification_queue_usage renvoie la fraction de l'espace total disponible pour les notifications actuellement occupées par des notifications en attente de traitement, sous la forme d'un double allant de 0 à 1. Voir LISTEN et NOTIFY pour plus d'informations.

pg_postmaster_start_time renvoie un horodatage au format timestamp with time zone correspondant au moment du démarrage du serveur.

pg_safe_snapshot_blocking_pids renvoie un tableau d'identifiants de processus (PID) des sessions qui empêchent le processus serveur d'identifiant de processus fourni d'acquérir un instantané sûr, ou un tableau vide s'il n'y pas de tels processus ou s'il n'est pas bloqué. Une session exécutant un bloc de transaction SERIALIZABLE empêche une transaction SERIALIZABLE READ ONLY DEFERRABLE d'acquérir un instantané jusqu'à ce que cette dernière détermine qu'il est sûr d'obtenir des verrous sur prédicat. Voir Section 13.2.3 pour plus d'informations sur les transactions sérialisables et déférables. Des appels fréquents à cette fonction pourraient avoir un certain impact sur les performances de la base, car elle a besoin d'accéder à l'état partagé du gestionnaire de verrou sur prédicat pendant un bref instant.

version renvoie une chaîne qui décrit la version du serveur PostgreSQL. Vous pouvez aussi obtenir cette information à partir de server_version ou, pour une version exploitable par un programme, server_version_num. Les développeurs de logiciels devraient utiliser server_version_num (disponible depuis la version 8.2) ou PQserverVersion() au lieu d'exploiter la version textuelle.

Le Tableau 9.64 liste les fonctions qui permettent aux utilisateurs de consulter les privilèges d'accès. Voir la Section 5.7 pour plus d'informations sur les privilèges.

Tableau 9.64. Fonctions de consultation des privilèges d'accès

NomType de retourDescription
has_any_column_privilege(user, table, privilege) booleanl'utilisateur a-t-il un droit sur une des colonnes de cette table ?
has_any_column_privilege(table, privilege) booleanl'utilisateur actuel a-t-il un droit sur une des colonnes de cette table ?
has_column_privilege(user, table, column, privilege) booleanl'utilisateur a-t-il un droit sur la colonne ?
has_column_privilege(table, column, privilege) booleanl'utilisateur actuel a-t-il un droit sur la colonne ?
has_database_privilege (utilisateur, base, privilège) booleanutilisateur a-t-il le privilège privilège sur base ?
has_database_privilege (base, privilège) booleanl'utilisateur courant a-t-il le privilège privilège sur base ?
has_foreign_data_wrapper_privilege(user, fdw, privilege) booleanl'utilisateur a-t-il un droit sur ce wrapper de données distantes ?
has_foreign_data_wrapper_privilege(fdw, privilege) booleanl'utilisateur actuel a-t-il un droit sur ce wrapper de données distantes ?
has_function_privilege (utilisateur, fonction, privilège) booleanutilisateur a-t-il le privilège privilège sur fonction ?
has_function_privilege (fonction, privilège) booleanl'utilisateur courant a-t-il le privilège privilège sur fonction ?
has_language_privilege (utilisateur, langage, privilège) booleanutilisateur a-t-il le privilège privilège sur langage ?
has_language_privilege (langage, droit) booleanl'utilisateur courant a-t-il le privilège privilège sur langage ?
has_schema_privilege(utilisateur, schéma, privilège) booleanutilisateur a-t-il le privilège privilège sur schéma ?
has_schema_privilege(schéma, privilège) booleanl'utilisateur courant a-t-il le privilège privilège sur schéma ?
has_sequence_privilege(user, sequence, privilege) booleanl'utilisateur a-t-il un droit sur cette séquence ?
has_sequence_privilege(sequence, privilege) booleanl'utilisateur actuel a-t-il un droit sur cette séquence ?
has_server_privilege(user, server, privilege) booleanl'utilisateur actuel a-t-il un droit sur ce serveur ?
has_server_privilege(server, privilege) booleanl'utilisateur actuel a-t-il un droit sur ce serveur ?
has_table_privilege(utilisateur, table, privilège) booleanutilisateur a-t-il le privilège privilège sur table ?
has_table_privilege(table, privilege) booleanl'utilisateur courant a-t-il le privilège privilège sur table ?
has_tablespace_privilege (utilisateur, tablespace, privilège) booleanutilisateur a-t-il le privilège privilège sur tablespace ?
has_tablespace_privilege (tablespace, privilège) booleanl'utilisateur courant a-t-il le privilège privilège sur tablespace ?
has_type_privilege(user, type, privilege) booleanl'utilisateur a-t-il des droits pour le type ?
has_type_privilege(type, privilege) booleanl'utilisateur courant a-t-il des droits pour le type ?
pg_has_role(utilisateur, rôle, privilège) booleanutilisateur a-t-il le privilège privilège sur rôle ?
row_security_active(table) booleanest-ce que l'utilisateur actuel a le mode row level security activé pour la table ?
pg_has_role(rôle, privilège) booleanl'utilisateur courant a-t-il le privilège privilège sur rôle ?

has_table_privilege vérifie si l'utilisateur possède un privilège particulier d'accès à une table. L'utilisateur peut être indiqué par son nom ou son OID (pg_authid.oid), public pour indiquer le pseudo-rôle PUBLIC. Si l'argument est omis, current_user est utilisé. La table peut être indiquée par son nom ou par son OID. (Il existe donc six versions de has_table_privilege qui se distinguent par le nombre et le type de leurs arguments.) Lors de l'indication par nom, il est possible de préciser le schéma. Les privilèges possibles, indiqués sous la forme d'une chaîne de caractères, sont : SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES ou TRIGGER. En option, WITH GRANT OPTION peut être ajouté à un type de droit pour tester si le droit est obtenu avec l'option « grant ». De plus, plusieurs types de droits peuvent être listés, séparés par des virgules, auquel cas le résultat sera true si un des droits listés est obtenu. (la casse des droits n'a pas d'importance et les espaces blancs supplémentaires sont autorisés entre, mais pas dans, les noms des droits.) Certains exemples :

SELECT has_table_privilege('myschema.mytable', 'select');
SELECT has_table_privilege('joe', 'mytable', 'INSERT, SELECT WITH GRANT OPTION');
    

has_sequence_privilege vérifie si un utilisateur peut accéder à une séquence d'une façon ou d'une autre. Les arguments sont analogues à ceux de la fonction has_table_privilege. Le type de droit d'accès doit valoir soit USAGE, soit SELECT soit UPDATE.

has_any_column_privilege vérifie si un utilisateur peut accéder à une colonne d'une table d'une façon particulière. Les possibilités pour que ces arguments correspondent à ceux de has_table_privilege, sauf que le type de droit d'accès désiré doit être évalué à une combinaison de SELECT, INSERT, UPDATE ou REFERENCES. Notez qu'avoir un droit au niveau de la table le donne implicitement pour chaque colonne de la table, donc has_any_column_privilege renverra toujours true si has_table_privilege le fait pour les mêmes arguments. Mais has_any_column_privilege réussit aussi s'il y a un droit « grant » sur une colonne pour ce droit.

has_column_privilege vérifie si un utilisateur peut accéder à une colonne d'une façon particulière. Les possibilités pour ses arguments sont analogues à has_table_privilege, avec un supplément : la colonne doit être indiquée soit par nom, soit par numéro d'attribut. Le type de droit d'accès désiré doit être une combinaison de SELECT, INSERT, UPDATE ou REFERENCES. Notez qu'avoir un de ces droits au niveau table les donne implicitement pour chaque colonne de la table.

has_database_privilege vérifie si un utilisateur peut accéder à une base de données d'une façon particulière. Les possibilités pour ses arguments sont analogues à has_table_privilege. Le type de droit d'accès désiré doit être une combinaison de CREATE, CONNECT, TEMPORARY ou TEMP (qui est équivalent à TEMPORARY).

has_function_privilege vérifie si un utilisateur peut accéder à une fonction d'une façon particulière. Les possibilités pour ses arguments sont analogues à has_table_privilege. Lors de la spécification d'une fonction par une chaîne texte plutôt que par un OID, l'entrée autorisée est la même que pour le type de données regprocedure (voir Section 8.19). Le type de droit d'accès désiré doit être EXECUTE. Voici un exemple :

SELECT has_function_privilege('joeuser', 'myfunc(int, text)', 'execute');
    

has_foreign_data_wrapper_privilege vérifie si un utilisateur peut accéder à un wrapper de données distantes d'une façon particulière. Les possibilités pour ses arguments sont analogues à has_table_privilege. Le type de droit d'accès désiré doit être USAGE.

has_language_privilege vérifie si un utilisateur peut accéder à un langage de procédure d'une façon particulière. Les possibilités pour ses arguments sont analogues à has_table_privilege. Le type de droit d'accès désiré doit être USAGE.

has_schema_privilege vérifie si un utilisateur peut accéder à un schéma d'une façon particulière. Les possibilités pour ses arguments sont analogues à has_table_privilege. Le type de droit d'accès désiré doit être une combinaison de CREATE et USAGE.

has_server_privilege vérifie si un utilisateur peut accéder à un serveur distant d'une façon particulière. Les possibilités pour ses arguments sont analogues à has_table_privilege. Le type de droit d'accès désiré doit être USAGE.

has_tablespace_privilege vérifie si l'utilisateur possède un privilège particulier d'accès à un tablespace. Ses arguments sont analogues à has_table_privilege. Le seul privilège possible est CREATE.

has_type_privilege vérifie si un utilisateur peut accéder à un type d'une façon particulière. Les possibilités au niveau des arguments sont analogues à has_table_privilege. Quand un type est spécifié par une chaîne de caractères plutôt que par un OID, l'entrée autorisée est la même que pour le type de données regtype (voir Section 8.19). Le type de privilège souhaité doit être USAGE.

pg_has_role vérifie si l'utilisateur possède un privilège particulier d'accès à un rôle. Ses arguments sont analogues à has_table_privilege, sauf que public n'est pas autorisé comme nom d'utilisateur. Le privilège doit être une combinaison de MEMBER et USAGE. MEMBER indique une appartenance directe ou indirecte au rôle (c'est-à-dire le droit d'exécuter SET ROLE) alors que USAGE indique que les droits du rôle sont immédiatement disponibles sans avoir à exécuter SET ROLE.

La fonction row_security_active vérifie si la sécurité niveau ligne est activée pour la table spécifiée dans le contexte de current_user et de l'environnement. La table peut être indiquée par son nom ou par son OID.

Tableau 9.65 montre les opérateurs disponibles pour le type aclitem, qui est la représentation des droits d'accès. Voir Section 5.7 pour plus d'informations sur la lecture des valeurs de droits d'accès.

Tableau 9.65. Opérateurs aclitem

OpérateurDescriptionExempleRésultat
= égalité'calvin=r*w/hobbes'::aclitem = 'calvin=r*w*/hobbes'::aclitemf
@> contient l'élément'{calvin=r*w/hobbes,hobbes=r*w*/postgres}'::aclitem[] @> 'calvin=r*w/hobbes'::aclitemt
~ contient l'élément'{calvin=r*w/hobbes,hobbes=r*w*/postgres}'::aclitem[] ~ 'calvin=r*w/hobbes'::aclitemt

Tableau 9.66 montre des fonctions supplémentaires pour gérer le type aclitem.

Tableau 9.66. Fonctions aclitem

NomType en retourDescription
acldefault(type, ownerId)aclitem[]obtient les droits d'accès par défaut à un objet appartenant à ownerId
aclexplode(aclitem[])setof recordtransforme un tableau aclitem en ensemble de lignes
makeaclitem(grantee, grantor, privilege, grantable)aclitemconstruit un aclitem à partir de l'entrée

acldefault renvoie les droits d'accès internes par défaut pour un objet de type type appartenant à ownerId. Ils représentent les droits d'accès qui seront assumés quand il n'y a pas d'entrée ACL pour l'objet. (Les droits d'accès par défaut sont décrits dans Section 5.7.) Le paramètre type est un CHAR : écrire 'c' pour COLUMN, 'r' pour TABLE et les objets de type table, 's' pour SEQUENCE, 'd' pour DATABASE, 'f' pour FUNCTION ou PROCEDURE, 'l' pour LANGUAGE, 'L' pour LARGE OBJECT, 'n' pour SCHEMA, 't' pour TABLESPACE, 'F' pour FOREIGN DATA WRAPPER, 'S' pour FOREIGN SERVER ou 'T' pour TYPE ou DOMAIN.

aclexplode transforme un tableau aclitem en un ensemble de lignes. Les colonnes en sortie sont : celui qui a donné le droit (grantor oid), celui qui reçoit le droit (grantee oid, 0 pour PUBLIC), droit donné (text, par exemple SELECT, ...) et si le droit est donnable à un autre rôle (boolean). makeaclitem réalise l'opération inverse.

Le Tableau 9.67 affiche les fonctions qui permettent de savoir si un objet particulier est visible dans le chemin de recherche courant. Une table est dite visible si son schéma contenant est dans le chemin de recherche et qu'aucune table de même nom ne la précède dans le chemin de recherche. C'est équivalent au fait que la table peut être référencée par son nom sans qualification explicite de schéma. Par exemple, pour lister les noms de toutes les tables visibles :

SELECT relname FROM pg_class WHERE pg_table_is_visible(oid);

Tableau 9.67. Fonctions d'interrogation de visibilité dans les schémas

NomType de retourDescription
pg_collation_is_visible(collation_oid) booleanle collationnement est-il visible dans le chemin de recherche ?
pg_conversion_is_visible (conversion_oid) booleanla conversion est-elle visible dans le chemin de recherche ?
pg_function_is_visible (function_oid) booleanla fonction est-elle visible dans le chemin de recherche ?
pg_opclass_is_visible(opclass_oid) booleanla classe d'opérateur est-elle visible dans le chemin de recherche ?
pg_operator_is_visible(operator_oid) booleanl'opérateur est-il visible dans le chemin de recherche ?
pg_opfamily_is_visible(opclass_oid) booleanla famille d'opérateur est-elle visible dans le chemin de recherche ?
pg_statistics_obj_is_visible(stat_oid) booleanest-ce que l'objet statistiques est visible dans le chemin de recherche ?
pg_table_is_visible(table_oid) booleanla table est-elle visible dans le chemin de recherche ?
pg_ts_config_is_visible(config_oid) booleanla configuration de la recherche textuelle est-elle visible dans le chemin de recherche ?
pg_ts_dict_is_visible(dict_oid) booleanle dictionnaire de recherche textuelle est-il visible dans le chemin de recherche ?
pg_ts_parser_is_visible(parser_oid) booleanl'analyseur syntaxique de recherche textuelle est-il visible dans le chemin de recherche ?
pg_ts_template_is_visible(template_oid) booleanle modèle de recherche textuelle est-il visible dans le chemin de recherche ?
pg_type_is_visible(type_oid) booleanle type (ou domaine) est-il visible dans le chemin de recherche ?

Chaque fonction vérifie la visibilité d'un type d'objet de la base de données. pg_table_is_visible peut aussi être utilisée avec des vues, vues matérialisées, index, séquences et tables externes, pg_function_is_visible peut aussi être utilisée avec les procédures et agrégats ; pg_type_is_visible avec les domaines. Pour les fonctions et les opérateurs, un objet est visible dans le chemin de recherche si aucun objet de même nom et prenant des arguments de mêmes types de données n'est précédemment présent dans le chemin de recherche. Pour les classes d'opérateurs, on considère à la fois le nom et la méthode d'accès à l'index associé.

Toutes ces fonctions nécessitent des OID pour identifier les objets à vérifier. Pour tester un objet par son nom, il est préférable d'utiliser les types d'alias d'OID (regclass, regtype, regprocedure ou regoperator). Par exemple

SELECT pg_type_is_visible('mon_schema.widget'::regtype);

Il n'est pas très utile de tester ainsi un nom non qualifié -- si le nom peut être reconnu, c'est qu'il est visible.

Le Tableau 9.68 liste les fonctions qui extraient des informations des catalogues système.

Tableau 9.68. Fonctions d'information du catalogue système

NomType de retourDescription
format_type (type_oid, typemod)textrécupère le nom SQL d'un type de données
pg_get_constraintdef(constraint_oid)textrécupère la définition d'une contrainte
pg_get_constraintdef(constraint_oid, pretty_bool)textrécupère la définition d'une contrainte
pg_get_expr(pg_node_tree, relation_oid)textdécompile la forme interne d'une expression, en supposant que toutes les variables qu'elle contient font référence à la relation indiquée par le second paramètre
pg_get_expr(pg_node_tree, relation_oid, pretty_bool)textdécompile la forme interne d'une expression, en supposant que toutes les variables qu'elle contient font référence à la relation indiquée par le deuxième paramètre
pg_get_functiondef(func_oid)textobtient une définition de la fonction ou de la procédure
pg_get_function_arguments(func_oid)textobtient une définition de la liste des arguments de la fonction ou procédure (avec les valeurs par défaut)
pg_get_function_identity_arguments (func_oid)textobtient une définition de la liste des arguments de la fonction ou procédure (sans valeurs par défaut)
pg_get_function_result(func_oid)textobtient la clause RETURNS pour la fonction
pg_get_indexdef(index_oid)textrécupère la commande CREATE INDEX de l'index
pg_get_indexdef(index_oid, column_no, pretty_bool)textrécupère la commande CREATE INDEX pour l'index, ou la définition d'une seule colonne d'index quand column_no ne vaut pas zéro
pg_get_keywords()setof recordrécupère la liste des mots-clés SQL et leurs catégories
pg_get_ruledef(rule_oid)textrécupère la commande CREATE RULE pour une règle
pg_get_ruledef(rule_oid, pretty_bool)textrécupère la commande CREATE RULE de la règle
pg_get_serial_sequence(table_name, column_name)textrécupère le nom de la séquence qu'une colonne serial ou qu'une colonne d'identité utilisée
pg_get_statisticsobjdef(statobj_oid)textrécupère la commande CREATE STATISTICS pour un objet statistiques
pg_get_triggerdef(trigger_oid)textrécupère la commande CREATE [ CONSTRAINT ] TRIGGER du trigger
pg_get_triggerdef(trigger_oid, pretty_bool)textrécupère la commande CREATE [ CONSTRAINT ] TRIGGER du déclencheur
pg_get_userbyid(role_oid)namerécupère le nom du rôle possédant cet OID
pg_get_viewdef(view_name)textrécupère la commande SELECT sous-jacente pour une vue standard ou matérialisée (obsolète)
pg_get_viewdef(view_name, pretty_bool)textrécupère la commande SELECT sous-jacente pour une vue standard ou matérialisée (obsolète)
pg_get_viewdef(view_oid)textrécupère la commande SELECT sous-jacente pour une vue standard ou matérialisée
pg_get_viewdef(view_oid, pretty_bool)textrécupère la commande SELECT sous-jacente pour une vue standard ou matérialisée
pg_get_viewdef(view_oid, wrap_column_int)textrécupère la commande SELECT pour une vue standard ou matérialisée ; les lignes contenant des champs sont terminées suivant le nombre de colonnes du terminal (l'affichage propre est effectué directement
pg_index_column_has_property(index_oid, column_no, prop_name)booleanteste si une colonne d'un index a une propriété particulière
pg_index_has_property(index_oid, prop_name)booleanteste si un index a une propriété particulière
pg_indexam_has_property(am_oid, prop_name)booleanteste si une méthode d'accès à un index a une propriété particulière
pg_options_to_table(reloptions)setof recordrécupère l'ensemble de paires nom/valeur des options de stockage
pg_tablespace_databases(tablespace_oid)setof oidrécupère l'ensemble des OID des bases qui possèdent des objets dans ce tablespace
pg_tablespace_location(tablespace_oid)textrécupère le chemin complet du répertoire utilisé par le tablespace
pg_typeof(any)regtypeobtient le type de données de toute valeur
to_regnamespace(schema_name)regnamespaceobtient l'OID du schéma indiqué
to_regrole(role_name)regroleobtient l'OID du rôle indiqué
collation for (any)textrécupère le collationnement de l'argument
to_regclass(rel_name)regclassrécupère l'OID de la relation nommée
to_regproc(func_name)regprocrécupère l'OID de la fonction nommée
to_regprocedure(func_name)regprocedurerécupère l'OID de la fonction nommée
to_regoper(operator_name)regoperrécupère l'OID de l'opérateur nommé
to_regoperator(operator_name)regoperatorrécupère l'OID de l'opérateur nommé
to_regtype(type_name)regtyperécupère l'OID du type nommé

format_type renvoie le nom SQL d'un type de données identifié par son OID de type et éventuellement un modificateur de type. On passe NULL pour le modificateur de type si aucun modificateur spécifique n'est connu.

pg_get_keywords renvoie un ensemble d'enregistrements décrivant les mots-clés SQL reconnus par le serveur. La colonne word contient le mot-clé. La colonne catcode contient un code de catégorie : U pour non réservé, C pour nom de colonne, T pour nom d'un type ou d'une fonction et R pour réservé. La colonne catdesc contient une chaîne pouvant être traduite décrivant la catégorie.

pg_get_constraintdef, pg_get_indexdef, pg_get_ruledef, pg_get_statisticsobjdef et pg_get_triggerdef reconstruisent respectivement la commande de création d'une contrainte, d'un index, d'une règle, d'un objet de statistiques étendu ou d'un déclencheur. (Il s'agit d'une reconstruction décompilée, pas du texte original de la commande.) pg_get_expr décompile la forme interne d'une expression individuelle, comme la valeur par défaut d'une colonne. Cela peut être utile pour examiner le contenu des catalogues système. Si l'expression contient des variables, spécifiez l'OID de la relation à laquelle elles font référence dans le second paramètre ; si aucune variable n'est attendue, zéro est suffisant. pg_get_viewdef reconstruit la requête SELECT qui définit une vue. La plupart de ces fonctions existent en deux versions, l'une d'elles permettant, optionnellement, d'« afficher joliment » le résultat. Ce format est plus lisible, mais il est probable que les futures versions de PostgreSQL continuent d'interpréter le format par défaut actuel de la même façon ; la version « jolie » doit être évitée dans les sauvegardes. Passer false pour le paramètre de « jolie » sortie conduit au même résultat que la variante sans ce paramètre.

pg_get_functiondef renvoie une instruction CREATE OR REPLACE FUNCTION complète pour une fonction. pg_get_function_arguments renvoie une liste des arguments d'une fonction, de la façon dont elle apparaîtrait dans CREATE FUNCTION. pg_get_function_result renvoie de façon similaire la clause RETURNS appropriée pour la fonction. pg_get_function_identity_arguments renvoie la liste d'arguments nécessaire pour identifier une fonction, dans la forme qu'elle devrait avoir pour faire partie d'un ALTER FUNCTION, par exemple. Cette forme omet les valeurs par défaut.

pg_get_serial_sequence renvoie le nom de la séquence associée à une colonne ou NULL si aucune séquence n'est associée à la colonne. Si la colonne est une colonne d'identité, la séquence associée est la séquence créée en interne pour la colonne d'identité. Pour les colonnes créées en utilisant un des types serial (serial, smallserial, bigserial), il s'agit de la séquence créée pour la définition de la colonne serial. Dans ce dernier cas, cette association peut être modifiée ou supprimée avec ALTER SEQUENCE OWNED BY. (La fonction devrait probablement avoir été appelée pg_get_owned_sequence ; son nom actuel reflète le fait qu'elle a été utilisée avec une colonne serial ou bigserial.) Le premier argument en entrée est un nom de table, éventuellement qualifié du schéma. Le second paramètre est un nom de colonne. Comme le premier paramètre peut contenir le nom du schéma et de la table, il n'est pas traité comme un identifiant entre guillemets doubles, ce qui signifie qu'il est converti en minuscules par défaut, alors que le second paramètre, simple nom de colonne, est traité comme s'il était entre guillemets doubles et sa casse est préservée. La fonction renvoie une valeur convenablement formatée pour être traitée par les fonctions de traitement des séquences (voir Section 9.16). Une utilisation typique correspond à la lecture de la valeur actuelle d'une séquence pour une colonne d'identité ou pour une colonne de type serial. Par exemple :

SELECT currval(pg_get_serial_sequence('unetable', 'id'));
    

pg_get_userbyid récupère le nom d'un rôle d'après son OID.

pg_index_column_has_property, pg_index_has_property et pg_indexam_has_property indiquent si la colonne d'index, l'index ou la méthode d'accès à l'index possède la propriété nommée. NULL est renvoyé si le nom de la propriété n'est pas connu ou ne s'applique pas à cet objet particulier ou si l'OID ou le numéro de colonne n'identifie pas un objet valide. Référez-vous à Tableau 9.69 pour les propriétés sur les colonnes, Tableau 9.70 pour les propriétés sur les index et Tableau 9.71 pour les propriétés sur les méthodes d'accès. (Notez que les méthodes d'accès provenant d'extensions peuvent définir des noms de propriété supplémentaires pour leurs index.)

Tableau 9.69. Propriétés des colonnes d'index

NomDescription
ascEst-ce que la colonne trie en ordre ascendant pour un parcours en avant ?
descEst-ce que la colonne trie en ordre descendant pour un parcours en avant ?
nulls_firstEst-ce que la colonne trie les valeurs NULL en premier pour un parcours en avant ?
nulls_lastEst-ce que la colonne trie les valeurs NULL en dernier pour un parcours en avant ?
orderableEst-ce que la colonne possède un ordre de tri défini ?
distance_orderableLa colonne peut-elle être parcourue dans l'ordre par un opérateur « distance », par exemple ORDER BY col <-> constante ?
returnableLa valeur de la colonne peut-elle être renvoyée par un parcours d'index seul ?
search_arrayLa colonne supporte-t-elle nativement les recherches du type col = ANY(array) ?
search_nullsEst-ce que la colonne supporte les recherches IS NULL et IS NOT NULL ?

Tableau 9.70. Propriétés des index

NomDescription
clusterableCet index peut-il être utilisé dans une commande CLUSTER ?
index_scanCet index supporte-t-il les parcours simples (non bitmaps) ?
bitmap_scanL'index supporte-t-il les parcours bitmap ?
backward_scanLa direction du parcours peut-elle être modifiée en cours de parcours ? (pour supporter FETCH BACKWARD sur un curseur sans avoir besoin de matérialisation)

Tableau 9.71. Propriétés des méthodes d'accès aux index

NomDescription
can_orderLa méthode d'accès supporte-t-elle ASC, DESC et les mots-clés relatifs dans CREATE INDEX ?
can_uniqueLa méthode d'accès supporte-t-elle les index uniques ?
can_multi_colLa méthode d'accès supporte-t-elle les index avec plusieurs colonnes ?
can_excludeLa méthode d'accès supporte-t-elle les contraintes d'exclusion ?
can_includeLa méthode d'accès supporte-t-elle la clause INCLUDE de CREATE INDEX ?

pg_options_to_table renvoie l'ensemble de paires nom/valeur des options de stockage (nom_option/valeur_option) quand lui est fourni pg_class.reloptions ou pg_attribute.attoptions.

pg_tablespace_databases autorise l'examen d'un tablespace. Il renvoie l'ensemble des OID des bases qui possèdent des objets stockés dans le tablespace. Si la fonction renvoie une ligne, le tablespace n'est pas vide et ne peut pas être supprimé. Pour afficher les objets spécifiques peuplant le tablespace, il est nécessaire de se connecter aux bases identifiées par pg_tablespace_databases et de requêter le catalogue pg_class.

pg_typeof renvoie l'OID du type de données de la valeur qui lui est passée. Ceci est utile pour dépanner ou pour construire dynamiquement des requêtes SQL. La fonction est déclarée comme renvoyant regtype, qui est un type d'alias d'OID (voir Section 8.19) ; cela signifie que c'est la même chose qu'un OID pour un bit de comparaison, mais que cela s'affiche comme un nom de type. Par exemple :

SELECT pg_typeof(33);

 pg_typeof
-----------
 integer
(1 row)

SELECT typlen FROM pg_type WHERE oid = pg_typeof(33);
 typlen
--------
      4
(1 row)
    

L'expression collation for renvoie le collationnement de la valeur qui lui est fournie. Par exemple :

SELECT collation for (description) FROM pg_description LIMIT 1;
 pg_collation_for
------------------
 "default"
(1 row)

SELECT collation for ('foo' COLLATE "de_DE");
 pg_collation_for
------------------
 "de_DE"
(1 row)
    

La valeur en retour peut être entre guillemets et qualifiée d'un schéma. Si aucun collationnement n'est retrouvé à partir de l'expression de l'argument, une valeur NULL est renvoyée. Si le type de l'argument n'est pas affecté par un collationnement, une erreur est renvoyée.

Les fonctions to_regclass, to_regproc, to_regprocedure, to_regoper, to_regoperator, to_regtype to_regnamespace et to_regrole traduisent les noms de relation, fonction, opérateur, type, schéma et rôle (en tant que text) en objets de type, respectivement, regclass, regproc, regprocedure, regoper, regoperator et regtype, regnamespace et regrole. Ces fonctions diffèrent d'une conversion à partir du texte dans le sens où elles n'acceptent pas un OID numérique, et qu'elles renvoient NULL plutôt qu'une erreur si le nom n'est pas trouvé (ou, pour to_regproc et to_regoper, si le nom donné correspond à plusieurs objets).

Tableau 9.72 liste les fonctions relatives à l'identification et l'adressage des objets de la base de données.

Tableau 9.72. Fonctions d'information et d'adressage des objets

NomType de retourDescription
pg_describe_object(classid oid, objid oid, objsubid integer)textrécupère la description d'un objet de la base de données
pg_identify_object(classid oid, objid oid, objsubid integer)type text, schema text, name text, identity textrécupère les informations d'identification d'un objet de la base de données
pg_identify_object_as_address(classid oid, objid oid, objsubid integer)type text, object_names text[], object_args text[]récupère la représentation externe de l'adresse d'un objet de la base de données
pg_get_object_address(type text, object_names text[], object_args text[])classid oid, objid oid, objsubid integerobtient l'adresse d'un objet d'une base de données à partir de sa représentation externe

La fonction pg_describe_object renvoie une description textuelle d'un objet de la base de données spécifié par l'OID du catalogue, son propre OID et de l'OID de son sous-objet (tel qu'un numéro de colonne au sein d'une table ; l'identifiant du sous-objet vaut zéro lors de la référence à un objet complet). La description est destinée à être lisible par un être humain, et peut être interprétée en fonction de la configuration du serveur. Ceci est utile pour déterminer l'identité d'un objet tel qu'il est stocké dans le catalogue pg_depend

pg_identify_object renvoie une ligne contenant assez d'informations pour identifier de manière unique l'objet de la base de données spécifié par l'OID de son catalogue, son propre OID et l'OID de son sous-objet. Cette information est destinée à être lisible par une machine, et n'est jamais interprétée. type identifie le type d'objet de la base de données ; schema est le nom du schéma dans lequel se situe l'objet (ou NULL pour les types d'objets qui ne sont pas affectés à des schémas) ; name est le nom de l'objet, si nécessaire entre guillemets, si le nom (avec le nom du schéma dans les cas pertinents) est suffisant à identifier de façon unique l'objet, sinon NULL ; identity est l'identité complète de l'objet, avec le format précis dépendant du type de l'objet, et chaque nom à l'intérieur du format étant qualifié par un schéma et entre guillemets si nécessaire.

pg_identify_object_as_address renvoie une ligne contenant assez d'informations pour identifier de manière unique l'objet de la base de données spécifié par l'OID de son catalogue, son propre OID et l'OID de son sous-objet. L'information retournée est indépendante du serveur actuel, c'est-à-dire qu'elle pourrait être utilisée pour identifier un objet nommé de manière identique sur un autre serveur. type identifie le type de l'objet de la base de données ; object_names et object_args sont des tableaux de texte qui forment ensemble une référence sur l'objet. Ces trois valeurs peuvent être passées en paramètres à la fonction pg_get_object_address pour obtenir l'adresse interne de l'objet. Cette fonction est l'inverse de pg_get_object_address.

pg_get_object_address renvoie une ligne contenant assez d'informations pour identifier de manière unique l'objet de la base de données spécifié par son type et ses tableaux de nom et d'argument. Les valeurs retournées sont celles qui seraient utilisées dans les catalogues système, telles que pg_depend et peuvent être passées à d'autres fonctions systèmes comme pg_identify_object ou pg_describe_object. classid est l'OID du catalogue système contenant l'objet ; objid est l'OID de l'objet lui-même, et objsubid est l'OID du sous-objet, ou zéro si non applicable. Cette fonction est l'inverse de pg_identify_object_as_address.

Les fonctions affichées dans Tableau 9.73 extraient les commentaires stockés précédemment avec la commande COMMENT. Une valeur NULL est renvoyée si aucun commentaire ne correspond aux paramètres donnés.

Tableau 9.73. Fonctions d'informations sur les commentaires

NomType de retourDescription
col_description(table_oid, column_number)textrécupère le commentaire d'une colonne de la table
obj_description (object_oid, catalog_name)textrécupère le commentaire d'un objet de la base de données
obj_description(object_oid)textrécupère le commentaire d'un objet de la base de données (obsolète)
shobj_description(object_oid, catalog_name)textrécupère le commentaire d'un objet partagé de la base de données

col_description renvoie le commentaire d'une colonne de table, la colonne étant précisée par l'OID de la table et son numéro de colonne. obj_description ne peut pas être utilisée pour les colonnes de table, car les colonnes n'ont pas d'OID propre.

La forme à deux paramètres de obj_description renvoie le commentaire d'un objet de la base de données, précisé par son OID et le nom du catalogue système le contenant. Par exemple, obj_description(123456,'pg_class') récupère le commentaire pour la table d'OID 123456. La forme à un paramètre de obj_description ne requiert que l'OID de l'objet. Elle est maintenant obsolète, car il n'existe aucune garantie que les OID soient uniques au travers des différents catalogues système ; un mauvais commentaire peut alors être renvoyé.

shobj_description est utilisé comme obj_description, mais pour les commentaires des objets partagés. Certains catalogues système sont globaux à toutes les bases de données à l'intérieur de chaque cluster et les descriptions des objets imbriqués sont stockées globalement.

Les fonctions présentées dans Tableau 9.74 remontent à l'utilisateur des informations de transaction de niveau interne au serveur. L'usage principal de ces fonctions est de déterminer les transactions commitées entre deux instantanés (« snapshots »).

Tableau 9.74. ID de transaction et instantanés

NomType retourDescription
txid_current()bigintrécupère l'ID de transaction courant, en assignant un nouvel ID si la transaction courante n'en a pas
txid_current_if_assigned()bigintcomme txid_current(), mais retourne NULL plutôt qu'assigner un nouvel identifiant de transaction si aucun n'est déjà assigné
txid_current_snapshot()txid_snapshotrécupère l'instantané courant
txid_snapshot_xip(txid_snapshot)setof bigintrécupère l'ID de la transaction en cours dans l'instantané
txid_snapshot_xmax(txid_snapshot)bigintrécupère le xmax de l'instantané
txid_snapshot_xmin(txid_snapshot)bigintrécupère le xmin de l'instantané
txid_visible_in_snapshot(bigint, txid_snapshot)booleanl'ID de transaction est-il visible dans l'instantané ? (ne pas utiliser les identifiants de sous-transactions)
txid_status(bigint)textRenvoie le statut de la transaction fournie - validée, annulée, en cours, ou NULL si l'identifiant de transaction est trop ancien

Le type interne ID de transaction (xid) est sur 32 bits. Il boucle donc tous les 4 milliards de transactions. Cependant, ces fonctions exportent au format 64 bits, étendu par un compteur « epoch », de façon à éviter tout cycle sur la durée de vie de l'installation. Le type de données utilisé par ces fonctions, txid_snapshot, stocke l'information de visibilité des ID de transaction à un instant particulier. Ces composants sont décrits dans Tableau 9.75.

Tableau 9.75. Composants de l'instantané

NomDescription
xmin ID de transaction (txid) le plus ancien encore actif. Toutes les transactions plus anciennes sont soit commitées et visibles, soit annulées et mortes.
xmax Premier txid non encore assigné. Tous les txids plus grands ou égaux à celui-ci ne sont pas encore démarrés à ce moment de l'instantané, et donc invisibles.
xip_list Active les identifiants de transactions (txids) au moment de la prise de l'image. La liste inclut seulement les identifiants actifs entre xmin et xmax ; il pourrait y avoir des identifiants plus gros que xmax. Un identifiant qui est xmin <= txid < xmax et qui n'est pas dans cette liste est déjà terminé au moment de la prise de l'image, et du coup est soit visible, soit mort, suivant son statut de validation. La liste n'inclut pas les identifiants de transactions des sous-transactions.

La représentation textuelle du txid_snapshot est xmin:xmax:xip_list. Ainsi 10:20:10,14,15 signifie xmin=10, xmax=20, xip_list=10, 14, 15.

txid_status(bigint) renvoie le statut de validation d'une transaction récente. Des applications peuvent l'utiliser pour déterminer si une transaction a été validée ou annulée quand l'application et le serveur de base de données sont déconnectés alors qu'un COMMIT est en cours. Le statut d'une transaction sera affiché comme in progress, committed, ou aborted, sous réserve que la transaction soit suffisamment récente pour que le système ait gardé le statut du commit de cette transaction. Si elle est trop ancienne pour qu'aucune référence à cette transaction ne survive dans le système et que l'information du statut ait été supprimée, cette fonction renverra NULL. Veuillez notez que les transactions préparées sont affichées comme in progress; les applications doivent vérifier pg_prepared_xacts si elle ont besoin de savoir si l'identifiant de transaction est une transaction préparée.

Les fonctions décrites dans le Tableau 9.76 fournissent des informations à propos des transactions déjà validées. Ces fonctions donnent principalement des informations sur le moment où elles ont été validées. Elles fournissent seulement des données utiles lorsque l'option de configuration track_commit_timestamp est activée et seulement pour les transactions qui ont été validées après son activation.

Tableau 9.76. Informations sur les transactions validées

NomType retourDescription
pg_xact_commit_timestamp(xid) timestamp with time zonerécupère l'horodatage de la validation d'une transaction
pg_last_committed_xact() xid xid, timestamp timestamp with time zonerécupère l'ID de transaction et l'horodatage de validation de la dernière transaction validée

Les fonctions montrées dans Tableau 9.77 affichent des informations initialisées lors de l'opération réalisée par la commande initdb, telles que la version du catalogue. Elles affichent aussi des informations sur la journalisation et le traitement des checkpoints. Cette information est valable pour toute l'instance, et n'est donc pas spécifique à une base de données. Elles fournissent à peu près les mêmes informations que pg_controldata et en s'informant auprès de la même source de données, mais dans une forme convenant mieux à des fonctions SQL.

Tableau 9.77. Fonctions des données de contrôle

NomType en retourDescription
pg_control_checkpoint() record Renvoie des informations sur l'état actuel du checkpoint.
pg_control_system() record Renvoie des informations sur l'état actuel du fichier controldata.
pg_control_init() record Renvoie des informations sur l'état d'initialisation de l'instance.
pg_control_recovery() record Renvoie des informations sur l'état de restauration.

pg_control_checkpoint renvoie un enregistrement, dont les colonnes sont décrites dans Tableau 9.78

Tableau 9.78. Colonnes de pg_control_checkpoint

Nom de colonneType de données
checkpoint_lsnpg_lsn
redo_lsnpg_lsn
redo_wal_filetext
timeline_idinteger
prev_timeline_idinteger
full_page_writesboolean
next_xidtext
next_oidoid
next_multixact_idxid
next_multi_offsetxid
oldest_xidxid
oldest_xid_dbidoid
oldest_active_xidxid
oldest_multi_xidxid
oldest_multi_dbidoid
oldest_commit_ts_xidxid
newest_commit_ts_xidxid
checkpoint_timetimestamp with time zone

pg_control_system renvoie un enregistrement, détaillé dans Tableau 9.79.

Tableau 9.79. Colonnes de pg_control_system

Nom de colonneType de données
pg_control_versioninteger
catalog_version_nointeger
system_identifierbigint
pg_control_last_modifiedtimestamp with time zone

pg_control_init renvoie un enregistrement, détaillé dans Tableau 9.80.

Tableau 9.80. Colonnes de pg_control_init

Nom de la colonneType de données
max_data_alignmentinteger
database_block_sizeinteger
blocks_per_segmentinteger
wal_block_sizeinteger
bytes_per_wal_segmentinteger
max_identifier_lengthinteger
max_index_columnsinteger
max_toast_chunk_sizeinteger
large_object_chunk_sizeinteger
float4_pass_by_valueboolean
float8_pass_by_valueboolean
data_page_checksum_versioninteger

pg_control_recovery renvoie un enregistrement, montré dans Tableau 9.81

Tableau 9.81. Colonnes de pg_control_recovery

Nom de la colonneType de données
min_recovery_end_lsnpg_lsn
min_recovery_end_timelineinteger
backup_start_lsnpg_lsn
backup_end_lsnpg_lsn
end_of_backup_record_requiredboolean