In che modo search_path influenza la risoluzione dell’identificatore e lo “schema corrente”

È ansible definire in quale schema vengono create nuove tabelle di default? (Riferito da “nomi di tabelle non qualificati”.)

Ho visto alcuni dettagli sull’utilizzo del “percorso di ricerca” in Postgres, ma penso che funzioni solo durante il recupero dei dati, non la creazione.

Ho un sacco di script SQL, che creano molte tabelle. Invece di modificare gli script, voglio impostare il database per creare tabelle in uno schema specifico per impostazione predefinita – quando hanno nomi non qualificati.

È ansible?

Il percorso di ricerca è davvero quello che vuoi:

% create schema blarg; % set search_path to blarg; % create table foo (id int); % \d List of relations Schema | Name | Type | Owner --------+------+-------+------- blarg | foo | table | pgsql 

Qual è il percorso di ricerca?

Per documentazione:

[…] le tabelle sono spesso indicate da nomi non qualificati, che consistono solo del nome della tabella. Il sistema determina quale tabella si intende seguendo un percorso di ricerca, che è un elenco di schemi da cercare .

Grassetto enfasi mio. Questo spiega la risoluzione dell’identificatore e lo “schema corrente” è, per documentazione:

Il primo schema nominato nel percorso di ricerca è chiamato schema corrente . Oltre ad essere il primo schema cercato, è anche lo schema in cui verranno create nuove tabelle se il comando CREATE TABLE non specifica un nome schema.

Grassetto enfasi mio. Gli schemi di sistema pg_temp (schema per oggetti temporanei della sessione corrente) e pg_catalog sono automaticamente parte del percorso di ricerca e ricercati per primi , in questo ordine. Per documentazione:

pg_catalog è sempre efficacemente parte del percorso di ricerca. Se non è nominato esplicitamente nel percorso, viene cercato implicitamente prima di cercare gli schemi del percorso. Ciò garantisce che i nomi predefiniti siano sempre disponibili. Tuttavia, puoi posizionare esplicitamente pg_catalog alla fine del tuo percorso di ricerca se preferisci che i nomi definiti dall’utente sostituiscano i nomi predefiniti.

Enfasi grassetto come da originale. E pg_temp viene prima, a meno che non sia messo in una posizione diversa.

Come impostarlo?

Sono disponibili varie opzioni per impostare la variabile di runtime search_path .

  1. Imposta un valore predefinito per tutto il cluster per tutti i ruoli in tutti i database in postgresql.conf (e ricarica). Attento con quello!

     search_path = 'blarg,public' 

    Il valore predefinito spedito per questa impostazione è:

     search_path = "$user",public 

    Il primo elemento specifica che deve essere cercato uno schema con lo stesso nome dell’utente corrente. Se non esiste uno schema di questo tipo, la voce viene ignorata.

  2. Impostalo come predefinito per un database :

     ALTER DATABASE test SET search_path = blarg,public; 
  3. Impostalo come predefinito per il ruolo con cui ti colleghi (efficace a livello di cluster):

     ALTER ROLE foo SET search_path = blarg,public; 
  4. O anche (spesso meglio!) Come predefinito per il ruolo solo in un determinato database :

     ALTER ROLE foo IN DATABASE test SET search_path = blarg,public; 
  5. Scrivi il comando nella parte superiore del tuo script (o eseguilo in qualsiasi punto della tua sessione :

     SET search_path = blarg,public; 
  6. Impostare un percorso di ricerca specifico per l’ ambito di una funzione (per essere al sicuro da utenti malintenzionati con privilegi sufficienti). SECURITY DEFINER informazioni su Scrittura SECURITY DEFINER Funzioni in modo sicuro nel manuale.

 CREATE FUNCTION foo() RETURNS void AS $func$ BEGIN -- do stuff END $func$ LANGUAGE plpgsql SECURITY DEFINER SET search_path=blarg,public,pg_temp ; 

Il numero più alto nella mia lista supera il numero più basso.
Il manuale ha ancora più modi , come l’impostazione di variabili d’ambiente o l’utilizzo di opzioni da riga di comando.

Per vedere l’impostazione corrente:

 SHOW search_path; 

Per resettarlo :

 RESET search_path; 

Per documentazione :

Il valore predefinito è definito come il valore che il parametro avrebbe avuto se non fosse mai stato impostato alcun SET per la sessione corrente.