Is it possible to write a postgres function that will handle a many to many join?

Question

I have a job table. I have an industries table. Jobs and industries have a many to many relationship via a join table called industriesjobs. Both tables have uuid is their primary key. My question is two fold. Firstly is it feasible to write two functions to insert data like this? If this is feasible then my second question is how do I express an array of the uuid column type. I'm unsure of the syntax.

CREATE OR REPLACE FUNCTION linkJobToIndustries(jobId uuid, industiresId uuid[]) RETURNS void AS $$
DECLARE
    industryId uuid[];
BEGIN
    FOREACH industryId SLICE 1 IN ARRAY industriesId LOOP
        INSERT INTO industriesjobs (industry_id, job_id) VALUES (industryId, jobId);
    END LOOP;
    RETURN;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION insertJobWithIndistries(orginsation varchar, title varchar, addressId uuid, industryIds uuid[]) RETURNS uuid AS $$
DECLARE
    jobId uuid;
BEGIN
    INSERT INTO jobs ("organisation", "title", "address_id") VALUES (orginsation, title, addressId)  RETURNING id INTO jobId;
    SELECT JobbaLinkJobToIndustries(jobId, industryIds);
END;
$$ LANGUAGE plpgsql;

SELECT jobId FROM insertJobWithIndistries(
    'Acme Inc'::varchar, 
    'Bomb Tester'::varchar, 
    '0030cfb3-1a03-4c5a-9afa-6b69376abe2e',
    { 19c2e0ee-acd5-48b2-9fac-077ad4d49b19, 21f8ffb7-e155-4c8f-acf0-9e991325784, 28c18acd-99ba-46ac-a2dc-59ce952eecf2 }
);

Thanks in advance.

score 2 · Accepted Answer · edited May 23 '17 at 12:24

Key to a solution are the function unnest() to (per documentation):

expand an array to a set of rows

And a data-modifying CTE.

A simple query does the job:

WITH ins_job AS (
   INSERT INTO jobs (organisation, title, address_id)
   SELECT 'Acme Inc', 'Bomb Tester', '0030cfb3-1a03-4c5a-9afa-6b69376abe2e'  -- job-data here
   RETURNING id
   )
INSERT INTO industriesjobs (industry_id, job_id)
SELECT indid, id
FROM   ins_job i  -- that's a single row, so a CROSS JOIN is OK
     , unnest('{19c2e0ee-acd5-48b2-9fac-077ad4d49b19
              , 21f8ffb7-e155-4c8f-acf0-9e9913257845
              , 28c18acd-99ba-46ac-a2dc-59ce952eecf2}'::uuid[]) indid;  -- industry IDs here

Also demonstrating proper syntax for an array of uuid. (White space between elements and separators is irrelevant while not inside double-quotes.)

One of your UUIDs was one character short:
~~21f8ffb7-e155-4c8f-acf0-9e991325784~~
Must be something like:
21f8ffb7-e155-4c8f-acf0-9e9913257845 -- one more character

If you need functions, you do that, too:

CREATE OR REPLACE FUNCTION link_job_to_industries(_jobid uuid, _indids uuid[])
  RETURNS void AS
$func$
   INSERT INTO industriesjobs (industry_id, job_id)
   SELECT _indid, _jobid
   FROM   unnest(_indids) _indid;
$func$ LANGUAGE sql;

Etc.

Is it possible to write a postgres function that will handle a many to many join?

1 Answers1