Using TG_TABLE_NAME in postgresql trigger function in a SELECT query

Question

I have this plpgsql trigger function

DECLARE
    prev_record daily_data%ROWTYPE;
BEGIN
    SELECT * INTO prev_record
    FROM ONLY TG_TABLE_NAME
    WHERE base = NEW.quote AND quote = 'USDT'
    ORDER BY timestamp DESC
    LIMIT 1;
    
    IF EXISTS prev_record THEN
        NEW.volume_usd := NEW.volume * prev_record.close;
    END IF;
    
    RETURN NEW;
END;

The main motive of this function is to grab the latest value from a table and use that to update the volume_usd of the new value being inserted.

This function is going to be used in many tables thus I used TG_TABLE_NAME in the select statement but it seems to throw error.

ERROR:  relation "tg_table_name" does not exist at character 41
2024-06-06 16:32:57.222 UTC [3253] QUERY:  SELECT *                      FROM ONLY TG_TABLE_NAME
             WHERE base = NEW.quote AND quote = 'USDT'
             ORDER BY timestamp DESC
             LIMIT 1
CONTEXT:  PL/pgSQL function update_volume_usd() line 5 at SQL statement

I have looked at execute function but don't think it would be useful here as I would require storing that

Edit: I have tried using EXECUTE but it doesn't work

CREATE OR REPLACE FUNCTION public.update_volume_usd()
    RETURNS trigger
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE NOT LEAKPROOF
AS $BODY$
DECLARE
    prev_record daily_data%ROWTYPE;
BEGIN
    EXECUTE format('
        SELECT * INTO prev_record
        FROM ONLY %I
        WHERE base = $1 AND quote = ''USDT''
        ORDER BY timestamp DESC
        LIMIT 1
    ', TG_TABLE_NAME) USING NEW.quote;
    
    IF EXISTS prev_record THEN
        NEW.volume_usd := NEW.volume * prev_record.close;
    END IF;
    
    RETURN NEW;
END;
$BODY$;

ERROR:

ERROR:  EXECUTE of SELECT ... INTO is not implemented
HINT:  You might want to use EXECUTE ... INTO or EXECUTE CREATE TABLE ... AS instead.
CONTEXT:  PL/pgSQL function update_volume_usd() line 4 at EXECUTE 

SQL state: 0A000

datatype of prev_record.close is NUMERIC

Answer 1

Generally, identifiers (including table names) cannot be parameterized in plain SQL. You need dynamic SQL with EXECUTE for that.

Also, IF EXISTS prev_record THEN isn't valid syntax. IF FOUND ... would work in its place.

But the logic of this trigger falls apart under concurrent write load. Multiple concurrent transactions will compute incoherent values due to visibility issues. Either use the isolation level SERIALIZABLE, or don't try this magic trick at all and store plain values and do computations across the time series in a query / view / materialized view instead.

That said, here is a valid function definition:

CREATE OR REPLACE FUNCTION public.update_volume_usd()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
DECLARE
   _prev_close numeric;  -- we only need this column
BEGIN
   EXECUTE format(
      $q$
      SELECT close
      FROM   ONLY %I
      WHERE  base = $1        -- compare base to NEW.quote?
      AND    quote = 'USDT'
      ORDER  BY timestamp DESC  -- must be defined NOT NULL !
      LIMIT  1
      $q$, TG_TABLE_NAME)
   USING NEW.quote
   INTO  _prev_close;
   
   IF _prev_close IS NOT NULL THEN
      NEW.volume_usd := NEW.volume * _prev_close;
   END IF;
   
   RETURN NEW;
END
$func$;

Must be used in a BEFORE trigger.

With EXECUTE, the INTO clause can't be nested in the dynamic query, it has to be appended to the EXECUTE command like demonstrated. Details in the manual.

After switching to dynamic SQL, my initial advice to use FOUND is void. The manual:

Note in particular that EXECUTE changes the output of GET DIAGNOSTICS, but does not change FOUND.

In this case, we can also check _prev_close IS NOT NULL - assuming the column is defined NOT NULL.