pg_net extension and which table to retrieve http_post response

[rgroothuis]

I'm trying to implement a http_post select statement to add a contact in Hubspot from a Supabase function/trigger when a new user is add/created. I've a working select statement:

select
    net.http_post(
        url:='https://proxy.sequin.io/U5juHj8y/api.hubapi.com/crm/v3/objects/contacts',
        headers:='{"Content-Type": "application/json", "Authorization": "Bearer xxxxxxxxxxxxxxxxxxxxxxx"}'::jsonb,
        body:='{ "properties": { 
          "email": "example@hubspot.com",  
          "firstname": "Joe",
          "lastname": "Doe",
          "contact_type": "Customer" } }'::jsonb,
        timeout_milliseconds:=3600
    )
    as request_id;

Reading the documentation I should use the following command to retrieve the result of the above select statement:

select
    (response).body::json
from
    net.http_collect_response(request_id:=1);

When executing this select statement I get the following error message:

Failed to run sql query: syntax error at or near "raise"

I do not have any glue why this error message is raised? What is wrong with the from the doc copied statement?

When I run the following command:

select * from net._http_response where id = 1

I get a good result:

The difference is that it is a different result table! net._http_response and net.http_collect_response.

Are my findings correct and should I use the net._http_response table or am I doing something wrong? If so, what am I doing wrong? Thanks for your support.

[steve-chavez]

net.http_collect_response(request_id:=1);
Failed to run sql query: syntax error at or near "raise"

The net.http_collect_response function executes a RAISE,see https://github.com/supabase/pg_net/blob/master/sql/pg_net.sql#L335-L353.

It's deprecated and shouldn't be used. For now you can query the net._http_response directly. Once supabase/pg_net#62 it's done that won't be necessary anymore.

[Rubenburdin]

Hey rgroothuis,
Steve's answer is spot on and still holds up in 2025. The net.http_collect_response function was deprecated because of that internal RAISE behavior, so querying net._http_response directly is the right approach. One thing worth noting though, the underscore prefix on that table indicates its considered internal, so the API might change in future pg_net versions. For production use you probably want to wrap your response checks in a small helper function so if the schema changes you only fix it in one place.
For the HubSpot sync specifically, theres a few things to watch out for beyond just retrieving the response. HubSpot's API has rate limits that can get tricky when you have bursts of new signups, and you'll need to handle retries for failed requests somewhere. The pg_net extension doesnt retry automatically so you'd need a separate job or cron to reprocess failed entries from that response table. Also storing that Bearer token directly in your function isnt ideal for security, the Supabase Vault is worth looking into for secrets management (https://supabase.com/docs/guides/database/vault).
If you find yourself spending more time maintaining this sync than you expected, Stacksync handles Supabase to HubSpot sync bi-directionally without needing to build the plumbing yourself. But if you want to keep it custom, your approach with triggers and pg_net is solid, just make sure you have some monitoring on that response table for failures.