type "http_request" does not exist

Question

type "http_request" does not exist

shuhaib-inunity opened this issue 2 years ago · 5 comments

I am trying to make a http request as a custom user

CREATE OR REPLACE FUNCTION public.test_post()
returns trigger as $$

declare hres net.http_response;
declare trigger_payload jsonb;

begin
  trigger_payload = jsonb_build_object(
    'old_record', OLD,
    'record', NEW,
    'type', TG_OP,
    'table', TG_TABLE_NAME,
    'schema', TG_TABLE_SCHEMA
  );

  select * into hres from http(
    (
      'POST',
      'https://api_link.com/',
      ARRAY[
        http_header('Authorization', concat('Bearer ', '')),
        http_header('Content-Type', 'application/json')
      ],
      trigger_payload,
      trigger_payload
    )::http_request
    );

  return new;
end;
$$ language plpgsql security definer;

DROP TRIGGER IF EXISTS ON_CREATE_test on "public"."test";
CREATE TRIGGER ON_CREATE_test
AFTER INSERT ON public.test
FOR EACH ROW EXECUTE PROCEDURE public.test_post();`

I have created the user as follows

create user api_gateway with password 'password123';
grant all on schema net to api_gateway;
grant all on schema extensions to api_gateway

alter user api_gateway SET search_path=public,extensions;

when api_gateway user inserts one row to the table, trigger should be called and make a http request as follows

QueryFailedError: type "http_request" does not exist....

Answer 1 · 2022-12-05T16:07:37.000Z

Sounds like the extension isn't installed, or your search_path when this trigger is called somehow still is missing access to the extension. I can't see an issue with HTTP here, if you find one please open.

Answer 2 · 2023-04-17T10:25:53.000Z

I am also having this issue when I http_request is inside a function

Answer 3 · 2023-04-17T10:27:58.000Z

@pramsey here is when I use it outside a function

Answer 4 · 2023-04-17T11:12:08.000Z

which schema are you installed http extension?

http_header('Authorization', concat('Bearer ', '')),
http_header('Content-Type', 'application/json')

i think it is not correct code

you had to write

 ARRAY[
        ('Authorization', concat('Bearer ', '')),
        ('Content-Type', 'application/json')
]::<schema_with_http_extension>.http_header[],

and

for content-type in http_request has special field

    Composite type "public.http_request"
    Column    |       Type        | Modifiers
--------------+-------------------+-----------
 method       | http_method       |
 uri          | character varying |
 headers      | http_header[]     |
 content_type | character varying | <===
 content      | character varying |

4th field in http_request is not json i think it is root of problem

and you need to cast json to text in 5th field

CREATE TRIGGER ON_CREATE_test
AFTER INSERT ON public.test
FOR EACH ROW EXECUTE PROCEDURE public.test_post();`CREATE OR REPLACE FUNCTION public.test_post()
returns trigger as $$

declare hres net.http_response;
declare trigger_payload jsonb;

begin
  trigger_payload = jsonb_build_object(
    'old_record', OLD,
    'record', NEW,
    'type', TG_OP,
    'table', TG_TABLE_NAME,
    'schema', TG_TABLE_SCHEMA
  );

  select * into hres from http(
    (
      'POST',
      'https://api_link.com/',
      ARRAY[
        ('Authorization', concat('Bearer ', ''))
      ]::http_header[],
      'application/json/,
      cast(trigger_payload to text)
    )::http_request
    );

  return new;
end;
$$ language plpgsql security definer;

DROP TRIGGER IF EXISTS ON_CREATE_test on "public"."test";
CREATE TRIGGER ON_CREATE_test
AFTER INSERT ON public.test
FOR EACH ROW EXECUTE PROCEDURE public.test_post();

Answer 5 · 2023-04-17T11:50:58.000Z