Thread: language interface in postgresql

Hi,

I was wondering if any other database has language interface for different programing languages such as C , perl , python etc the way postgresql has.

Thanks,
Jas

On 8/14/07, Jasbinder Singh Bali <jsbali@gmail.com> wrote:

> I was wondering if any other database has language interface for different
> programing languages such as C , perl , python etc the way postgresql has.

Assuming you mean stored procedures, Microsoft's SQL Server hosts the
CLR, which means pretty much any language capable of targeting .NET
will work.  The mechanisms behind this are quite involved, to
guarantee different levels of security and reliability; it's rather
interesting to dig into.

It is just one platform though, so might not be quite what you had in mind :)

Hi,
Let me fine tune my question here. What I mean to say is the way we can write stored procedures in C, perl etc in Postgres specifying the language parameter at the end of stored procedure, compared to that, in SQL Server 2000 I've seen SP writing in pure SQL only.
Can you write Stored Procedures in SQL Server 2000 or Oracle in different programing languages as well ?

Thanks
~Jas

"Jasbinder Singh Bali" <jsbali@gmail.com> writes:
> Let me fine tune my question here. What I mean to say is the way we can
> write stored procedures in C, perl etc in Postgres specifying the language
> parameter at the end of stored procedure, compared to that, in SQL Server
> 2000 I've seen SP writing in pure SQL only.

Ah.  I thought you were talking about client interface libraries in
different languages, which are surely a dime a dozen.  As far as
server-side functions go, we might be unique in offering so many
languages to work in.  I think we probably are unique in being so
aggressively agnostic about what the function language is.  That's
not necessarily all good, as it's driven us to invent curiosities
like dollar-quoting to avoid having to mesh lexical details of the
function language and the outer SQL language.

            regards, tom lane

On 8/14/07, Jasbinder Singh Bali <jsbali@gmail.com> wrote:

> Let me fine tune my question here. What I mean to say is the way we can
> write stored procedures in C, perl etc in Postgres specifying the language
> parameter at the end of stored procedure, compared to that, in SQL Server
> 2000 I've seen SP writing in pure SQL only.
> Can you write Stored Procedures in SQL Server 2000 or Oracle in different
> programing languages as well ?

AFAIK SQL Server 2000 only has a C interface as the other option; CLR
hosting was added in SQL Server 2005.  Because the CLR is a virtual
machine that runs compiled bytecode, and compilers for all of the
available languages are not necessarily available at runtime, it
doesn't make sense to specify such code in source form.  The process
is more like creating a function in C in PostgreSQL (compile and load
a shared library).  Details here, if you're curious:
http://msdn2.microsoft.com/en-us/library/ms345136.aspx

I don't know what Oracle supports.

I'm certainly not familiar with all of the databases out there, but I
can't think of any offhand that support different languages in the
same form as PostgreSQL.  Aside from VM platforms like the CLR, I
can't think of any with the same range either.  Most databases seem to
pick a specific language or two in script-style form, and possibly
support binary extensions (written in C or some other native language)
for work beyond that.

On 8/14/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Jasbinder Singh Bali" <jsbali@gmail.com> writes:
> > Let me fine tune my question here. What I mean to say is the way we can
> > write stored procedures in C, perl etc in Postgres specifying the language
> > parameter at the end of stored procedure, compared to that, in SQL Server
> > 2000 I've seen SP writing in pure SQL only.
>
> Ah.  I thought you were talking about client interface libraries in
> different languages, which are surely a dime a dozen.  As far as
> server-side functions go, we might be unique in offering so many
> languages to work in.  I think we probably are unique in being so
> aggressively agnostic about what the function language is.  That's
> not necessarily all good, as it's driven us to invent curiosities
> like dollar-quoting to avoid having to mesh lexical details of the
> function language and the outer SQL language.

Well, I for one LOVE $$ quoting in the newer versions of pgsql.
Having statements with ''''''''value'''''''' in the, was downright
annoying in 7.4...

I think the real issue with all the pl languages is the variations in
quality of implementations out there.  And some don't always make it
to the next release, or if they do they lag behind.  I believe plsh
was quite late to show up for 8.0 back in the day...  If you're
relying on a particular pl{lang} you kinda want to check to see if
it's still working on the latest pg version before upgrading.

"Scott Marlowe" <scott.marlowe@gmail.com> writes:
> On 8/14/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> ...  I think we probably are unique in being so
>> aggressively agnostic about what the function language is.  That's
>> not necessarily all good, as it's driven us to invent curiosities
>> like dollar-quoting to avoid having to mesh lexical details of the
>> function language and the outer SQL language.

> Well, I for one LOVE $$ quoting in the newer versions of pgsql.
> Having statements with ''''''''value'''''''' in the, was downright
> annoying in 7.4...

You bet, but the reason why it was like that was we insisted on the
function body being a string literal in the eyes of the outer CREATE
FUNCTION command.  Anytime the function's language had the same ideas
about string quoting and escaping rules as the outer SQL language does,
you were in for some recursively bad experiences.

Dollar-quoting is a cute technical solution to that, but you can't deny
that it's simpler if you just restrict the function language to be
SQL-ish so that CREATE FUNCTION can parse it without any interesting
quoting rules.  So sayeth Oracle and the SQL standards committee,
anyway.

            regards, tom lane

Trevor Talbot wrote:
> On 8/14/07, Jasbinder Singh Bali <jsbali@gmail.com> wrote:
>
>> Let me fine tune my question here. What I mean to say is the way we can
>> write stored procedures in C, perl etc in Postgres specifying the language
>> parameter at the end of stored procedure, compared to that, in SQL Server
>> 2000 I've seen SP writing in pure SQL only.
>> Can you write Stored Procedures in SQL Server 2000 or Oracle in different
>> programing languages as well ?
>
> AFAIK SQL Server 2000 only has a C interface as the other option; CLR
> hosting was added in SQL Server 2005.  Because the CLR is a virtual
> machine that runs compiled bytecode, and compilers for all of the
> available languages are not necessarily available at runtime, it
> doesn't make sense to specify such code in source form.  The process
> is more like creating a function in C in PostgreSQL (compile and load
> a shared library).  Details here, if you're curious:
> http://msdn2.microsoft.com/en-us/library/ms345136.aspx
>
> I don't know what Oracle supports.

I believe Oracle support Java in the same way MSSQL supports .net, give
or take.

And IIRC the method is you build a DLL on your client and upload it to
the server so no, source not specified int he CREATE PROCEDURE call.

//Magnus

On 8/15/07, Magnus Hagander <magnus@hagander.net> wrote:
> Trevor Talbot wrote:
> > On 8/14/07, Jasbinder Singh Bali <jsbali@gmail.com> wrote:
> >
> >> Let me fine tune my question here. What I mean to say is the way we can
> >> write stored procedures in C, perl etc in Postgres specifying the language
> >> parameter at the end of stored procedure, compared to that, in SQL Server
> >> 2000 I've seen SP writing in pure SQL only.
> >> Can you write Stored Procedures in SQL Server 2000 or Oracle in different
> >> programing languages as well ?
> >
> > AFAIK SQL Server 2000 only has a C interface as the other option; CLR
> > hosting was added in SQL Server 2005.  Because the CLR is a virtual
> > machine that runs compiled bytecode, and compilers for all of the
> > available languages are not necessarily available at runtime, it
> > doesn't make sense to specify such code in source form.  The process
> > is more like creating a function in C in PostgreSQL (compile and load
> > a shared library).  Details here, if you're curious:
> > http://msdn2.microsoft.com/en-us/library/ms345136.aspx
> >
> > I don't know what Oracle supports.
>
> I believe Oracle support Java in the same way MSSQL supports .net, give
> or take.

I don't know specifics of what exactly you can do with it nor how
exactly you go about it, but I know at least Oracle 10g supports
extension in Java. We had grand plans to improve some sort of
processing by writing a comparison function in Java for one the Oracle
databases I've been cursed to associate with. I don't know of any
other languages supported by Oracle for stored procedures and the
like, though it does have an ECPG-like system for C, C++, COBOL,
FORTRAN, and PL/1 of all things.

- Josh

- Josh

On Wed, Aug 15, 2007 at 01:10:15AM -0400, Tom Lane wrote:
> "Scott Marlowe" <scott.marlowe@gmail.com> writes:
> > On 8/14/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >> ...  I think we probably are unique in being so aggressively
> >> agnostic about what the function language is.  That's not
> >> necessarily all good, as it's driven us to invent curiosities
> >> like dollar-quoting to avoid having to mesh lexical details of
> >> the function language and the outer SQL language.
>
> > Well, I for one LOVE $$ quoting in the newer versions of pgsql.
> > Having statements with ''''''''value'''''''' in the, was downright
> > annoying in 7.4...
>
> You bet, but the reason why it was like that was we insisted on the
> function body being a string literal in the eyes of the outer CREATE
> FUNCTION command.  Anytime the function's language had the same
> ideas about string quoting and escaping rules as the outer SQL
> language does, you were in for some recursively bad experiences.
>
> Dollar-quoting is a cute technical solution to that, but you can't
> deny that it's simpler if you just restrict the function language to
> be SQL-ish so that CREATE FUNCTION can parse it without any
> interesting quoting rules.  So sayeth Oracle and the SQL standards
> committee, anyway.

I think the aforementioned entities got it wrong where we got it right :)

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
phone: +1 415 235 3778        AIM: dfetter666
                              Skype: davidfetter

Remember to vote!
Consider donating to PostgreSQL: http://www.postgresql.org/about/donate

David Fetter wrote:
>> Dollar-quoting is a cute technical solution to that, but you can't
>> deny that it's simpler if you just restrict the function language to
>> be SQL-ish so that CREATE FUNCTION can parse it without any
>> interesting quoting rules.  So sayeth Oracle and the SQL standards
>> committee, anyway.
>
> I think the aforementioned entities got it wrong where we got it right :)
>

Though Oracle says[1] 'Java-based stored procedures are a standard (ANSI SQL-1999
SQLJ-Part-1 a.k.a. "SQL routines using Java") database-independent alternative
to proprietary procedural extensions to SQL'; so I guess they did both think
of some flexibility in that regard, no?

[1] http://www.oracle.com/technology/sample_code/tech/java/jsp/samples/jwcache/Abstract.html