https://supabase.com/ logo
Join DiscordCommunities
Powered by
Hey! I have this plpgsql function: ```sql CREATE O...
# help
h
Hey! I have this plpgsql function:
Copy code
sql
CREATE OR REPLACE FUNCTION public.events_http_send_message_event(event_id uuid, dispatch_event_type event_type, bot bot_members, message messages)
    RETURNS http_client_response AS
$$
DECLARE
    res     http_client_response;
    headers http_header[];
BEGIN
    SELECT
           public.events_http_headers(event_id, bot)
    INTO headers;

    SELECT status, content_type, content::json->>'data' AS data, content::json->>'type' as type
        FROM http((
                   'POST',
                   bot.interactions_url,
                   headers,
                   -- Removed for brefiety
            )::http_request
        ) INTO res;

    RETURN res;
END;
$$ LANGUAGE plpgsql;
But it gives me this error,
Copy code
invalid input syntax for type integer: \"(200,\"application/json; charset=utf-8\",,)\"
Any help would be awesome!
c
I am not very familiar with plpgsql, but what is the definition of "http_client_response" - I assume this is a custom type of yours since I don't see it anywhere in the pgsql-http extension source code
from the looks of it, it seems that sticking the values from the response (status, content_type, etc) into your res variable fails and I think knowing the definition of http_client_response would help
other than this, I can suggest 2 other things: - you can introduce some logging into your functions (by logging, I mean writing to a dedicated logs table, because currently, you don't have access to the Postgres file logs) - this doesn't seem like a Supabase-specific issue but a more general PostgreSQL/PLpgSQL /pgsql-http so you might also want to ping the PostgreSQL community directly (https://www.postgresql.org/community/irc/)
h
@User
Copy code
sql
    CREATE TYPE http_client_response AS
    (
        status       integer,
        content_type varchar,
        data         jsonb,
        type         integer
    );
c
are you sure you are getting the error inside the function and not in the caller of the function?
I tried to reproduce your error like this, but it works:
CREATE OR REPLACE FUNCTION public.test_http() RETURNS http_client_response AS $$ DECLARE res http_client_response; BEGIN SELECT status, content_type, content::json->>'form' AS data, 5 from http_post('http://httpbin.org/post', 'myvar=myval&foo=bar', 'application/x-www-form-urlencoded') INTO res; RETURN res; END; $$ LANGUAGE plpgsql; SELECT public.test_http();
h
It might be, I can get the code that calls it!
Copy code
sql
CREATE OR REPLACE FUNCTION public.events_dispatch_message(event_id uuid, dispatch_event_type event_type, message_id uuid, client bot_members)
    RETURNS VOID AS
$$
DECLARE
    message messages;
    res http_client_response;
BEGIN
    SELECT
        *
    INTO message
    FROM messages
    WHERE id = message_id;

    SELECT
           public.events_http_send_message_event(event_id, dispatch_event_type, client, message)
    INTO res;
END;
$$ LANGUAGE plpgsql;
this is all under functions
c
yep, the problem is with this call:
Copy code
SELECT
           public.events_http_send_message_event(event_id, dispatch_event_type, client, message)
    INTO res;
h
Oh! Whats wrong with it?
c
well, as I said, I don't really know PLpgSQL, but it seems that events_http_send_message_event() returns a string which you then try to stick inside a variable of type http_client_response
why events_http_send_message_event() returns a string instead of a record variable, I don't know
probably some subtlety related to PLpgSQL
h
You see, i change the line to just run it and ignore the returned data:
Copy code
sql
    EXECUTE public.events_http_send_message_event(event_id, dispatch_event_type, client, message);
and that still errors with: 
syntax error at or near \"200\"
c
yes, I see...not sure what the problem is, as I said, maybe you can ping the guys in the IRC postgresql channel
btw
if you need to discard the result of events_http_send_message_event
h
the IRC channel are very helpful thx for the suggestion
c
no problem
I was gonna say
if you do PERFORM public.events_http_send_message_event() instead of EXECUTE or SELECT it works fine
...but that discards the return value of the function call
h
@User you and the IRC channel has helped me fix it!
c
great - can you update us on what the issue was and how you fixed it?
h
Sure!
It was a collection of poor SQL
c
can you be a bit more specific 🙂
h
so I just removed redudant function and as of this commit - https://github.com/github-chat/sql/commit/a4c6aa864d19e5261697ce3ab0756a27c6a11af4 - its working
c
ok, it looks like you replaced the SELECT ... INTO statement with a direct assignment :=...I am glad that it works now, but I don't really understand why one works and the other doesn't, which was the interesting part for me
h
yeah
i dont get it either :/
PreviousNext
Powered by