Can't insert values using pl\pgSQL procedure

Question

I have a problem with my procedure. We have 2 tables : companies and typeofcompanies. Companies have 3 columns ("companyid","companyname","typeofcompany") , typeofcompanies have 2 columns ("typeofcompanyId", "typeofcompany");

This is my code for insert values into companies:

 CREATE OR REPLACE FUNCTION insert_to_companies(c_name VARCHAR(255),toc INTEGER)
      RETURNS VOID AS $$
          DECLARE i integer;
           BEGIN
          FOR i IN SELECT "typeofcompanyId" FROM typeofcompanies LOOP
            IF toc = i THEN insert into public.companies(companyname, typeofcompany) VALUES (c_name,toc);
              END IF;
          END LOOP;
          IF(SELECT companyname FROM companies WHERE companyname = c_name) = NULL THEN insert into public.typeofcompanies(typeofcompany) VALUES (toc);
                                                                             INSERT into public.companies(companyname,typeofcompany) VALUES (c_name,toc);
          END IF;
          END;
    $$ LANGUAGE plpgsql VOLATILE;

So if we haven't typeofcompany we should create it. But when i SELECT insert_to_companies("1",5); i take ERROR: column "1" does not exist. So i think it's very easier problem, but i spent more than 3 hour and can't find problem. Please, i need help....

code for creating table with liquibase

companies:

<databaseChangeLog xmlns = "http://www.liquibase.org/xml/ns/dbchangelog/1.9" xmlns:xsi = "http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog/1.9
                      http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-1.9.xsd">
    <changeSet id="1" author="tiurinvalery@gmail.com" runOnChange="true" >
        <createTable tableName ="companies">
            <column autoIncrement = "true" name = "companyid" type ="BIGINT">
                <constraints primaryKey ="true"></constraints>
            </column>
            <column name="companyname" type="VARCHAR(255)">
            </column>
            <column name="typeofcompany" type="INTEGER">
            </column>
        </createTable>
    </changeSet>
 </databaseChangeLog>

For type of companies:

<databaseChangeLog xmlns = "http://www.liquibase.org/xml/ns/dbchangelog/1.9" xmlns:xsi = "http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog/1.9
                      http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-1.9.xsd"  >
    <changeSet id="5" author="tiurinvalery@gmail.com" runOnChange="true" >
        <createTable tableName ="typeofcompanies">
            <column autoIncrement = "true" name = "typeofcompanyId" type ="BIGINT">
                <constraints nullable="false" primaryKey="true"></constraints>
            </column>
            <column name = "typeofcompany" type="VARCHAR(255)">
                <constraints nullable="false"></constraints>
            </column>
        </createTable>
    </changeSet>
</databaseChangeLog>

Answer 1

Table definitions could look like this:

CREATE TABLE company_type (
   company_type_id serial PRIMARY KEY
 , company_type    text UNIQUE NOT NULL
); 

CREATE TABLE company (
   company_id      serial PRIMARY KEY
 , company         text NOT NULL
 , company_type_id int REFERENCES company_type
);

I made a couple of changes:

• Not using double-quoted CaMeL-case names. Avoid that, if you can.
  • Are PostgreSQL column names case-sensitive?
• Using integer instead of bigint for PK IDs. I seriously doubt you'll exhaust the key space with company types in this century ...
• Using just text. varchar(255) typically is a misunderstanding in Postgres.

The best function (or just query) heavily depends on your use case. DB load, concurrency, exact requirements. If concurrent writes are possible, I suggest this to avoid race conditions:

CREATE OR REPLACE FUNCTION f_insert_to_companies(_c_name text, _toc text)
  RETURNS void AS
$func$
BEGIN
LOOP
   INSERT INTO company(company, company_type_id)
   SELECT _c_name, t.company_type_id
   FROM   company_type t
   WHERE  company_type = _toc;

   EXIT WHEN FOUND;

   -- if type is missing, insert and keep looping    
   INSERT INTO company_type (company_type)
   VALUES (_toc)
   ON     CONFLICT (company_type) DO NOTHING;
END LOOP;
END
$func$ LANGUAGE plpgsql;

dbfiddle here

Related (with detailed explanation):

• Is SELECT or INSERT in a function prone to race conditions?