Thread: ECPG in stored procedures?
Reading up on ECPG, I figured it would be nice to be able to use that in
stored procedures. I couldn't find anything about such in the
documentation though.
So, a few questions:
Is it possible?
If so, is it feasable? Is the performance comparable to using libpq
calls directly, for example. What are the cons and pros?
--
Alban Hertroys
alban@magproductions.nl
magproductions b.v.
T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede
//Showing your Vision to the World//
Am Montag, 5. September 2005 11:42 schrieb Alban Hertroys: > Reading up on ECPG, I figured it would be nice to be able to use that in > stored procedures. The purpose of ECPG is to provide a smooth interface for hooking in SQL calls into C programs. If you want to have that kind of smoothness for stored procedures, then don't write them in C in the first place. You may find that PL/pgSQL gives you a very similar interface to ECPG. -- Peter Eisentraut http://developer.postgresql.org/~petere/
Peter Eisentraut wrote:
> Am Montag, 5. September 2005 11:42 schrieb Alban Hertroys:
>
>>Reading up on ECPG, I figured it would be nice to be able to use that in
>>stored procedures.
>
>
> The purpose of ECPG is to provide a smooth interface for hooking in SQL calls
> into C programs. If you want to have that kind of smoothness for stored
> procedures, then don't write them in C in the first place. You may find that
> PL/pgSQL gives you a very similar interface to ECPG.
We did so far, but we're looking into getting a better performance out
of some of our stored procedures. There are a number that happily
cascade away that could probably be a bit faster. I realize C or
PL/PGSQL won't make much difference if the SPI is basically just doing
queries, but there are some that do various types of math.
The "problem" with calling libpq functions directly is that a small
mistake allocating or freeing a variable is made easily - eventually
resulting in de database server crashing (does it? Our sysadmin says so,
but...). It looks like ECPG could take a lot of that risc away, and make
programming easier along the way.
Next to that, the very similar interface is exactly why I would like to
use ECPG. It could cut down development times.
If it's not possible to use ECPG for this, too bad. Then we'll have to
try with libpq (I doubt my boss would give us time to create a free
implementation of a PL/ECPG or something along the lines). I'd just like
to know...
Regards,
--
Alban Hertroys
alban@magproductions.nl
magproductions b.v.
T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede
//Showing your Vision to the World//
Am Montag, 5. September 2005 13:37 schrieb Alban Hertroys: > If it's not possible to use ECPG for this, too bad. Then we'll have to > try with libpq (I doubt my boss would give us time to create a free > implementation of a PL/ECPG or something along the lines). I'd just like > to know... If you are using libpq for writing stored procedures, you're doing something wrong. libpq is for client-side access. -- Peter Eisentraut http://developer.postgresql.org/~petere/
Peter Eisentraut wrote: > If you are using libpq for writing stored procedures, you're doing something > wrong. libpq is for client-side access. It seems the documentation doesn't tell what library to link, so I guessed wrong. Searching the FAQs or my mailbox doesn't help much either... Which lib are we supposed to use for SPI? -- Alban Hertroys
Am Montag, 5. September 2005 14:39 schrieb Alban Hertroys: > It seems the documentation doesn't tell what library to link, so I > guessed wrong. Searching the FAQs or my mailbox doesn't help much > either... Which lib are we supposed to use for SPI? None. Your code will be compiled into a shared object that is loaded into the backend server which provides the required functions. -- Peter Eisentraut http://developer.postgresql.org/~petere/