Re: 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(at)fuhr(dot)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