sqlpostgresqlfunctiontriggers

How to autofill a field with data from another field in the same table?

I have a table in a Postgres 17 database:

CREATE TABLE customerdevices (
  "deviceID" serial PRIMARY KEY
, "barcode"  varchar(50)
-- more columns
);

barcode will be a text representation of a Code128 barcode.

Essentially, I am trying to do the following:

When a new row is created, I want barcode to be auto-filled by concatenating the auto-generated deviceID with a prefix specific to the table. (I have several different tables that I want to implement this functionality to).

My approach was to create a trigger function and trigger for that:

CREATE OR REPLACE FUNCTION generate_barcode(pref VARCHAR, id INTEGER)
RETURNS TRIGGER
LANGUAGE plpgsql
CALLED ON NULL INPUT
AS $$
BEGIN
   NEW.barcode := pref || id;
   RETURN NEW;
END;
$$;

CREATE TRIGGER autoGenerateCustomerDeviceBarcode
BEFORE INSERT ON customerdevices
FOR EACH ROW
EXECUTE FUNCTION
    generate_barcode('C', NEW.deviceID);

However, when I run the script, it outputs an error:

unterminated dollar-quoted string at or near "$$ BEGIN"

I have tried multiple iterations of the function but I always get the same error. I am fairly new to Postgres, more familiar with SQLite and Oracle.

I put my code though Chat-GPT and as per its suggestions I have:

  1. Verified that plpgsql is installed and enabled in the database
  2. Verified that I am connected to the correct database
  3. Manually changed the line endings of my files from CRLF to LF because according to Chat-GPT "The presence of CRLF line endings can sometimes cause issues in PostgreSQL, especially when executed from environments that interpret line endings differently (e.g., Windows vs. Linux)." 4.Verified that I have installed the latest version of Postgresq

Still, after doing all those things, I still get the same error. At this point, I am thinking that it might be easier to create a barcode table and consolidate all of the barcodes from the different tables together, but if there is indeed a way to do it like this, I would prefer it.

Solution

  • Trigger functions don't work like this. The manual:

    A trigger function is created with the CREATE FUNCTION command, declaring it as a function with no arguments and a return type of trigger (for data change triggers)

    Bold emphasis mine.

    My first advice (beside reading the manual): Drop the functionally dependent column barcode completely. You can easily generate it on the fly with pref || id. Or create a view that adds it.

    If you insist on the redundant column, and the content is supposed to never change, use a generated column instead. Simpler, cheaper. See:

    If you insist on both column and trigger, I recommend to write a separate trigger function for each table. Simpler and faster execution.

    If I failed to convince you of a better approach, you can pass arguments to a trigger function with the special variable TG_ARGV. Here is an example:

    Related: