CODEWITHSUNDEEP
postgresqlfunctiontriggersforeign-keys
Filipe Pina
Filipe Pina

Reputation: 2239

Follow foreign key in function in Postgresql

I have been trying to find this online but I'm new to postgresql and I probably don't know enough to quickly filter out search results..

I have declared the following tables:

CREATE SEQUENCE country_id_seq;

CREATE TABLE country
(
  id integer NOT NULL DEFAULT nextval('country_id_seq'),
  name character varying(40) NOT NULL,
  CONSTRAINT country_pkey PRIMARY KEY (id)
);

CREATE SEQUENCE user_id_seq;

CREATE TABLE user
(
  id integer NOT NULL DEFAULT nextval('user_id_seq'),
  first_name character varying(40) NOT NULL,
  last_name character varying(40) NOT NULL,
  country_id integer NOT NULL,
  CONSTRAINT user_pkey PRIMARY KEY (id),
  CONSTRAINT user_fk_country_id FOREIGN KEY (country_id)
      REFERENCES country (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED
);

Now I want to create a trigger function on USER that (for example purposes) raises an exception with country name

CREATE FUNCTION trigger_user_deny() RETURNS trigger AS $$
BEGIN
    RAISE EXCEPTION 'STUFF %', NEW.country_id.name;
  RETURN NEW;
END
$$
LANGUAGE plpgsql;

CREATE TRIGGER trigger_user_deny_insert
  BEFORE INSERT
  ON customer
  FOR EACH ROW
  EXECUTE PROCEDURE trigger_user_deny();

So basically my question is: how do I do something like NEW.country_id.name (which doesn't work and the purpose is to select country.name from country where id=NEW.country_id)?

Upvotes: 0

Views: 860

Answers (1)

Tom-db
Tom-db

Reputation: 6868

The question is not very clear, but I think you want the name of the country which has id = "user".country_id. In this case you have to query the table country with the new value in the table user:

CREATE or replace FUNCTION trigger_user_deny() RETURNS trigger AS $$
BEGIN

    RAISE EXCEPTION 'STUFF %', name from country WHERE id = NEW.country_id;
  RETURN NEW;
END
$$
LANGUAGE plpgsql;

PS: if you name a table user, which is PostgreSQL keyword, you must always double quote it in queries and other sql statements:

CREATE TABLE "user" ....

Upvotes: 1

Related Questions