[kiwicopple]

> speaks a particular API over the network

it's a interesting idea, and one of the things that we were toying with in our pg_net extension (https://github.com/supabase/pg_net). This is a "generic" async network extension, so you can fetch/put/post. It works well for APIs.

I think the generic approach works for some things where the data is less "fixed" - for example, an OpenAI API endpoint.

But for "fixed" data (data warehouses), the wrapper usually needs some custom work for security, protocols, and "push down". I'll be interested to get HN's take on this - they might have some suggestions for us for this framework

[atonse]

I don't think so... in your example those are functions we just call in SQL right? I'm talking about exposing arbitrary data as a table like an FDW, but without having to compile a whole module.

So more like, (this is a bit contrived but bear with me):

Let's say I wanted to make a table that lists all my keybase proofs (so sad Keybase is basically dead, but I digress).

I'd build a graphql endpoint where you can query those proofs.

Then I'd have to define a metadata endpoint that maybe returns a JSON schema?

Then you can tell the FDW to define a virtual table that (as much as possible) uses the JSON schema to generate columns in this virtual table.

You'd define the FDW to say, here's the virtual table (like you'd do now), and then put in an option to say "you'll get the mappings for this table with the JSON schema returned by this metadata endpoint" and then tell the FDW "send select queries to this REST/graphql endpoint" which would be something in our example that returns keybase proofs).

I wouldn't need to build a whole new keybase FDW. I'd just use the generic one to hit that existing endpoint.

Is that making sense? It's kind of a dynamic FDW that can be configured for the long tail of API endpoints.

[kiwicopple]

Yes that makes sense.

> define a metadata endpoint that maybe returns a JSON schema

We could use an OpenAPI spec[0], which many REST services expose as a JSON endpoint. I think that could work: develop a generic "OpenAPI" FDW, which you can connect like this:

    create server example_api
      foreign data wrapper example_api_wrapper
      options (
        open_api_url 'https://api.example.com/'
      );

Then we'd map:

    GET -> SELECT (which would map selected columns into a table)
    POST -> INSERT
    PATCH -> UPDATE
    DELETE -> DELETE

[0] OpenAPI: https://swagger.io/specification/

[atonse]

Right!

I’m wondering if this is how multicorn works even now. It seems to be an out of process thing. I wasn’t aware of multicorn before.

But python… wish it was elixir :-)