sql - How to autofill a field with data from another field in the same table? - Stack Overflow

admin管理员组

文章数量:1334335

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.

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.

• sql
• postgresql
• function
• triggers

Share Improve this question Follow asked Nov 20, 2024 at 12:44 AudaciousTAudaciousT 1311 silver badge22 bronze badges

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

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:

• Computed / calculated / virtual / derived / generated columns in PostgreSQL

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:

• Trigger with dynamic field name

Related:

• Column name as argument for a trigger function

本文标签： sqlHow to autofill a field with data from another field in the same tableStack Overflow