expenses trigger to find category PK

expenses.sql

CREATE TABLE expenses (
  Id SERIAL PRIMARY KEY NOT NULL,
  TrxDate DATE DEFAULT now(),
  PaymentType VARCHAR(100) NOT NULL,
  Location VARCHAR(100) NOT NULL,
  What VARCHAR(250) NOT NULL,
  CategoryType INTEGER NOT NULL REFERENCES categories (Id),
  Amount NUMERIC(9,2) NOT NULL,
  Description TEXT NULL,
  CONSTRAINT amount_greater_than_zero CHECK (Amount > 0)
);

CREATE TABLE categories (
	Id SERIAL PRIMARY KEY NOT NULL,
	Name VARCHAR(100) NOT NULL UNIQUE
);

CREATE OR REPLACE FUNCTION set_category() RETURNS trigger AS $set_category$
	BEGIN
	  NEW.CategoryType = "SELECT NEW.Id FROM categories WHERE Name LIKE NEW.CategoryType";
		RETURN NEW;
	END;
$set_category$ LANGUAGE plpgsql;

INSERT INTO expenses
VALUES (DEFAULT, now(), 'visa', 'giant eagle', 'groceries', 'Groceries', 22.55, 'odds and ends');

-- This throws a type error on Groceries not being an integer.  Thought maybe a trigger would run before the insert thus replacing the varchar
-- with the categories' primary key.  

function_solution.sql

--first solution is to create a function that finds the primary key from the categories table

CREATE OR REPLACE FUNCTION get_category(categorytype varchar) RETURNS INTEGER AS $$
  SELECT Id FROM categories WHERE Name LIKE categorytype
$$ LANGUAGE SQL;

INSERT INTO expenses
VALUES (DEFAULT, now(), 'visa', 'giant eagle', 'groceries', get_category('Gas Bill'), 22.55, 'odds and ends');