Postgres trigger with after and on row appears to not run after writing to row
0
votes
1
answer
43
views
I have the following two tables:
CREATE TABLE qanda.group_questions (
id uuid DEFAULT gen_random_uuid() NOT NULL,
category_id uuid NULL,
business_model_id int4 NULL,
industry_id int4 NULL,
business_structure_id int4 NULL,
CONSTRAINT group_questions_pkey PRIMARY KEY (id),
CONSTRAINT group_questions_business_model_id_fkey FOREIGN KEY (business_model_id) REFERENCES public.business_models(id) ON DELETE CASCADE,
CONSTRAINT group_questions_business_structure_id_fkey FOREIGN KEY (business_structure_id) REFERENCES public.business_structure(id),
CONSTRAINT group_questions_category_id_fkey FOREIGN KEY (category_id) REFERENCES public.category(id) ON DELETE CASCADE,
CONSTRAINT group_questions_industry_id_fkey FOREIGN KEY (industry_id) REFERENCES public.industry(id) ON DELETE CASCADE
);
CREATE TABLE qanda.plain_text_questions (
id uuid NOT NULL,
question text NOT NULL,
user_prompt text NULL,
CONSTRAINT plain_text_questions_pkey PRIMARY KEY (id),
CONSTRAINT plain_text_questions_id_fkey FOREIGN KEY (id) REFERENCES qanda.group_questions(id)
);
When a row is inserted into the group_questions
table the following trigger fires:
create trigger on_group_question_inserted after
insert
on
qanda.group_questions for each row execute function notify_web_service_on_insert();
The trigger calls a function that calls a web service, which then should insert data in the plain_text_questions
table.
However it fails with this error:
"Error inserting data:",
{
"code": "23503",
"details": "Key (id)=(50ec4620-a27b-429b-b05f-4fe786f116a9) is not present in table \"group_questions\".",
"hint": null,
"message": "insert or update on table \"plain_text_questions\" violates foreign key constraint \"plain_text_questions_id_fkey\""
}
The trigger function is:
CREATE OR REPLACE FUNCTION public.notify_web_service_on_insert()
RETURNS trigger
LANGUAGE plpgsql
AS $function$
DECLARE
details JSON;
payload JSON;
request extensions.http_request;
BEGIN
-- Fetch details using the get_details_by_group_question_id function
SELECT qanda.get_details_by_group_question_id(NEW.id) INTO details; -- this is selecting from qanda.group_questions demonstrating that the data is in the table
-- Construct the JSON payload by adding the group_question_id
payload := jsonb_set(details::jsonb, '{group_question_id}', to_jsonb(NEW.id::text));
-- Construct the HTTP request structure
request := (
'POST', -- Method
'https://this_is_the_webservice/ ', -- URI
ARRAY[extensions.http_header('Authorization', 'Bearer hello_world'), -- Headers
extensions.http_header('Content-Type', 'application/json')],
'application/json', -- Content Type
payload::text -- Content
)::extensions.http_request;
-- Make the HTTP POST request
PERFORM content FROM extensions.http(request);
-- Return the NEW record to allow the insert operation to complete
RETURN NEW;
END;
$function$
;
If I change the trigger to create trigger on_group_question_inserted before
it fails, as it should. Based on my understanding before
means before the row has been written to the table. But, after
should mean it has been written. And due to the successful use of SELECT qanda.get_details_by_group_question_id(NEW.id) INTO details;
that is correct.
So, I'm left confused.
Asked by Magick
(111 rep)
Jul 16, 2024, 08:54 PM
Last activity: Jul 17, 2024, 03:25 AM
Last activity: Jul 17, 2024, 03:25 AM