Обсуждение: Is it possible to call Postgres directly?

I am attempting to create a new language implementation. The language is
Andl (see http://www.andl.org/2016/04/postgres-meet-andl/).

I would like to be able to execute SQL queries by a direct call into
Postgres, without going through either (1) the language call handler or (2)
a 'wire' protocol.

Re (1): At present I can do it like this:

SELECT * FROM COMPILE($$
<Andl code goes here>
$$);

But I would like to avoid the outer SQL SQL wrapper.

Re (2): Those queries will contain Andl functions, which require a callback
into the same session of the language 'engine'.

I guess what I'm trying to do is provide a substitute for existing wire
protocols, using either a Thrift server or a Web server calling directly
into Postgres.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org

<david@andl.org> writes:
> I would like to be able to execute SQL queries by a direct call into
> Postgres, without going through either (1) the language call handler or (2)
> a 'wire' protocol.

What would you consider to be "directly"?  What advantage would you
hope to gain from a different API?

FWIW, it seems unlikely to me that we would consider anything much
lower-level than SPI to be an officially-supported query interface.
You could no doubt run a query by calling assorted backend functions
directly from a PL implementation, but it would be mostly on your
own head whether that worked and kept working across releases.

            regards, tom lane

On 4/20/2016 7:27 PM, david@andl.org wrote:
> Re (1): At present I can do it like this:
>
> SELECT * FROM COMPILE($$
> <Andl code goes here>
> $$);
>
> But I would like to avoid the outer SQL SQL wrapper.


so all your top level application queries are of the form SELECT * from
FUNCTION() ?

thats not going to do a very good job on things like joins, aggregates,
etc, your ANDL is going to be completely reinventing the wheel, its own
query optimizer, figuring out how to use indexes, all the postgres
datatypes and things like postGIS, etcetcetc ?

huh.



--
john r pierce, recycling bits in santa cruz

> > I would like to be able to execute SQL queries by a direct call into
> > Postgres, without going through either (1) the language call handler
> > or (2) a 'wire' protocol.
>
> What would you consider to be "directly"?  What advantage would you hope
to
> gain from a different API?

The aim is to execute Andl code at the top level, not inside SQL. So instead
of:

>>>SELECT * FROM AndlFunc(args);

I need

>>>AndlFunc(args)

> FWIW, it seems unlikely to me that we would consider anything much lower-
> level than SPI to be an officially-supported query interface.
> You could no doubt run a query by calling assorted backend functions
directly
> from a PL implementation, but it would be mostly on your own head whether
> that worked and kept working across releases.

No, SPI is quite low enough level for me. I really don't want or need to go
lower than that.

So the question is: Can a C program link to the Postgres DLL and call SPI
directly, rather than through a language function?

Is there a way to launch a Thrift server or a Web server and call SPI
directly?

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org

On 4/20/2016 10:59 PM, david@andl.org wrote:
> So the question is: Can a C program link to the Postgres DLL and call SPI
> directly, rather than through a language function?
>
> Is there a way to launch a Thrift server or a Web server and call SPI
> directly?


no, those can only be called from the context of a function that was
called from SQL as part of a query.

if you want to completely replace SQL as the query language, why don't
you just provide your own Andl API, and have it run in the client
context and execute SQL queries via the normal libpq style API ?

you could still use pl/andl for things like triggers and such but not
for direct client queries.


--
john r pierce, recycling bits in santa cruz

<david@andl.org> writes:
> So the question is: Can a C program link to the Postgres DLL and call SPI
> directly, rather than through a language function?

No, there is no provision for non-server usage of the code.  If that's
what you're after you might be happier with SQLite or something similar.

            regards, tom lane

That's OK. I can set things up so that the Thrift or Web servers call the
Andl runtime directly, pass generated SQL queries in through libpq, and call
the Andl runtime recursively from the plandl handler as needed. It's just
one more API to deal with.

Thanks for the suggestion, but I already did an Sqlite implementation. It
was the obvious place to start, but now I need a real server.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org


> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: Thursday, 21 April 2016 11:57 PM
> To: david@andl.org
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Is it possible to call Postgres directly?
>
> <david@andl.org> writes:
> > So the question is: Can a C program link to the Postgres DLL and call
> > SPI directly, rather than through a language function?
>
> No, there is no provision for non-server usage of the code.  If that's
what
> you're after you might be happier with SQLite or something similar.
>
>             regards, tom lane