Postgres Trigger function syntax error near BEGIN

Question

I use PostgreSQL 12

I have the following two tables:

table_a:

table_a_id   |   version 
1            |     v1
2            |     v1
3            |     v1
4            |     v2
5            |     v2
6            |     v2

table_b:

table_b_id   |   version   |  table_a_id
1            |     v1      |     1  
2            |     v1      |     2         
3            |     v1      |     3  
4            |     v2      |     4  
5            |     v2      |     5  
6            |     v2      |     6

table_b must reference same version table_a_id i.e The below data is valid entry because table_a_id -> 1 belongs to version 'v1'

table_b_id   |   version   |  table_a_id
1            |     v1      |     1

But the below data is invalid entry because table_a_id -> 4 belongs to version 'v2'

table_b_id   |   version   |  table_a_id
1            |     v1      |     4

I am new to Postgres trigger functions

I have created the following trigger function to validate ON BEFORE INSERT OR UPDATE to table_b:

CREATE FUNCTION version_check() 
    RETURNS TRIGGER AS 
$BODY$
  BEGIN
    IF NOT EXISTS (
      SELECT
        *
      FROM
        "table_a"
      WHERE
        "table_a"."table_a_id" = NEW."table_a_id"
      AND
        "table_a"."version" = NEW."version";
    )
    THEN
      RAISE EXCEPTION 'table_a and table_b Version do not match';
    END IF;

    RETURN NEW;
  END;

$BODY$
LANGUAGE plpgsql;

CREATE TRIGGER "version_check" BEFORE INSERT OR UPDATE ON "table_b"
  FOR EACH ROW EXECUTE PROCEDURE version_check();

I am getting the following error on saving the Trigger function in pgAdmin 4

ERROR: syntax error at or near "BEGIN"
LINE 8: BEGIN
^

Am i doing any syntax error? Also will the above trigger function work fine for my requirement ?

Thanks in advance!

Unrelated to your problem, but: why don't you use a proper foreign key constraint? — user330315
– user330315, Commented Apr 25, 2020 at 15:07
What do u mean by proper foreign key constraint here? Can u please tell in detail? — code-geek
– code-geek, Commented Apr 25, 2020 at 15:21
The BEGIN is at line 4 and the error says it's at line 8. It probably means that the SQL really sent to the server is not what the question shows. — Daniel Vérité
– Daniel Vérité, Commented Apr 25, 2020 at 16:18

user330315 · Accepted Answer · 2020-04-25 15:09:09Z

0

You need to remove the ; in your nested select

IF NOT EXISTS (
  SELECT
    *
  FROM
    "table_a"
  WHERE
    "table_a"."table_a_id" = NEW."table_a_id"
  AND
    "table_a"."version" = NEW."version" --<< no semicolon here
)

Online example

answered Apr 25, 2020 at 15:09

user330315

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

3 Comments

code-geek Over a year ago

I tried with and without semi colon in nested select. But it is not working

user330315 Over a year ago

@Maha: well, the code works (as you can see in my link) - there must be something else you are not telling us

code-geek Over a year ago

Thanks for the link @a_horse_with_no_name ! But it does not work in pgAdmin 4. Do u have any idea on what could be the reason?

code-geek · Accepted Answer · 2020-04-25 16:27:19Z

0

One of the syntax errors is the semi colon ; in nested Select statement

I would like to add one more point that caused error while creating Trigger function is, in pgAdmin 4, you will have to open a new Query tool and execute the trigger function script rather than directly creating a Trigger function from pgAdmin 4 UI.

This solved my problem.

answered Apr 25, 2020 at 16:27

code-geek

4631 gold badge9 silver badges34 bronze badges

Collectives™ on Stack Overflow

Postgres Trigger function syntax error near BEGIN

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related