Selecting Row After Trigger

I have a database trigger that calls a function after a row is inserted. However, in my function I am not able to select the row as it hasnt been saved yet. The trigger is saying is an after insert, so shouldn't the record be saved? I am trying to just run a function after a row is inserted in the database. My suspicion is that it's because the function returns the record after the http call is made. So while the function may have the record attributes, they aren't actually there yet. For additional context, this is all trying to index records in my database to a search service after they are inserted (and eventually updated/deleted etc) Any help could be appreciated!

Trigger

create trigger index_products_to_search
  after insert on products
  for each row execute procedure 
  public.send_to_webhook( 'http://172.17.0.1:54321/functions/v1/hello' );

create trigger index_products_to_search
  after insert on products
  for each row execute procedure 
  public.send_to_webhook( 'http://172.17.0.1:54321/functions/v1/hello' );

Function

create extension http with schema extensions;

create or replace function send_to_webhook() returns trigger AS $trigger$
declare
  rec RECORD;
  payload TEXT;
  column_name TEXT;
  column_value TEXT;
  payload_items JSONB;
begin
  -- Set record row depending on operation
  CASE TG_OP
  WHEN 'INSERT', 'UPDATE' THEN
     rec := new;
  WHEN 'DELETE' THEN
     rec := old;
  ELSE
     RAISE EXCEPTION 'Unknown TG_OP: "%". Should not occur!', TG_OP;
  END CASE;
 
  payload_items := coalesce(row_to_json(rec),'{}')::jsonb;
  
  -- Build the payload
  payload := json_build_object(
    'timestamp',CURRENT_TIMESTAMP,
    'operation',TG_OP,
    'schema',TG_TABLE_SCHEMA,
    'table',TG_TABLE_NAME,
    'data',payload_items
  );

  -- Notify the webhook
  perform extensions.http((
    'POST',
    TG_ARGV[0]::varchar,
    ARRAY[http_header('Authorization','Bearer ANON_KEY')],
    'application/json',
    payload::varchar
  )::http_request);

  return rec;
end;
$trigger$ language plpgsql;

create extension http with schema extensions;

create or replace function send_to_webhook() returns trigger AS $trigger$
declare
  rec RECORD;
  payload TEXT;
  column_name TEXT;
  column_value TEXT;
  payload_items JSONB;
begin
  -- Set record row depending on operation
  CASE TG_OP
  WHEN 'INSERT', 'UPDATE' THEN
     rec := new;
  WHEN 'DELETE' THEN
     rec := old;
  ELSE
     RAISE EXCEPTION 'Unknown TG_OP: "%". Should not occur!', TG_OP;
  END CASE;
 
  payload_items := coalesce(row_to_json(rec),'{}')::jsonb;
  
  -- Build the payload
  payload := json_build_object(
    'timestamp',CURRENT_TIMESTAMP,
    'operation',TG_OP,
    'schema',TG_TABLE_SCHEMA,
    'table',TG_TABLE_NAME,
    'data',payload_items
  );

  -- Notify the webhook
  perform extensions.http((
    'POST',
    TG_ARGV[0]::varchar,
    ARRAY[http_header('Authorization','Bearer ANON_KEY')],
    'application/json',
    payload::varchar
  )::http_request);

  return rec;
end;
$trigger$ language plpgsql;

Edge Function

import { serve } from "https://deno.land/std@0.131.0/http/server.ts";
import { adminSupabaseClient } from "../_shared/supabase.ts";

serve(async (req) => {
  try {
    const payload = await req.json();
    const { data } = payload;

    const product = await adminSupabaseClient
      .from("products")
      .select("id")
      .eq("id", data.id)
      .single();

    // data.id is the ID of the return rec from the function, but trying to select it returns no results.  

    if (!product.data) {
      throw new Error(`No Product Found for ${data.id}`); // This error throws and it has the id from data
    }

    return new Response(JSON.stringify(data), { headers: { "Content-Type": "application/json" } });
  } catch (e) {
    throw new Error(e);
  }
});

import { serve } from "https://deno.land/std@0.131.0/http/server.ts";
import { adminSupabaseClient } from "../_shared/supabase.ts";

serve(async (req) => {
  try {
    const payload = await req.json();
    const { data } = payload;

    const product = await adminSupabaseClient
      .from("products")
      .select("id")
      .eq("id", data.id)
      .single();

    // data.id is the ID of the return rec from the function, but trying to select it returns no results.  

    if (!product.data) {
      throw new Error(`No Product Found for ${data.id}`); // This error throws and it has the id from data
    }

    return new Response(JSON.stringify(data), { headers: { "Content-Type": "application/json" } });
  } catch (e) {
    throw new Error(e);
  }
});

And then in the above url it's an edge function that tries to select that product but no product is returned. The function has access to the record as its provided in the send_to_webhook function, but retrieving a record by that id returns no results.

Selecting Row After Trigger

create trigger index_products_to_search
  after insert on products
  for each row execute procedure 
  public.send_to_webhook( 'http://172.17.0.1:54321/functions/v1/hello' );

create trigger index_products_to_search
  after insert on products
  for each row execute procedure 
  public.send_to_webhook( 'http://172.17.0.1:54321/functions/v1/hello' );

Function

create extension http with schema extensions;

create or replace function send_to_webhook() returns trigger AS $trigger$
declare
  rec RECORD;
  payload TEXT;
  column_name TEXT;
  column_value TEXT;
  payload_items JSONB;
begin
  -- Set record row depending on operation
  CASE TG_OP
  WHEN 'INSERT', 'UPDATE' THEN
     rec := new;
  WHEN 'DELETE' THEN
     rec := old;
  ELSE
     RAISE EXCEPTION 'Unknown TG_OP: "%". Should not occur!', TG_OP;
  END CASE;
 
  payload_items := coalesce(row_to_json(rec),'{}')::jsonb;
  
  -- Build the payload
  payload := json_build_object(
    'timestamp',CURRENT_TIMESTAMP,
    'operation',TG_OP,
    'schema',TG_TABLE_SCHEMA,
    'table',TG_TABLE_NAME,
    'data',payload_items
  );

  -- Notify the webhook
  perform extensions.http((
    'POST',
    TG_ARGV[0]::varchar,
    ARRAY[http_header('Authorization','Bearer ANON_KEY')],
    'application/json',
    payload::varchar
  )::http_request);

  return rec;
end;
$trigger$ language plpgsql;

create extension http with schema extensions;

create or replace function send_to_webhook() returns trigger AS $trigger$
declare
  rec RECORD;
  payload TEXT;
  column_name TEXT;
  column_value TEXT;
  payload_items JSONB;
begin
  -- Set record row depending on operation
  CASE TG_OP
  WHEN 'INSERT', 'UPDATE' THEN
     rec := new;
  WHEN 'DELETE' THEN
     rec := old;
  ELSE
     RAISE EXCEPTION 'Unknown TG_OP: "%". Should not occur!', TG_OP;
  END CASE;
 
  payload_items := coalesce(row_to_json(rec),'{}')::jsonb;
  
  -- Build the payload
  payload := json_build_object(
    'timestamp',CURRENT_TIMESTAMP,
    'operation',TG_OP,
    'schema',TG_TABLE_SCHEMA,
    'table',TG_TABLE_NAME,
    'data',payload_items
  );

  -- Notify the webhook
  perform extensions.http((
    'POST',
    TG_ARGV[0]::varchar,
    ARRAY[http_header('Authorization','Bearer ANON_KEY')],
    'application/json',
    payload::varchar
  )::http_request);

  return rec;
end;
$trigger$ language plpgsql;

Edge Function

import { serve } from "https://deno.land/std@0.131.0/http/server.ts";
import { adminSupabaseClient } from "../_shared/supabase.ts";

serve(async (req) => {
  try {
    const payload = await req.json();
    const { data } = payload;

    const product = await adminSupabaseClient
      .from("products")
      .select("id")
      .eq("id", data.id)
      .single();

    // data.id is the ID of the return rec from the function, but trying to select it returns no results.  

    if (!product.data) {
      throw new Error(`No Product Found for ${data.id}`); // This error throws and it has the id from data
    }

    return new Response(JSON.stringify(data), { headers: { "Content-Type": "application/json" } });
  } catch (e) {
    throw new Error(e);
  }
});

import { serve } from "https://deno.land/std@0.131.0/http/server.ts";
import { adminSupabaseClient } from "../_shared/supabase.ts";

serve(async (req) => {
  try {
    const payload = await req.json();
    const { data } = payload;

    const product = await adminSupabaseClient
      .from("products")
      .select("id")
      .eq("id", data.id)
      .single();

    // data.id is the ID of the return rec from the function, but trying to select it returns no results.  

    if (!product.data) {
      throw new Error(`No Product Found for ${data.id}`); // This error throws and it has the id from data
    }

    return new Response(JSON.stringify(data), { headers: { "Content-Type": "application/json" } });
  } catch (e) {
    throw new Error(e);
  }
});

Selecting Row After Trigger

Selecting Row After Trigger

Similar Threads

Similar Threads

Similar Threads