Date: Sun, 28 Dec 2025 20:38:50 -0500
User-Agent: K-9 Mail for Android
MIME-Version: 1.0
Content-Type: text/plain;
 charset=utf-8
Content-Transfer-Encoding: quoted-printable
Subject: Combining metavariables and table names
To: PostgreSQL Users Mailing List <pgsql-general@lists.postgresql.org>
From: H <agents@meddatainc.com>
Message-ID: <8254F175-871C-4678-8D28-A67E09B099A6@meddatainc.com>
UI-OutboundReport: notjunk:1;M01:P0:K4S2hFNq6A0=;5L3MG+6QJxkRNDUf6jljbm6YRX+
 tqyg5Wk0ehK0zj/dZlumJw+Pmu0ENj/VpWOQFxeC3RSlyhbaI+oGZPyf9riTP4nhm97j6NhOY
 cXA7FcUn0OwJcbC+1fqkH/D9X2y11OXDqkqc9N3bxXVn+W/CEOzI1t7coCvYJJIGog3PLb2Vu
 XacZmiTI7w0+XKXLlDXyQSbHE1TZXxlb2pc9FGh0iCpA+vrQyYi04s0rxw2+eGJyk2/9a5qDu
 6eLbGjaXhG86U4IvIPKaslx8sqRYR18h4u2f4FTVPCZk6gsinD74Wqnl1sCz/50T8IrJBrAsG
 KKRq8oOklKavOov32IRfWUlzRCxuNPpW1AN7LS6jnc0vI/0HMvXMbX3WTNq0kkxYasMHvtwft
 x3U5Ua5vn1Y7h5curudY01vYAvsvjItHNyMu7Qa7TAiBSdFWARIaiEk4Nu9qii7W8WbGx+w47
 xps2Ko8fN0kvzN9JrMyUJvj3QN+SQbKDd8N5OastSinwRxS/+bWqslKmEzlUXvUVxoq79JZ/d
 dunAIoGd3k8ZWREi7aM/Tadg63kwIugsVKu4Jw4hD2UnNEslu/LUsw0uB+lQxAAlupv3uriCf
 zAfqoKBLfaiW/XIxZsY0OSq1Cr4xTEs1ilbKUBccK9e+SFSKSn+yMZCqxTCCJ843nhpSf6Q39
 JezE+kYdrlsjgbJ/NArK2KytpUAcmAao0gRjOALrUJiA18fBZGodORTm2oNtMEommZLySzzwA
 jsJnbH4VVqBwRtx2qLq2JUwVUOlJtvOMz7B1Aq2KsuDkFnAE9J2uNH18ySEiBaJFOcjiw2D6Q
 wUwk2ow2AnV2otX4dL0HGI/vMj3YJuGyX+jZJhsq67jH5HmJeWMEjhXL4fdGvkmfZ7Hi7RoRr
 If7smFvzpwWyizY19PV/E1djnxsaZkxYe1qErERCGfimL6EzJVWcSiJ4RapkEc7nDnDOnD9r2
 Okxiz+fA2ZS7NgdKv5Bvar1RnFXTK8/8KL32dkmku9xO9DeNzT13ny2mJFsLLKbe3Z0NWjebG
 gpAWu1m4HieM+F53TyJ4hif82gpMinTkqDiQsFCJMCExf55U/xTAPC1OmGZABj7fCkVwTiUcy
 EUWwOduJW0xNoDnECd2AOPfE1ndgQrCKgytZjSHWKaJ9LsFQ6CJLFHgtpixB4UftCAwqpZQnB
 QXSD7LpNfZ7rPgVZppCOWIP0zcGVchqFGs1My/ps4cGsoN+N2M8wPbh0D9V8IyaFRWRHwZMXh
 qI3ugxfOk9vjFPPLzBP8F8VEwdyKlGJMAmFnfPxv5i1BXeWmsK1zRkO5RLvlsZSrTXan82VYj
 gkULXHb7/0HZxLRpRLSk2yIABw+FzLhR++ZvqO9BkI2OST7lDFcLw7D7i9jIGnqTVLBkYNNBM
 C4U1RPxjGqZYqJu3ZgzAUpi5MdN4OxQ/7R/m7IYuI72cGOgRMzjWAjL0AIwbWTT91V5/gKTc+
 KfFCNAO733l67NYMfr+KRj4X3cp1ZSpvGTohog63CNWJJp7/F8QUHhUGPWLCg67/MATBKpS17
 6fB5+4e8zUAqkfpY3M8L0pidZKCghrrmG2BSDt8auc3rTfRHBG+n24ULFUK9s1EJPyAiRM6fK
 HRG60mQ/2qOjtRMwemKnJLlKVee9Hc+b7LAEs+KcbXhaZJOd/Ndnxev3BQ6d073XWNRjtsoFB
 pSmzBxOvbM5Ig8FxCh0xo1u8rWBofGtKucSj395yVszIld6Oap5r1/NtqvOK+tpt2SRNiYm9a
 a4iHbAOlwItch
Archived-At: <https://www.postgresql.org/message-id/8254F175-871C-4678-8D28-A67E09B099A6%40meddatainc.com>
Precedence: bulk

Running postgresql 16 on Rocky Linux 9 developing a multi-tenant applicatio=
n where tenants will be represented by individual schemes=2E

I am using the temporal tables extension to save updated/deleted rows and =
am running into a problem=2E During the development I am creating tables in=
 an SQL files for testing=2E In my example, the SQL files also create a ver=
sioning trigger for the example table test using test_history to store upda=
ted/deleted rows using the following statements:

Metavariable to store schema:

\set s t

=2E=2E=2E

CREATE OR REPLACE TRIGGER versioning_trigger
    BEFORE INSERT OR DELETE OR UPDATE
    ON :s=2Etest
    FOR EACH ROW
    EXECUTE PROCEDURE public=2Eversioning('sys_period', ':s=2Etest_history=
', 'true');

Creating the tables works as expected, as does inserting data into them=2E=
 However, updating/deleting rows the following generates the error message =
"ERROR:  schema ":s" does not exist" even though it does exist=2E I have tr=
ied different variations of ':s=2Etest_history' such as :"s"'=2Etest_histor=
y" and a number of other variations, none of which work for the update/dele=
tion of a row=2E

The only way I have gotten it to work is to add the following statement to=
 the SQL file creating tables:

\set stest :s'=2Etest_history'

and then to reference it like:

CREATE OR REPLACE TRIGGER versioning_trigger
    BEFORE INSERT OR DELETE OR UPDATE
    ON :s=2Etest
    FOR EACH ROW
    EXECUTE PROCEDURE public=2Eversioning('sys_period', :"stest", 'true')

Note the placement of the colon and the use of trouble quotes=2E

I have a feeling I might be missing how to use the combination of a metava=
riable and a table name when used in an argument to a procedure=2E

Even though I gotten it to work, what would the correct usage be?

Thanks=2E