Clash Royale CLAN TAG#URR8PPP

Reusing identifiers in a trigger function in PostgreSQL

I am using PostgreSQL 9.2.23.
I have a trigger function that has a number of statements that reuse the same identifiers in the format statement. I'll just include one execute statement for brevity.

CREATE OR REPLACE FUNCTION update_lookup_table() RETURNS trigger AS
$BODY$
DECLARE
arg_key text;
arg_desc text;
arg_table text;
BEGIN
arg_key := TG_ARGV[0];
arg_desc := TG_ARGV[1];
arg_table := TG_ARGV[2];

EXECUTE format('DROP TABLE IF EXISTS %s',
quote_ident('temp_' || arg_table));
EXECUTE format('CREATE TEMPORARY TABLE %s(%I text, %I text)',
quote_ident('temp_' || arg_table), arg_key, arg_desc);
EXECUTE format('INSERT INTO %s(%I, %I)
SELECT DISTINCT %I, %I
from staging_staff',
quote_ident('temp_' || arg_table), arg_key, arg_desc,
arg_key, arg_desc);
EXECUTE format('LOCK TABLE %I IN EXCLUSIVE MODE',
arg_table);
EXECUTE format('UPDATE %I
SET %I = %s.%I
FROM %s
WHERE %s.%I = %I.%I',
arg_table,
arg_desc, quote_ident('temp_' || arg_table), arg_desc,
quote_ident('temp_' || arg_table),
quote_ident('temp_' || arg_table), arg_key, arg_table, arg_key);
EXECUTE format('INSERT INTO %I (%I, %I)
SELECT %s.%I, %s.%I
FROM %s
LEFT OUTER JOIN %I ON ( %I.%I = %s.%I )
WHERE %I.%I IS NULL',
arg_table, arg_key, arg_desc,
quote_ident('temp_' || arg_table), arg_key, quote_ident('temp_' || arg_table), arg_desc,
quote_ident('temp_' || arg_table),
arg_table, arg_table, arg_key, quote_ident('temp_' || arg_table), arg_key,
arg_table, arg_key);

RETURN NULL;

END;
$BODY$
LANGUAGE plpgsql;


Here is the trigger for more context:

CREATE Trigger trig_update_staff_code
AFTER INSERT OR UPDATE ON staging_staff
EXECUTE PROCEDURE update_lookup_table('staffgroupcode','staffgroupdescription','staff_group');


Some of the statements I am using are quite long and they end up very silly, with the same 3 identifiers repeated over and over.
As you can see in my format statement I am reusing, the arg_key and arg_desc identifiers twice. Is there a way that I can declare variables and pass them in somehow. Such as?:

EXECUTE format('INSERT INTO temp_$A($B, $C) SELECT DISTINCT $B, $C from staging_staff,
arg_table, arg_key, arg_desc);


I have tried:

DECLARE
...
temp_table text;
BEGIN
...
temp_table:= CONCAT('temp_', arg_table);

INSERT INTO temp_table(arg_key, arg_desc) SELECT DISTINCT arg_key, arg_desc from staging_staff


But PostgreSQL didn't like this syntax. Has anyone else figured a way around this?

Thanks

EDIT: I added all of the statements I am using. As well as took Laurenz's advice and used %s instead of %I for the concatenated table names

2 Answers
2

You can use positional format specifiers to reference an argument by index:

EXECUTE format(
'INSERT INTO %1$I(%2$I, %3$I) SELECT DISTINCT %2$I, %3$I FROM staging_staff',
'temp_' || arg_table, arg_key, arg_desc
);


As Laurenz noted, the input for the %I specifier should be a complete identifier name, so you need to attach your temp_ prefix to the argument instead of embedding it in the format string.

%I

temp_

TG_ARGV is the only way to parameterize a trigger function.

TG_ARGV

I don't really see your problem. Laziness is a virtue in programming because it leads to innovation, but you can take it too far.

As a side remark, the temp_%I in your format won't work well. If the replacement string is not a regular identifier, you will end up with something like temp_"CamelCase", which is not an identifier.

temp_%I

temp_"CamelCase"

Try to use the %s format with an argument of quote_ident('temp_' || arg_table).

%s

quote_ident('temp_' || arg_table)

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.