Обсуждение: Accessing Database Data from C Function
Hello, 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? I've looked into C Functions but the examples I've found have talked about performing calculations on the arguments. Table function examples show how I might return more than one row or multi-columned rows, but I don't see how to actually access the data in the database. I apologize if this question is answered somewhere. I suspect it is but I apparently don't know the terminology to look it up and have spent an hour conducting fruitless Google searches. A pointer to such a resource along with the topic I should check out would be more than helpful. Thanks, Jeff
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 Do you have a reason to use C instead of a higher-level language? 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 -- Michael Fuhr
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 >
"Jeffrey Bigham" <jbigham@cs.washington.edu> writes:
> 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?
C should theoretically be faster than any of the alternatives you
mention, all else being equal (eg, you are implementing the identical
algorithm in each language). Whether the difference is enough to notice
is another question --- for example, if effectively all the runtime is
spent inside SQL queries, shaving a few microseconds off the time it
takes you to issue the queries isn't going to improve your life.
You also have to consider the larger effort you'll need to put into
coding in C ... it's a pretty low-level language by any modern standard.
That effort might more profitably be spent elsewhere, eg improving the
SQL queries themselves.
My advice is don't code in C until it's clear you have to. If you can
prototype in plpgsql or plperl or pl-your-favorite-language, do that
first and get it working; and then recode in C if the performance is
so bad you can't stand it (and you can prove that the cycles are
actually spent in your PL code and not somewhere else like the SQL
engine). About the only time I'd not do it that way is if my problem
involves data structures too complex to express nicely in
my-favorite-pl or if I need access to low-level database details that
aren't exposed by my-favorite-pl.
regards, tom lane
On Aug 8, 2006, at 8:33 PM, Jeffrey Bigham wrote: > >> 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? It depends on what you're doing. I've found plpgsql to be a better match for a lot of purposes, as it has a much better "impedance match" with the database. I'm a happy C hacker, and use C for PG functions where it seems appropriate, but use plpgsql for >90% of my in database work. Performance is probably marginally slower than C, but development time for day-to-day triggers and functions is a lot faster. Much easier to maintain, too. Cheers, Steve
On 8/8/06, Jeffrey Bigham <jbigham@cs.washington.edu> wrote: > 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? IMO, pl/pgsql should be the first sp language you learn and the primary language of development for strored procedures. It is simply better than most other pl languages for data processing and ease of maitenance. There are two reasons why you might want to use C: one is that you might want to interface with a C library and make it callable from SQL. You should check out Magnus Hagander's integration of OpenLDAP for a good example of this (check his blog). Note that Magnus who is in my opinion an expert C programmer only did this after trying ther perl implementation and finding it unworkable. another reason to use C is to expose some database internals. for example, in C you can invoke a function by it's oid which is currently impossible to do in sql/plpgsql without some serious acrobatics. Aside from that, I would avoid. One of the reasons databases were invented was so we don't have to use C for data processing (or COBOL, etc.), or at least not until the hard work has already been done. merlin