https://supabase.com/ logo
Join Discord
Powered by
My function creates 3 records, 2 of which rely on ...
# sql
s
My function creates 3 records, 2 of which rely on each other (both have FK's that cannot be null). I'm guessing I've not created it correctly, because I'm now getting:
Copy code
sql
{"hint":null,"message":"null value in column \"invoice_id\" of relation \"jobs\" violates not-null constraint","code":"23502","details":"Failing row contains (...)."}
Anyone willing to help me debug? I'll post the code in a thread cause it's relatively big
Copy code
sql
create or replace function new_post(
  application_email text,
  business_name text,
  content_description text,
  content_how_to_apply text,
  content_who text,
  categories text[],
  invoice_address text,
  invoice_email text,
  invoice_purchase_order text,
  title text,
  job_type text,
  job_location text,
  remuneration_max int,
  remuneration_min int
)
returns void
language plpgsql
as $$
declare
  new_user_id bigint;
  new_job_id uuid;
  new_invoice_id uuid;
begin
  -- create public.user record
  -- public.user record is required to allow job posts
  -- without a specific user registration flow
  insert into public.users default values returning id into new_user_id;

  -- create job record
  insert into public.jobs (
    invoice_id,
    user_id,

    application_email,
    business_name,
    content_description,
    content_how_to_apply,
    content_who,
    categories,
    job_type,
    job_location,
    remuneration_max,
    remuneration_min,
    title
    ) values (
    new_invoice_id,
    new_user_id,

    application_email,
    business_name,
    content_description,
    content_how_to_apply,
    content_who,
    categories,
    job_type,
    job_location,
    remuneration_max,
    remuneration_min,
    title
  ) returning id into new_job_id;

  -- create invoice record
  insert into public.invoices (
    job_id,
    user_id,

    invoice_address,
    invoice_email,
    invoice_purchase_order
  ) values (
    new_job_id,
    new_user_id,

    invoice_address,
    invoice_email,
    invoice_purchase_order
  ) returning id into new_invoice_id;

  commit;
end; $$
j
the first value you're inserting into 
public.jobs
is still NULL:
Copy code
-- create job record
  insert into public.jobs (
    invoice_id,
you're inserting 
new_invoice_id
inside this column, and you declared it at the beginning with 
new_invoice_id uuid
, but because you hadnt assigned any variable to it at that point, it's still NULL
Copy code
insert into public.jobs (invoice_id ...)
values (
  new_invoice_id,  -- this is still NULL
  ...
)
if your invoice ID is meant to be auto-generated, you can exclude it from your INSERT statement altogether
s
It is auto-generated, in the invoices table. How do I make the jobs insert use the 
new_invoice_id
AFTER it creates the record in 
public.invoices
?
Or is this just bad practice and I should make one of the FKs nullable
j
hmm quick question before that - what's the relationship between invoices and jobs? im assuming a job can have many invoices (1 to many), and an invoice can only be mapped to 1 job (1-1)?
the reason is that generally, you wouldnt need to double-insert foreign keys in 2 separate tables (i.e. wouldnt have to insert a job_id into invoices + insert invoice_id into jobs at the same time)
s
Most likely it might be 1 invoice to many jobs 🤔
So I've structured my tables wrong then, good point
j
oh that's interesting. ok if it's 1 invoice to many jobs, then your invoice table does not need a job_id column. only your jobs table will need an invoice_id column. just a quick data modelling summary for your particular usecase, but other resources would be more comprehensive if you search online
s
Progress - new error message 🤣 
{"hint":null,"message":"invalid transaction termination","code":"2D000","details":null}
This is wrong?
Copy code
sql
  commit;
end; $$
j
you dont need the 'commit' line
s
🥳
It works 😄
Thank you!
I've updated my function to include below, but the response when successful has all 3 values as null?
Copy code
sql
create or replace function new_post(
  ...
  out new_user_id bigint,
  out new_invoice_id uuid,
  out new_job_id uuid
)
...
declare
  new_user_id bigint;
  new_invoice_id uuid;
  new_job_id uuid;
begin
...returning id into new_user_id;
...returning id into new_invoice_id;
...returning id into new_job_id;
j
do your table inserts look ok?
the IDs appear in the actual table right?
s
Yep inserts worked, IDs are there
Removing my declare block fixed the issue
PreviousNext
Powered by