Reusing identifiers in a trigger function in PostgreSQL

Question

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

Nick Barnes · Accepted Answer · 2018-08-08 08:06:49Z

3

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.

answered Aug 8, 2018 at 8:06

Nick Barnes

21.8k5 gold badges63 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

PhantomSalt Over a year ago

Thanks, this is what I was looking for!

Laurenz Albe · Accepted Answer · 2018-08-08 07:10:10Z

1

TG_ARGV is the only way to parameterize a trigger function.

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.

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

answered Aug 8, 2018 at 7:10

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

1 Comment

PhantomSalt Over a year ago

Thanks for the help. I guess most of the time this isn't a problem as it still functions. I thought that maybe I could make my code a bit DRYer. For example there is one statement that I have in the function, which I didn't include in the original question, but I will add it just to show how long it can get.

Collectives™ on Stack Overflow

Reusing identifiers in a trigger function in PostgreSQL

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related