StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<h3>PostgreSQL 9.1 or later</h3> <p><a href="http://www.postgresql.org/docs/current/interactive/functions-string.html#FUNCTIONS-STRING-OTHER" rel="noreferrer"><code>format()</code></a> has a built-in way to escape identifiers. Simpler than before:</p> <pre><code>CREATE OR REPLACE FUNCTION foo_before() RETURNS trigger AS $func$ BEGIN EXECUTE format('INSERT INTO %I.%I SELECT $1.*' , TG_TABLE_SCHEMA, TG_TABLE_NAME || 'shadow') USING OLD; RETURN OLD; END $func$ LANGUAGE plpgsql; </code></pre> <p><a href="http://sqlfiddle.com/#!17/ff78f/1" rel="noreferrer"><strong>SQL Fiddle.</strong></a><br> Works with a <a href="http://www.postgresql.org/docs/current/interactive/sql-values.html" rel="noreferrer"><code>VALUES</code></a> expression as well.</p> <h3>Major points</h3> <ul> <li>Use <a href="http://www.postgresql.org/docs/current/interactive/functions-string.html#FUNCTIONS-STRING-OTHER" rel="noreferrer">format()</a> or <a href="http://www.postgresql.org/docs/current/interactive/functions-string.html#FUNCTIONS-STRING-OTHER" rel="noreferrer"><code>quote_ident()</code></a> to quote identifiers where necessary and defend against <a href="http://en.wikipedia.org/wiki/Sql_injection" rel="noreferrer">SQL injection</a>.<br> This is <em>necessary</em>, even with your own table names!</li> <li>Schema-qualify the table name. Depending on the current <a href="https://stackoverflow.com/questions/9067335/how-does-the-search-path-influence-identifier-resolution-and-the-current-schema/9067777#9067777"><code>search_path</code> setting</a> a bare table name might otherwise resolve to another table of the same name in a different schema.</li> <li>Use <strong><code>EXECUTE</code></strong> for dynamic DDL statements.</li> <li>Pass <em>values</em> safely with the <strong><code>USING</code></strong> clause.</li> <li>Consult the fine manual on <a href="http://www.postgresql.org/docs/current/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN" rel="noreferrer">Executing Dynamic Commands in plpgsql</a>.</li> <li>Note that<code>RETURN OLD;</code> in the trigger function is required for a trigger <strong><code>BEFORE DELETE</code></strong>. <a href="https://www.postgresql.org/docs/current/static/trigger-definition.html" rel="noreferrer">Details in the manual here.</a></li> </ul> <p>You get the <strong>error message</strong> in your almost successful version because <code>OLD</code> is <strong>not visible</strong> inside <code>EXECUTE</code>. And if you want to concatenate individual values of the decomposed row like you tried, you have to prepare the text representation of every single column with <code>quote_literal()</code> to guarantee valid syntax. You would also have to <em>know</em> column names beforehand to handle them or query the system catalogs - which stands against your idea of having a simple, dynamic trigger function ...</p> <p>My solution avoids all these complications. Also simplified a bit.</p> <h3>PostgreSQL 9.0 or earlier</h3> <p><code>format()</code> is not available, yet, so:</p> <pre><code>CREATE OR REPLACE FUNCTION foo_before() RETURNS trigger AS $func$ BEGIN EXECUTE 'INSERT INTO ' || quote_ident(TG_TABLE_SCHEMA) || '.' || quote_ident(TG_TABLE_NAME || 'shadow') || ' SELECT $1.*' USING OLD; RETURN OLD; END $func$ LANGUAGE plpgsql; </code></pre> <p>Related:</p> <ul> <li><a href="https://stackoverflow.com/questions/7519044/how-to-dynamically-use-tg-table-name-in-postgresql-8-2/7617533#7617533">How to dynamically use TG_TABLE_NAME in PostgreSQL 8.2?</a></li> </ul>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload