我想創建一個腳本，該腳本將創建一些自定義命名模式，並在其中創建一些表和函式。像這樣：

example.sh文件：

#!/bin/bash

# this is the only place I want to set the schema name
SCH="ex"

export SCH

export PGPASSWORD="*******"

PSQL="psql \
 -X \
 -U postgres \
 -h localhost \
 --single-transaction \
 --echo-all \
 --set SCH=$SCH \
 --set ON_ERROR_STOP=on "

eval $PSQL "-f ./example.sql"

example.sql文件：

DROP SCHEMA IF EXISTS :SCH CASCADE;
CREATE SCHEMA :SCH;

CREATE TABLE :SCH.my_table
(
 id SERIAL PRIMARY KEY,
 my_col text
);

INSERT INTO :SCH.my_table (my_col) VALUES ('abc'), ('def');

CREATE OR REPLACE FUNCTION :SCH.getLast()
RETURNS text AS $$
SELECT my_col FROM :SCH.my_table ORDER BY id DESC LIMIT 1;
$$ LANGUAGE sql STABLE;

SELECT * FROM :SCH.getLast();

它工作正常，直到：

CREATE OR REPLACE FUNCTION :SCH.getLast()
RETURNS text AS $$
SELECT my_col FROM :SCH.my_table ORDER BY id DESC LIMIT 1;
$$ LANGUAGE sql STABLE;

由於函式體是一個文本常量，所以:SCH沒有被模式名稱替換，我們得到一個錯誤：

psql:./example.sql:16: ERROR:  syntax error at or near ":"
LINE 3: SELECT my_col FROM :SCH.my_table ORDER BY id DESC LIMIT 1;

有沒有一種巧妙的方法讓它工作？

解決方法

這可以通過以下方式完成，但似乎應該有更簡單的方法：

CREATE OR REPLACE FUNCTION :SCH.makeFunction(schema_name text)
RETURNS VOID AS $body$
BEGIN
   EXECUTE format('CREATE OR REPLACE FUNCTION %1$s.getLast()
   RETURNS text AS $$
   SELECT my_col FROM %1$s.my_table ORDER BY id DESC LIMIT 1;
   $$ LANGUAGE sql STABLE;', schema_name);
END
$body$ LANGUAGE plpgsql VOLATILE;

SELECT :SCH.makeFunction(:'SCH');

實現此目的的一種有趣方法是使用psql變數來儲存整個函式體，例如

\set body '$$SELECT 1$$'

CREATE FUNCTION bla() RETURNS integer LANGUAGE SQL AS :c;

SELECT bla();
bla 
─────
  1

或者，您可以將整個定義傳遞給變數，然後執行它：

\set function 'CREATE FUNCTION bla() RETURNS integer LANGUAGE SQL AS $$SELECT 1;$$;'

:function

SELECT bla();
bla 
─────
  1

所以，問題是如何將body放入一個合適的變數中。

從psql上面的 9.3 中，有一個\gset命令來救援。我們建構一個查詢，生成函式體作為輸出，然後將其分配給一個psql變數，並像上面一樣使用它（with format()）：

SELECT format('CREATE OR REPLACE FUNCTION %1$s.getLast()
   RETURNS text AS $$
   SELECT my_col FROM %1$s.my_table ORDER BY id DESC LIMIT 1;
   $$ LANGUAGE sql STABLE;', 'test') AS function;

\gset

:function

並做了。

作為使用模式限定符:SCH變數為所有對象添加前綴的替代方法，您可以分配search_path，以便它自動發生。

CREATE SCHEMA :"SCH";
SET search_path TO :"SCH";

在此之後，所有 CREATE 語句和類型查找都發生在:SCH.

關於函式體內的查詢，預設情況下它們仍然使用search_path執行時的任何內容，但這可以通過與SET函式聲明關聯的顯式進行更改。

例如getLast()可以在 SQL 腳本中聲明為：

CREATE OR REPLACE FUNCTION getLast()
RETURNS text AS $$
 SELECT my_col FROM my_table ORDER BY id DESC LIMIT 1;
$$ LANGUAGE sql STABLE SET search_path TO :"SCH";

引用自：https://dba.stackexchange.com/questions/161739