Re: PL/pgSQL 2

On 09/02/2014 11:42 AM, David Johnston wrote:
> 
> ​Yet pl/pgsql does not have to use SPI-interface type calls to interact
> with PostgreSQL at the SQL level...

That's right.

> ​I don't have an answer to your questions but the one I'm asking is
> whether a particular language could hide all of the SPI stuff behind
> some custom syntax so that it in effect looks similar to what pl/pgsql
> does today?

Sure, it's possible. Have you seen Microsoft's "LINQ" for C# / .NET? One
of the few really awesome things they've done in a long time.

Do you see many other languages with good seamless SQL support, though?
The only one I can think of off the top of my head is PROGRESS/4GL, and
like PL/PgSQL it's designed around the idea of being a
database-integrated language.

The hard problem to solve here isn't "how do I make the language talk to
the database server backend". That's easy. The hard problem is "how
should the integrated SQL support look and work so it fits in with the
language while remaining as powerful and expressive as SQL its self".

It's *the same* problem as if you were using the same language via a
network connection to the database, instead of embedding it. How do you
make SQL syntax interoperate with your language's syntax, or produce a
clean-ish adapter layer?

Lots of people have tried. Most have failed dismally. ActiveRecord
becomes gruesomely ugly hodgepodge of text snippets and code as soon as
you try to do anything interesting/nontrivial with it. Hibernate
Criteria Query - ever wanted to write 100 lines of code for a simple
query? You'll love it. JOOQ? Django ORM? SQLAlchemy? iBatis/MyBatis?
QueryDSL? They're all ugly in various ways.

The underlying problem is that SQL, syntactically speaking, is a weird,
clunky way to do things that sticks out like a sore thumb when you put
it together with a language designed after 1975.

Also, most of the "modern" languages people want to use are to some
degree OO, not just procedural. That brings the OO/relational mismatch
into play, and there's no pretty and easy solution for that.

http://en.wikipedia.org/wiki/Object-relational_impedance_mismatch

PL/PgSQL gets away with it by not being OO - it fits its model around
that of SQL.

If you can solve that problem - create a smooth, seamless
language-integrated way to use SQL in any language of your choice - then
please, please come talk about it here.

> Or, more to the point, does pl/pgsql use the same SPI
> interface behind the scenes as PL/Perl or does it have its own special
> interface?

Take a look.

cd src/pl/pgsql/src

You'll see pl_handler.c, the PL/PgSQL procedural language handler.
There's also pl_exec.c, containing the guts of the language runtime. SPI
use is heavy throughout.

PL/PgSQL uses SPI to execute queries and fetch results. See, for
example, exec_stmt_execsql(...) in pl_exec.c .

Any other language that can run embedded into the PostgreSQL backend can
do the same.

PL/Python does it:

http://www.postgresql.org/docs/current/static/plpython-database.html

(though unfortunately the PL/Python interface for SQL does't follow the
Python DB-API).

PL/V8, PL/Lua, PL/Ruby, they all use the SPI. Same as PL/PgSQL.

The challenge isn't dispatching the SQL, the challenge is fitting SQL
into the language seamlessly.

-- Craig Ringer                   http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services