can we issue an http get request from inside a pl/pgsql function?
Lee
(analagous to the unix curl ) in postresql; but there doesnt seem to be
any such animal.
That would be a "built in" function which, given a url, goes out the
internet, fetches the response and returns it to the caller as text or a
clob or an xmlType or something.
Would it be fair to say that in postgres "you cant get there from here"
, or at least not without going through some sort of middleware to make
the http request and feed the results into postgresql ?
From the non results I have from poking around, it would seem not, but
I want to do "due diligence" and not say the feature isnt there until
the experienced people confirm that alleged finding.
Ditto httpURIType.
Laurenz Albe
It is impossible to do what you want with only PL/pgSQL.
Also, there is no built-in function that does such a thing.
But it is very easy to write it yourself in PL/Perl.
Perl has modules for exactly that, and you can use them in
a function.
I do not know if that counts as "middleware" for you; I guess
the answer would be "yes" only if you consider the Java engine
in an Oracle database as middleware.
If you prefer PL/pgSQL, you could encapsulate the HTTP call
in a small Perl function which you use from PL/pgSQL.
Yours,
Laurenz Albe
Lee
>>I've been looking for something like Oracle's utl_http.request
<SNIP>
>
> It is impossible to do what you want with only PL/pgSQL.
<SNIP>
>
> If you prefer PL/pgSQL, you could encapsulate the HTTP call
> in a small Perl function which you use from PL/pgSQL.
>
THanks for the reply. THat is actually good news. Here is the larger
context:
We have an ORacle web application (uses the Oracle RDBMS) written
entirely in pl/Sql using the Oracle/plSql gateway with Apache.
It was written as a demo, to illustrate what could be done with a so
called "thick database" approach (most of the heavy lifting done in the
database. In this case, a bit extreme because the HTML GUI comes from
the database too. As often happens, the PTB saw the demo, loved it to
death, and promoted it to production (Arise sir production!) by simply
decreeing that all shall use it. Fech. The original choice to implement
the thing in Oracle was "We already have Oracle installed, we know
Oracle tolerably well, so there's no initial anything. Start writing
the code in our 'spare' time and reveal it when its able to do something"
Well, now the PTB want to do it "for real" and are looking at Postgresql
, (oops! I gotta learn about postgres soonest!) with JRuby. There are
some pretty good reasons for this particular shop to go with JRuby, but
that will take us a bit afield.
One part of the existing app has the user feed an identifier to the
system (via the GUI, of course), which goes out to the web and fetches a
few K of data, which is in a known format, and keyed to the given
identifier. The app transforms the data, stripping it into a flock of
normalized tables. If all goes well, the user can then search for,
discover, browse, edit and generally muck with the newly ingested data
as if it had been loaded the old fashioned way (via the GUI, that is).
Part of the beauty is that even in the new world, all the fetching and
parsing and inserting and would go on in the database itself as a
service to the GUI part, setting JRuby free from having to do any of that.
What I hear you saying is that yes, we can make that happen. Its a bit
different from the way we'ld do it in Oracle, but we expected that.
Different doesnt necessarily mean less effective. In ORacle, there's a
built in package (utl_http) that pretty much does it all for you, but
here. we'ld need to write a perl script and "wrap" it so it can be
called from pl/pgsql (Oracle does something similar with java routines).
Thanks again for the information.