Re: Accessing Database Data from C Function

On 8/8/06, Michael Fuhr <mike@fuhr.org> wrote:
> On Tue, Aug 08, 2006 at 12:16:18PM -0700, jeffrey.bigham@gmail.com wrote:
> > I'd like to write a C Function for Postgresql that can access data in
> > the database, use that to conduct additional queries to find more
> > information in the database and eventually return a result.  I
> > currently have the functionality I want implemented as php/C programs
> > that first connect to the database, do the processing and then
> > disconnect, but I want to make it what I think is called a "stored
> > procedure" in other databases.  It seems odd that I would write a C
> > program to be included in the database that connects to  the localhost
> > - is that what I should do?  Is that somehow more efficient than
> > running it as a separate process?
>
> Server-side functions written in C can use the Server Programming
> Interface (SPI) to query the database.
>
> http://www.postgresql.org/docs/8.1/interactive/spi.html

Thanks, that's exactly what I was looking for.

> Do you have a reason to use C instead of a higher-level language?

Not really.  Basically I know C decently enough and would have to
learn PL/pgSQL (although it looks pretty easy).  Perl sounds quite
attractive for the added benefit of text-processing as you mention.  I
also have some vague understanding that C might be faster.  I hope I'm
not opening a can-of-worms, but is C actually faster?

> Functions that are mostly queries are probably best done in SQL or
> PL/pgSQL, while text processing and some OS-level access (e.g.,
> reading and writing files) can be done with PL/Perl, PL/Tcl,
> PL/Python, PL/Ruby, PL/R, etc.  There's even a third-party PL/php
> if that's your preferred language:
>
> http://projects.commandprompt.com/public/plphp

Thanks!
Jeff

> --
> Michael Fuhr
>