postgresql - Implementing get or create function

Question

I need to create a function in PostgreSQL using the pl/pgSQL language. I have the following four tables:

CREATE TABLE person (
     id serial PRIMARY KEY,
     name text NOT NULL,
     town_id integer NOT NULL,
     country_id integer NOT NULL,
     FOREIGN KEY (town_id) references towns (id),
     FOREIGN KEY (country_id) references countries (id)
);

CREATE TABLE towns (
    id serial PRIMARY KEY,
    name text UNIQUE NOT NULL
);

CREATE TABLE countries (
    id serial PRIMARY KEY,
    name text UNIQUE NOT NULL
);

Now I want to create a function that receives a person record and normalizes this into these tables. The example record is:

 name: Jakob
 town: Los Angeles
 country: USA

In other words, I want to store unique town names in the table towns, and I want to store unique county names in the table countries. I would thus like to implement a "get-or-create" that checks whether a value already exists, and if yes, fetch the primary key. Otherwise, I want to insert the town/country based on the provided name, and fetch the id. Then insert this in the person table.

How would this work as a function in the pl/pgSQL language?

Answer 1

等待大神答复