pramsey / pgsql-http Goto Github PK

Any chance you’d be willing to release this on PGXN, Paul? Would make it easier for our automated build stuff to grab, build, and install. Plus the docs would look cool!

at least instructions how to build it.

Thank you.

Hi,

I think there may be an issue with PUT requests please see:

sysadminmike/couch-to-postgres#21

I have done some further testing and get an error as well but not a postgres crash
For example if i run:

SELECT status FROM http_put('http://192.168.3.21:5984/newdb'::text, ''::text, 'application/json'::text); 

This returns correctly (note this creates a db on couchdb).

If i then add some content to the request to make a document in couch:

SELECT status FROM http_put('http://192.168.3.21:5984/newdb/puttest-1'::text, '{"myvar":1}'::text, 'application/json'::text); 

In postgres log i see:

Oct  5 11:06:17 postgres postgres[95749]: [8-1] ERROR:  select/poll returned error
Oct  5 11:06:17 postgres postgres[95749]: [8-2] STATEMENT:  
Oct  5 11:06:17 postgres postgres[95749]: [8-5]     SELECT status FROM http_put('http://192.168.3.21:5984/newdb/puttest-1'::text, '{"myvar":1}'::text, 'application/json'::text); 

And in pgsql i get back

ERROR:  select/poll returned error
********** Error **********

ERROR: select/poll returned error
SQL state: XX000

If i try to run the request as a POST then all is ok

SELECT status FROM http_post('http://192.168.3.21:5984/newdb'::text, '{"_id":"posttest-1"}'::text, 'application/json'::text); 

Now that we have regress tests, they work great under 64-bit, not so much under 32-bit.

This is testing with 1.2.2

At a glance all the tests fail because of the status output
In all the tests, the status output should be 200, but instead I get random numbers returned back

  SELECT status
  FROM http_get('http://httpbin.org/status/202');

yields:

status   

! -----------
! 232402072

I get a different number each time I run it.

the version 1.3 is it already available?

are the functions below only available in the vesion 1.3 or should they be available in the vesion 1.2
SELECT http_set_curlopt('CURLOPT_PROXYPORT', '12345');

I have PostgreSQL devel packages and CURL devel packages installed (postgresql-devel-9.2.23-3.el7_4.x86_64 and libcurl-devel-7.29.0-42.el7_4.1.x86_64).
When I run make, I get this:
`
gcc -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -DLINUX_OOM_SCORE_ADJ=0 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -fPIC -I. -I. -I/usr/include/pgsql/server -I/usr/include/pgsql/internal -D_GNU_SOURCE -I/usr/include/libxml2 -c -o http.o http.c
http.c:169:1: error: unknown type name «pqsigfunc»
pqsigfunc pgsql_interrupt_handler = NULL;
^
http.c: In function «_PG_init»:
http.c:240:2: Warning: implicit declaration of function «pqsignal» [-Wimplicit-function-declaration]
pgsql_interrupt_handler = pqsignal(SIGINT, http_interrupt_handler);
^
In file included from /usr/include/curl/curl.h:2227:0,
from http.c:69:
http.c: In function «http_request»:
http.c:854:29: error: «CURLOPT_XFERINFOFUNCTION» undeclared (first use in this function)
CURL_SETOPT(g_http_handle, CURLOPT_XFERINFOFUNCTION, http_progress_callback);
^
http.c:854:2: Warning: in expansion of macro «CURL_SETOPT»
CURL_SETOPT(g_http_handle, CURLOPT_XFERINFOFUNCTION, http_progress_callback);
^
http.c:854:29: Warning: each undeclared identifier is reported only once for each function it appears in
CURL_SETOPT(g_http_handle, CURLOPT_XFERINFOFUNCTION, http_progress_callback);
^
http.c:854:2: Warning: in expansion of macro «CURL_SETOPT»
CURL_SETOPT(g_http_handle, CURLOPT_XFERINFOFUNCTION, http_progress_callback);
^
http.c:982:5: error: invalid type argument of unary «*» (have «int»)
(*pgsql_interrupt_handler)(http_interrupt_requested);
^
http.c: In function «typname_get_tupledesc»:
http.c:525:1: Warning: control reaches end of non-void function [-Wreturn-type]
}
^
make: *** [http.o] Error 1

`

I have hacked so it can PUT docs into a couchdb eg: https://github.com/sysadminmike/couch-to-postgres/blob/master/README.md

//CURL_SETOPT(http_handle, CURLOPT_POST, 1); //Comment this out
CURL_SETOPT(http_handle, CURLOPT_CUSTOMREQUEST, "PUT"); //Add this

But it would be very useful not to have to do this and be able to issue a POST request as well.

For an operation like geocoding, not every site is going to like getting hammered with lots of requests. Is there currently any way to throttle requests that are made, say by 50 or 100 milliseconds? And if not, I think this would be a really useful improvement-

db=# select urlencode('ö');
urlencode 
-----------
 ö
(1 Zeile)

But this should be %C3%B6

Hi we want to use you extensions to make an http_post request

but we get an
ERROR: Peer's Certificate issuer is not recognized.

on the Shell with Curl there is an Option -k to supress this Error

is it possible to do it with you extention as well?

Thanks

I am sending put request to elasticsearch service from postgresql.

SELECT status, content_type
  FROM http_put('http://localhost:9200/merchant_index_dev/merchant/45cb81fe-647e-4a8d-934a-8f4cccf881bf', '{
    "id" : "45cb81fe-647e-4a8d-934a-8f4cccf881bf",
    "name" : "Test merchant indexed from postgres"
}', 'application/json');

It is working fine(indexes a new doc with given id). However I am getting the following error at pg side:

[SQL]
SELECT status, content_type
  FROM http_put('http://localhost:9200/merchant_index_dev/merchant/45cb81fe-647e-4a8d-934a-8f4cccf881bf', '{
    "id" : "45cb81fe-647e-4a8d-934a-8f4cccf881bf",
    "name" : "Test merchant indexed from postgres"
}', 'application/json');

[Err] server closed the connection unexpectedly
	This probably means the server terminated abnormally
	before or while processing the request.

I'm not sure whether this extension is available on PGXN, I see that there is a META.json file containing PGXN metadata, but I can't find pgsql-http on PGXN neither by going to https://pgxn.org/dist/http/ (where in my understanding should be according to the "name": "http" entry in the metadata file) nor by using PGXN's search engine.

If it isn't published, are there any plans to publish it (given the presence of the metadata file)?

Thank you and sorry if this a dummy question/not a real issue but I don't understand why there is a META.json and I can't find the page on the PGXN site, maybe it's me not searching in the correct way.

Hi,

I was able to get pgsql-http installed under PostgreSQL 9.4 (Redhat 6.6)

But now when i execute the function http_post() i get :

[XX000] ERROR: pgsql-http requires Curl version 0.7.20 or higher

Initially i had curl 7.19 installed, but this messages keeps coming even after i upgraded to curl 7.31
Also i did rebuild the sources after upgrading curl to 7.31

Hello,

Not a big issue, but we use pgsql-http in a symfony application, which seem to use a custom search_path when updating entities in the database (probably the ORM which does that). We have an UPDATE trigger on a table managed by symfony. It kind of works, but there is an error with inline the http_response type.
After installing (on Ubuntu), if you check the SQL of the http function, you get

CREATE OR REPLACE FUNCTION public.http(request http_request)
  RETURNS http_response AS
'$libdir/http', 'http_request'
  LANGUAGE c VOLATILE
  COST 1;
ALTER FUNCTION public.http(http_request)
  OWNER TO postgres;

the function is prefixed, but not the return type (it should be public.http_response I believe)
In my trigger, I had to do beforehand

SET search_path TO 'public';

And at the end, set it back to the symfony search path, which is not ideal (but works :) ).

Fabien

Hi my opstgres was installed manual,
i did make and make install but, i when want to do CREATE EXTENSION http
return:
ERROR: could not access file "$libdir/http": No such file or directory

regex.h at least for mingw doesn't exist and I presume won't exist by default for VC++ builds either, so should be mentioned as a requirement.

So gives a

can't find regex.h file when trying to compile under mingw64.

I was able to get it to compile by swapping out regex dependency with pcre and get it to compile with this hack

http://go.kblog.us/2013/02/compiling-pcre-library.html

Though I'm running into issues now trying to get it to run under PostgreSQL 9.4. That might be separate or related.

I'm sure I've tried this before, but doesn't seem to work anymore at least under windows with latest changes. I assume it might have been the UTF support change. Have to try back.

Here is a slightly modified from the readme.

WITH
  http AS (
    SELECT * FROM http_get('http://postgis.net/images/postgis-logo.png')
  ),
  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';

Gives me this error:

ERROR: invalid byte sequence for encoding "UTF8": 0x89

Hello,

We are getting http timeout error while posting large volume of data via http_post postgres db package.

ERROR: Operation timed out after 5001 milliseconds with 0 bytes received
CONTEXT: SQL statement "SELECT status, content FROM http_post(v_httpurl,v_jsondata,'application/json; charset=utf8')"

It works perfectly fine for small set of data. If we are sending huge volume of data, we are getting Timeout error. Any help on this would be highly appreciated.

Do we need to set any parameters at DB or System level. Please advice.

Thanks in advance.

Mohammad Mamajiwala

@pramsey I'm sorry I guess I didn't test master before you released and didn't think things would break.
I got below trying to compile 1.2.1 you just released. switching back to 1.2.0 compiled fine.

This is building with curl 7.48.0,

Might have to do with you new reliance on libcrypto. I'm going to compile with that enabled.
Before you didn't have a direct dependence on OpenSSL, so I could swap in and out with a curl that had or didn't have SSL support.

> rm -rf results/ regression.diffs regression.out tmp_check/ tmp_check_iso/ log/ output_iso/
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -g -O2  -I/projects/curl/rel-curl-7.48.0w64gcc48/include -I/projects/pcre/rel-8.33w64gcc48/include -I/projects/libxml/rel-libxml2-2.7.8w64gcc48/include/libxml2 -I. -I./ -IC:/MING64~1/projects/POSTGR~1/rel/PG10W6~2/include/server -IC:/MING64~1/projects/POSTGR~1/rel/PG10W6~2/include/internal  -I.  -I/projects/curl/rel-curl-7.48.0w64gcc48/include -I/projects/libxml/rel-libxml2-2.7.8w64gcc48/include/libxml2 -I/projects/pcre/rel-8.33w64gcc48/include -IC:/MING64~1/projects/POSTGR~1/rel/PG10W6~2/include/server/port/win32  -c -o http.o http.c
http.c: In function 'http_request':
http.c:912:5: warning: implicit declaration of function 'strcasestr' [-Wimplicit-function-declaration]
     if ( strcasestr(param, paramtype) )
     ^
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -g -O2   -shared -static-libgcc -o http.dll  http.o -LC:/MING64~1/projects/POSTGR~1/rel/PG10W6~2/lib -Wl,--allow-multiple-definition -Wl,--disable-auto-import -L/projects/libxml/rel-libxml2-2.7.8w64gcc48/lib  -Wl,--as-needed   -L/projects/curl/rel-curl-7.48.0w64gcc48/lib -L/projects/libxml/rel-libxml2-2.7.8w64gcc48/lib -L/projects/pcre/rel-8.33w64gcc48/lib -lpostgres -lpgport -lcurl -lxml2 -lregex -lpgcommon -lpgport -lxml2 -lws2_32 -lm  -lws2_32 -Wl,--export-all-symbols -Wl,--out-implib=libhttp.a
http.o: In function `http_request':
C:\ming64gcc48\projects\postgresql\extensions\pgsql_http/http.c:912: undefined reference to `strcasestr'
collect2.exe: error: ld returned 1 exit status
C:/MING64~1/projects/POSTGR~1/rel/PG10W6~2/lib/pgxs/src/MAKEFI~1/../../src/Makefile.shlib:372: recipe for target 'http.dll' failed
make: *** [http.dll] Error 1
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -g -O2   -shared -static-libgcc -o http.dll  http.o -LC:/MING64~1/projects/POSTGR~1/rel/PG10W6~2/lib -Wl,--allow-multiple-definition -Wl,--disable-auto-import -L/projects/libxml/rel-libxml2-2.7.8w64gcc48/lib  -Wl,--as-needed   -L/projects/curl/rel-curl-7.48.0w64gcc48/lib -lcurl -lssl -lcrypto -lssl -lcrypto -lgdi32 -lwldap32 -lws2_32 -LC:/MING64~1/projects/POSTGR~1/rel/PG10W6~2/lib -lpostgres -lpgcommon -lpgport -lxml2 -lws2_32 -lm  -lws2_32 -Wl,--export-all-symbols -Wl,--out-implib=libhttp.a
C:/ming64gcc48/mingw64/bin/../lib/gcc/x86_64-w64-mingw32/4.8.3/../../../../x86_64-w64-mingw32/bin/ld.exe: cannot find -lssl
C:/ming64gcc48/mingw64/bin/../lib/gcc/x86_64-w64-mingw32/4.8.3/../../../../x86_64-w64-mingw32/bin/ld.exe: cannot find -lcrypto
C:/ming64gcc48/mingw64/bin/../lib/gcc/x86_64-w64-mingw32/4.8.3/../../../../x86_64-w64-mingw32/bin/ld.exe: cannot find -lssl
C:/ming64gcc48/mingw64/bin/../lib/gcc/x86_64-w64-mingw32/4.8.3/../../../../x86_64-w64-mingw32/bin/ld.exe: cannot find -lcrypto
collect2.exe: error: ld returned 1 exit status
C:/MING64~1/projects/POSTGR~1/rel/PG10W6~2/lib/pgxs/src/MAKEFI~1/../../src/Makefile.shlib:372: recipe for target 'http.dll' failed
make: *** [http.dll] Error 1

Hi,
I get this error message randomly when I try to issue the following query:

SELECT content FROM http_get('someurl')

Of course, the timeout may vary for few milliseconds.
What's weird is I read that CURLOPT_CONNECTTIMEOUT is set to 300s by default. And I can't modify this value anyway.
And I have checked the value of http.timeout_msec which is set to 5s:

show http.timeout_msec ;
http.timeout_msec
-------------------
5s

So basicaly, I don't understand where does this 1000 ms limit come from.
Thanks

This is probably more of an issue on Windows EDB version. On BigSQL windows version surprisingly it's not so much of an issue.

Regarding where I place the cabundle.crt file, it never is found. Doing a process file check, , it never ieven seems to try to look for it.

That said, I can't get https to work at all under PostgreSQL VC++ builds without being able to set the CURL_CAINFO.

so things like:

`
SELECT * FROM http_get('https://www.google.com');

ERROR: SSL certificate problem: unable to get local issuer certificate
********** Error **********
`

I have a patch which I'll send shortly to add another GUC http.cainfo. Feel free to reject it since I know you don't want to add any other specific GUCs.

Great extension! That's exactly what I was searching for.

Does this extension work with a proxy?
Can a special CA file be used?

Best regards
Johannes

Hi pramsey,

I cant install the client on my postgresql server :(
I follow the instructions but nothing. Error.
My Postgresql version is: PostgreSQL 9.2.4 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-3), 64-bit

Apparently its an error with "htup_details.h"

Please help!
Regards

Hi,

When I call 'http_put' or 'http_post' in my SQL script, I found that the memory consumption of the corresponding PG backend process was growing rapidly (about 2M per seond).

I did a little investigation in http.c...
it seems that in the end of function 'http_request()'， when we are constructing the http_response tuple, the memory allocated by CStringGetTextDatum() / header_string_to_array() / cstring_to_text_with_len() was not released immediately.

is there a way to prevent this memory leak?
thanks in advance..

Easy enough to generate an example from httpbin, so there we go.

SELECT * 
FROM http_get('http://httpbin.org/gzip?foo=bar');
ERROR:  invalid byte sequence for encoding "UTF8": 0x8b

SELECT ext.http_get('www.google.com'
);

ERROR: type "http_request" does not exist
LINE 1: SELECT http(('GET', $1, NULL, NULL, NULL)::http_request)
^
QUERY: SELECT http(('GET', $1, NULL, NULL, NULL)::http_request)
CONTEXT: SQL function "http_get" during inlining
********** Fehler **********

ERROR: type "http_request" does not exist
SQL Status:42704
Kontext:SQL function "http_get" during inlining

How to pass parameter on delete?
Something like this
SELECT status, content
FROM http_delete('http://localhost/openapi/delete',
'uid=1001');

Hi,

I'm trying to get a page for which charset is defined to iso8859-1. But with the extension, I get this kind of characters: Probl�me.

Using curl on the command line, I can pipe the result to the following command iconv -f iso8859-1 -t utf-8 which does the job perfectly.

How can I achieve that with the extension ?

Of course, there's a perfect service to run testing on. A testing target could use this http://httpbin.org/.

Trying to build on CentOS 7.4.1708, where libcurl is at v7.29.0, I get:

In file included from /usr/include/curl/curl.h:2227:0,
                 from http.c:69:
http.c: In function 'http_request':
http.c:842:29: error: 'CURLOPT_XFERINFOFUNCTION' undeclared (first use in this function)
  CURL_SETOPT(g_http_handle, CURLOPT_XFERINFOFUNCTION, http_progress_callback);
                             ^
http.c:842:2: note: in expansion of macro 'CURL_SETOPT'
  CURL_SETOPT(g_http_handle, CURLOPT_XFERINFOFUNCTION, http_progress_callback);
  ^
http.c:842:29: note: each undeclared identifier is reported only once for each function it appears in
  CURL_SETOPT(g_http_handle, CURLOPT_XFERINFOFUNCTION, http_progress_callback);
                             ^
http.c:842:2: note: in expansion of macro 'CURL_SETOPT'
  CURL_SETOPT(g_http_handle, CURLOPT_XFERINFOFUNCTION, http_progress_callback);
  ^
make: *** [http.o] Error 1
error: Bad exit status from /var/tmp/rpm-tmp.S40Dcu (%build)

It looks like CURLOPT_XFERINFOFUNCTION was added in v7.32.0, but your README says pgsql-http requires 7.20.0 or higher. So I guess increment the minimum required version of libcurl or drop the use of this function?

Have some fun with the extension e.g.

just crashes server,

select status from http_get('https://twitter.com/DemonicDragon')

raises an error

select status from http_get('https://www.google.com')

the error

RROR:  SSLRead() return error -9806
********** Error **********

ERROR: SSLRead() return error -9806
SQL state: XX000

at the same time

curl -I https://www.google.com
=> HTTP/1.1 302 Found

other https links work just fine
e.g.

returns 2000

select status from http_get('https://example.com')

just compiled on new machine and got that

osx fully updated, postgresql (PostgreSQL) 9.5.3

http-app=| select status from http_get('https://ya.ru');
 status
--------
    200
(1 row)

http-app=| select status from http_get('https://www.instagram.com');
 status
--------
    200
(1 row)

http-app=| select status from http_get('https://google.com');
ERROR:  SSLRead() return error -9806

http-app=| select status from http_get('https://twitter.com/');
ERROR:  Operation timed out after 0 milliseconds with 0 out of 0 bytes received
http-app=| select status from http_get('https://twitter.com/');
server closed the connection unexpectedly
    This probably means the server terminated abnormally
    before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.

It seems that it's not possible to build pgsql-http for Postgres 9.5 (at least for current 9.5.3) because of changes at PostgreSQL public functions:

+ make
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -DLINUX_OOM_ADJ=0 -fpic -I. -I./ -I/usr/pgsql-9.5/include/server -I/usr/pgsql-9.5/include/internal -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include  -c -o http.o http.c
http.c: In function 'header_array_to_slist':
http.c:235:2: error: too few arguments to function 'array_create_iterator'
  iterator = array_create_iterator(array, 0);
  ^
In file included from http.c:46:0:
/usr/pgsql-9.5/include/server/utils/array.h:448:22: note: declared here
 extern ArrayIterator array_create_iterator(ArrayType *arr, int slice_ndim, ArrayMetaState *mstate);

@pramsey I'm in middle of installing all my favorite exntesions for PostgreSQL 10. Are you planning to do another release soon like a 1.2.1, or should I just go with 1.2.0.

1.2.0 works fine I think.

Hi Paul,

How to send header param with http_get(). I need this for sending username & password.
Thank you,

I tried to install the extension under PostgreSQL 9.4, but i get the following message :

ERROR: incompatible library "/usr/pgsql-9.4/lib/http.so": version mismatch
DETAIL: Server is version 9.4, library is version 9.3.
********** Error **********

ERROR: incompatible library "/usr/pgsql-9.4/lib/http.so": version mismatch
SQL state: XX000
Detail: Server is version 9.4, library is version 9.3.

is it possible to fix this?

Use this https://curl.haxx.se/libcurl/c/CURLOPT_XFERINFOFUNCTION.html

during the installation i am getting this error:

gcc -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -m64 -mtune=generic -DLINUX_OOM_ADJ=0 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -fwrapv -fpic -I. -I/usr/include/pgsql/server -I/usr/include/pgsql/internal -D_GNU_SOURCE -I/usr/include/libxml2 -c -o http.o http.c
http.c: In function ‘lookup_type_oid’:
http.c:204: error: too many arguments to function ‘LookupExplicitNamespace’
http.c:205: warning: implicit declaration of function ‘GetSysCacheOid2’
make: *** [http.o] Error 1

Hi there
I though you can set different curl settings

SELECT http_set_curlopt('CURLOPT_TIMEOUT_MS', '12000');
ERROR: curl option 'CURLOPT_TIMEOUT_MS' is not available for run-time configuration

but I get the error
is this not supported?

Thanks

Hi,

This is very cool idea but I got difficulties using this library.
Compiling is no problem on CentOS 5.8 with pg 9.1.15
But create extension failed with various errors (not found module path, type column). Having successfully installed extension, I couldn't use it due to ERROR: cache lookup failed for type 0
Only urlencode() works.

Please advise.
Thank you

PS: I edited sql with code below:

CREATE DOMAIN http_method AS text
CHECK (
VALUE ILIKE 'get' OR
VALUE ILIKE 'post' OR
VALUE ILIKE 'put' OR
VALUE ILIKE 'delete'
);

CREATE DOMAIN content_type AS text
CHECK (
VALUE ~ '^\S+/\S+'
);

CREATE TYPE http_header AS (
field VARCHAR,
value VARCHAR
);

CREATE TYPE http_response AS (
status INTEGER,
content_type VARCHAR,
headers http_header[],
content VARCHAR
);

CREATE TYPE http_request AS (
method http_method,
uri VARCHAR,
headers http_header[],
content_type VARCHAR,
content VARCHAR
);

CREATE OR REPLACE FUNCTION http_header (field VARCHAR, value VARCHAR)
RETURNS http_header
AS $$ SELECT $1, $2 $$
LANGUAGE 'sql';

CREATE OR REPLACE FUNCTION http(request http_request)
RETURNS http_response
AS '$libdir/http', 'http_request'
LANGUAGE 'c';

CREATE OR REPLACE FUNCTION http_get(uri VARCHAR)
RETURNS http_response
AS $$ SELECT http(('GET', $1, NULL, NULL, NULL)::http_request) $$
LANGUAGE 'sql';

CREATE OR REPLACE FUNCTION http_post(uri VARCHAR, content VARCHAR, content_type VARCHAR)
RETURNS http_response
AS $$ SELECT http(('POST', $1, NULL, $3, $2)::http_request) $$
LANGUAGE 'sql';

CREATE OR REPLACE FUNCTION http_put(uri VARCHAR, content VARCHAR, content_type VARCHAR)
RETURNS http_response
AS $$ SELECT http(('PUT', $1, NULL, $3, $2)::http_request) $$
LANGUAGE 'sql';

CREATE OR REPLACE FUNCTION http_delete(uri VARCHAR)
RETURNS http_response
AS $$ SELECT http(('DELETE', $1, NULL, NULL, NULL)::http_request) $$
LANGUAGE 'sql';

CREATE OR REPLACE FUNCTION urlencode(string VARCHAR)
RETURNS TEXT
AS '$libdir/http'
LANGUAGE 'c'
IMMUTABLE STRICT;

Hi,
I ran in a situation where it would be nice to be able to disable ssl verification (e.g. self-signed ceritficate).
Any chance to implement the curl runtime options CURLOPT_SSL_VERIFYHOST and CURLOPT_SSL_VERIFYPEER ?
At the moment I get the following message:
ERROR: curl option 'CURLOPT_SSL_VERIFYHOST' is not available for run-time configuration

Thanks

Okay I finally got this extension to work after figuring out my issue.

I have a schema called contrib which is part of my search path.

If I install http extension in the contrib schema and then do something like

SELECT (unnest(headers)).* FROM http_get('http://myserver');

I get error:

ERROR:  cache lookup failed for type 0

However if I then do:

ALTER EXTENSION http SET SCHEMA public;

Then it works fine

I tried compiling against my PostgreSQL 9.5beta2 install and get this error

http.c: In function 'header_array_to_slist':
http.c:272:2: error: too few arguments to function 'array_create_iterator'
  iterator = array_create_iterator(array, 0);
  ^
In file included from http.c:47:0:
c:/MING64~1/projects/POSTGR~1/rel/PG9~2.5W6/include/server/utils/array.h:448:22: note: declared here
 extern ArrayIterator array_create_iterator(ArrayType *arr, int slice_ndim, ArrayMetaState *mstate);
                      ^
<builtin>: recipe for target `http.o' failed
make: *** [http.o] Error 1
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -
http.c: In function 'header_array_to_slist':
http.c:272:2: error: too few arguments to function 'array_create_iterator'
  iterator = array_create_iterator(array, 0);
  ^
In file included from http.c:47:0:
c:/MING64~1/projects/POSTGR~1/rel/PG9~2.5W6/include/server/utils/array.h:448:22: note: declared here
 extern ArrayIterator array_create_iterator(ArrayType *arr, int slice_ndim, ArrayMetaState *mstate);
                      ^
<builtin>: recipe for target `http.o' failed

I'll try to create a patch to fix

The server process always shuts down when executing this statement:

select http_get('http://nominatim.openstreetmap.org/reverse.php?format=json&lat=50.9318&lon=6.9679');

SSL-SYSCALL-Fehler: Dateiende entdeckt
Die Verbindung zum Server wurde verloren. Versuche Reset: Fehlgeschlagen.

postresql logfile:

LOG: Serverprozess (PID 2274) wurde von Signal 11 beendet: Segmentation fault
DETAIL: Der fehlgeschlagene Prozess führte aus: select http_get('http://nominat
im.openstreetmap.org/reverse.php?format=json&lat=50.9318&lon=6.9679');
LOG: aktive Serverprozesse werden abgebrochen
WARNUNG: breche Verbindung ab wegen Absturz eines anderen Serverprozesses
DETAIL: Der Postmaster hat diesen Serverprozess angewiesen, die aktuelle Transaktion zurückzurollen und die Sitzung zu beenden, weil ein anderer Serverprozess abnormal beendet wurde und möglicherweise das Shared Memory verfälscht hat.
TIPP: In einem Moment sollten Sie wieder mit der Datenbank verbinden und Ihren Befehl wiederholen können.

What is the problem? Invoking curl directly with this url works fine.

Use case: Sending emails via Mailgun.

https://documentation.mailgun.com/en/latest/user_manual.html#sending-via-api
https://curl.haxx.se/libcurl/c/curl_mime_init.html

The fact that http_get returns varchar prevents this from being used to return binary data. I was specifically trying to fetch some images from a webserver.

Hi I was trying a simple test with a simple web server using the http_post('http://localhost:8080/test', 'name=foo', '/text/plain') and it does seem to call post on the web server side, but I don't seem to be able to get the value of the parameter passed in correctly. I verified that curl --data "name=foo" http:localhost:8080/test does hit the web server and the values are passed correctly. Is there anything obviously wrong in how I am invoking http_post?

am trying to post request like following:

SELECT status, "content" INTO v_response_code, v_response_content
      FROM http_post(v_redmine_url||'/issues.json'||urlencode('?key='||v_redmine_api_key),
                     format('{"issue": {"project_id": %s, "subject": "%s", "status_id": %s, "description": %s, "tracker_id": }}', v_redmine_project, NEW.id, v_new_status_id, v_subject||' '||v_description, v_crossings_tracker_id),
                     'application/json');

and getting error:
ERROR: Operation timed out after 0 milliseconds with 0 out of 0 bytes received
CONTEXT: SQL statement "SELECT status, "content" FROM http_post(v_redmine_url||'/issues.json'||urlencode('?key='||v_redmine_api_key),
format('{"issue": {"project_id": %s, "subject": "%s", "status_id": %s, "description": %s, "tracker_id": }}', v_redmine_project, NEW.id, v_new_status_id, v_subject||' '||v_description, v_crossings_tracker_id),
'application/json')"

I'm having the follow message on url bellow:
"ERROR: SSL certificate problem: unable to get local issuer certificate"

Query:
select * FROM http_get('https://maps.googleapis.com/maps/api/geocode/json?address=')

When expected result is:
"{
"error_message" : "Invalid request. Missing the 'address', 'bounds', 'components', 'latlng' or 'place_id' parameter.",
"results" : [],
"status" : "INVALID_REQUEST"
}"

Thank you for your efforts.
psql-http library support websocket ?

Sorry for poor english.