DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:date:message-id:subject:from:to:content-type
	:content-transfer-encoding;
	b=hG6/sjiWHvWPgc+Pu4kWOQ1bKjb34y76L1m9YIkOmhGKYt0F4sUM5klj9s1GbpmoxN
	MCYg3afUyC54VMndz43Os0Soa8Jbakzag4zNMfiiarN9tuNCYdSV6rUSnVTixDQVagLQ
	xG8UuNWNj/gKQtgtYz54Kaka0gwTngeCBoNV0=
MIME-Version: 1.0
Date: Mon, 23 Feb 2009 19:04:04 -0600
Message-ID: <fedea56b0902231704w531b26a9hbba53dea7956eedf@mail.gmail.com>
Subject: C Stored Function with ECPG
From: Bob Henkel <bob.henkel@gmail.com>
To: pgsql-interfaces@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

Sorry if this double posts. My first attempt was stalled because I was
not a member of the list. So now I am.

I'm starting to experiment with writing some stored functions in C and
find it really handy.  I have a simple C function that takes two
integers and returns the sum.  This works fine. Now I want to create a
function that makes us of some embedded ECPG. So far I have failed at
my first attempt. Below are the steps and outputs of what I did.  I
assume it's feasible to embedd ECPG in a Postgres Stored function
written in C?

My foo1.pcg file looks like this.
#include "postgres.h"
#include  <string.h>
#include "fmgr.h"

#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif


PG_FUNCTION_INFO_V1(foo1);

Datum foo1(PG_FUNCTION_ARGS)
{
   EXEC SQL CREATE TABLE foo1 (c1 integer NOT NULL, c2 varchar(50) NOT NULL);

   PG_RETURN_INT32(0);
}

1. I run ecpg foo1.pcg and this generates my foo1.c file. I get no
warnings or errors.
[postgres@PortJackson ~]$ ecpg foo1.pgc
[postgres@PortJackson ~]$

2. I compile foo1.c and get some warnings.  Are these warnings ok?
[postgres@PortJackson ~]$  gcc -g -fpic -c foo1.c
-I/usr/home/postgres/postgresql-8.3.5/src/include/
-I/home/postgres/postgresql-8.3.5/src/include/libpq/
-I/usr/home/postgres/postgresql-8.3.5/src/interfaces/ecpg/include/
-I/usr/local/pgsql/include/
foo1.pgc: In function 'foo1':
foo1.pgc:14: warning: passing argument 5 of 'ECPGdo' makes integer
from pointer without a cast
foo1.pgc:14: warning: passing argument 7 of 'ECPGdo' makes pointer
from integer without a cast
[postgres@PortJackson ~]$

3.I link foo1. With now warnings or errors.
[postgres@PortJackson ~]$ gcc -shared -o foo1.so foo1.o
-L/usr/local/pgsql/lib -lecpg
[postgres@PortJackson ~]$


4. I create a function in psql to call my new library. And get a could
not load library error for file libecpg.so.6.  How do I fix this
error?
dev=# CREATE OR REPLACE FUNCTION foo1(integer)
 RETURNS integer AS
'/usr/home/postgres/foo1', 'foo1'
 LANGUAGE 'c' VOLATILE STRICT
 COST 1;
ERROR:  could not load library "/usr/home/postgres/foo1.so": dlopen
(/usr/home/postgres/foo1.so) failed: Shared object "libecpg.so.6" not
found, required by "foo1.so"
dev=#

I'm new to C functions and to ECPG. So mixing the two at this point
for me is probably asking for trouble.  Any help would be much
appreciated.

Thanks
Bob