pgsql-http to GET and POST an image

I need to do some basic image scraping.

I will be receiving URLs of image assets from a clients' API and my plan is to park those URLs in the database and move on with things.

Later on, I want come back around and using pg_cron and pgsql-http, scrape the images from the URLs I have and put in them into Supabase storage.

This would be a doddle in an edge function and I'm already dealing with user uploads from forms in a Vue app using supabase-js – but I don't feel like this is going to work/be practical if I've got a few hundred new images to scrape and upload. Hence doing this in the background later using pg_cron and pgsql-http – in short I need to GET the image from a URL I have on file, and PUT it into Supabase storage, but from a database function called by pg_cron.

I'm struggling with it tho. There's an example on the pgsql-http github that brings in a png and gets its content-type and size, and this works as expected when I dump it as is into a new SQL snippet in Supabase. But as soon as I put any other image URL in, it doesn't work.

WITH
  http AS (
    SELECT * FROM http_get('http://httpbin.org/image/png') -- works, but no other url I does... 
  ),
  headers AS (
    SELECT (unnest(headers)).* FROM http
  )
SELECT
  http.content_type,
  length(textsend(http.content)) AS length_binary,
  headers.value AS length_headers
FROM http, headers
WHERE field = 'Content-Length';

WITH
  http AS (
    SELECT * FROM http_get('http://httpbin.org/image/png') -- works, but no other url I does... 
  ),
  headers AS (
    SELECT (unnest(headers)).* FROM http
  )
SELECT
  http.content_type,
  length(textsend(http.content)) AS length_binary,
  headers.value AS length_headers
FROM http, headers
WHERE field = 'Content-Length';

WITH
  http AS (
    SELECT * FROM http_get('http://httpbin.org/image/png') -- works, but no other url I does... 
  ),
  headers AS (
    SELECT (unnest(headers)).* FROM http
  )
SELECT
  http.content_type,
  length(textsend(http.content)) AS length_binary,
  headers.value AS length_headers
FROM http, headers
WHERE field = 'Content-Length';

WITH
  http AS (
    SELECT * FROM http_get('http://httpbin.org/image/png') -- works, but no other url I does... 
  ),
  headers AS (
    SELECT (unnest(headers)).* FROM http
  )
SELECT
  http.content_type,
  length(textsend(http.content)) AS length_binary,
  headers.value AS length_headers
FROM http, headers
WHERE field = 'Content-Length';

So my question is two fold:
1) There's a general, "am I going about this the right way?"
2) Then to at least help me along my way, why does the pgsql-http example work with the example URL, but no others I try do?

pgsql-http to GET and POST an image

Similar Threads

Similar Threads

Similar Threads