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.
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:
Thanks!
Jeff
Show quoted text
--
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