Thread: Help requd in writing functions in C and using in Postgres
Hi,
Can you help me in writing functions in C and the using in Postgres.
would like to know some pointers and what all needs to be taken into considerations.
I'm kind of confused how to do it
~Jas
On Tue, Jun 20, 2006 at 11:23:33AM -0400, Jasbinder Bali wrote: > Can you help me in writing functions in C and the using in Postgres. > would like to know some pointers and what all needs to be taken into > considerations. Are you writing server-side or client-side code? For server-side see "C-Language Functions" in the documentation: http://www.postgresql.org/docs/8.1/interactive/xfunc-c.html For examples you could look through the PostgreSQL source code, in particular the files under contrib and in src/backend/utils/adt. If you're writing client-side code then see "libpq - C Library": http://www.postgresql.org/docs/8.1/interactive/libpq.html If that doesn't help then please provide more information about what you'd like to do or what concerns you have. -- Michael Fuhr
[Please copy the mailing list on replies so others can participate in and learn from the discussion.] On Tue, Jun 20, 2006 at 11:39:03AM -0400, Jasbinder Bali wrote: > Well, as in normal Sql server or oracle Stored procedures, you write a > procedure in the database server and some middle tier would invoke it. > Now here what is the concept of server side or client side function in > postres if i just have to create a function in C and then would like my > Stored procedure to invoke it. Also all the DB related activities like > select, delete, update should be taken care of by the C function itself. It sounds like you want to write a server-side function that a client application or another server-side function could invoke with SELECT do_stuff() (or PERFORM do_stuff() in PL/pgSQL). You can do that in C, but unless you need the kind of low-level functionality that only C can provide then you'd probably be better off using a higher level server-side language like PL/pgSQL, PL/Perl, PL/Python, PL/Tcl, PL/Ruby, PL/Java, PL/R, etc. Functions in PostgreSQL aren't the same as stored procedures in some other DBMSs. For example, a function can't start or end a transaction since it's already being executed in the context of a transaction, although in 8.0 and later a function can do partial rollbacks via exception handling. However, a function could connect to the database via dblink, Perl DBI, etc., and then do anything that an ordinary client could do. -- Michael Fuhr
Does that mean I can't make a function in C that will have all the database transactions and then would just be included in the Stored Procedure in postgres (though i know there is no concept of stored procs in postgres, its all functions that you have in it).
If this is not the case then i think its just a normal function that'll have some sort of business logic and would be included in a postgres function instead of writing that function in postgres itself.
Please correct me if I am wrong and please add some more knowledge here so that my doubts are clearified.
Also when i say include <postgres.h>, my function would not find it coz its in some different path altogether. Where exactly is this postgres.h and other postgres header files??
Regards,
~Jas
On 6/20/06, Michael Fuhr <mike@fuhr.org> wrote:
[Please copy the mailing list on replies so others can participate
in and learn from the discussion.]
On Tue, Jun 20, 2006 at 11:39:03AM -0400, Jasbinder Bali wrote:
> Well, as in normal Sql server or oracle Stored procedures, you write a
> procedure in the database server and some middle tier would invoke it.
> Now here what is the concept of server side or client side function in
> postres if i just have to create a function in C and then would like my
> Stored procedure to invoke it. Also all the DB related activities like
> select, delete, update should be taken care of by the C function itself.
It sounds like you want to write a server-side function that a
client application or another server-side function could invoke
with SELECT do_stuff() (or PERFORM do_stuff() in PL/pgSQL). You
can do that in C, but unless you need the kind of low-level
functionality that only C can provide then you'd probably be better
off using a higher level server-side language like PL/pgSQL, PL/Perl,
PL/Python, PL/Tcl, PL/Ruby, PL/Java, PL/R, etc.
Functions in PostgreSQL aren't the same as stored procedures in
some other DBMSs. For example, a function can't start or end a
transaction since it's already being executed in the context of a
transaction, although in 8.0 and later a function can do partial
rollbacks via exception handling. However, a function could connect
to the database via dblink, Perl DBI, etc., and then do anything
that an ordinary client could do.
--
Michael Fuhr
On Tue, Jun 20, 2006 at 02:03:06PM -0400, Jasbinder Bali wrote: > Does that mean I can't make a function in C that will have all the database > transactions and then would just be included in the Stored Procedure in > postgres (though i know there is no concept of stored procs in postgres, its > all functions that you have in it). Functions are executed in the context of an outer transaction, so if you need multiple transactions then you'll have to initiate them from the client side. However, as I mentioned, a server-side function could connect to the database and act as a client, so that would be one way to execute multiple transactions via a server-side function. A possible drawback of doing so is that if the function is called from a transaction that's later rolled back, the statements that the function executed over its client connection would have already been committed and thus wouldn't be rolled back. > Also when i say include <postgres.h>, my function would not find it coz its > in some different path altogether. Where exactly is this postgres.h and > other postgres header files?? The "C-Language Functions" documentation I previously sent explains how to find include files, but if you use the PGXS build infrastructure then you don't need to worry about it. http://www.postgresql.org/docs/8.1/interactive/xfunc-c.html#AEN32313 http://www.postgresql.org/docs/8.1/interactive/xfunc-c.html#DFUNC http://www.postgresql.org/docs/8.1/interactive/xfunc-c.html#XFUNC-C-PGXS -- Michael Fuhr