Thread: Stored procedures in C

Stored procedures in C

From

"Emiliano Moscato"

Date:

23 April 2008, 19:33:08

Hi all,

I have to do some stuff writing stored procedures for Postgres in C. I saw the oficial documentation but it was hard for me to find out how to do a simple function, let's call it "query()" , that receives a string and uses this string to do a query and return the results. Has anyone some examples?
I am looking at crosstab() example at contrib/tablefunc/tablefunc.c, but it is hard to follow, cause it does more complicated stuff.
Thanks in advance...
Regards,

Emiliano
--
mOsKi
"No hay nada que uno haga mal , lo que hay es poco vino." Autor Anonimo

Re: Stored procedures in C

From

"Martin Gainty"

Date:

23 April 2008, 22:41:53

Emiliano and Mike

The real challenge is trying to determine what a datatype is in cobol..for that matter what is stack variable or heap in Cobol?
In the end you're better off <re>writing this mess (preferably in Java)..

unless of course you need the billable hours for

the first rewrite to C
then later rewrite to Java

(That should get you at least 2 weeks worth of work..)

Buena Suerte/Good Luck
Martin

----- Original Message -----
From: Emiliano Moscato
To: pgsql-general@postgresql.org
Sent: Wednesday, April 23, 2008 3:32 PM
Subject: [GENERAL] Stored procedures in C

Hi all,

I have to do some stuff writing stored procedures for Postgres in C. I saw the oficial documentation but it was hard for me to find out how to do a simple function, let's call it "query()" , that receives a string and uses this string to do a query and return the results. Has anyone some examples?
I am looking at crosstab() example at contrib/tablefunc/tablefunc.c, but it is hard to follow, cause it does more complicated stuff.
Thanks in advance...
Regards,

Emiliano
--
mOsKi
"No hay nada que uno haga mal , lo que hay es poco vino." Autor Anonimo

Re: Stored procedures in C

From

"Andrej Ricnik-Bay"

Date:

24 April 2008, 02:39:35

On 24/04/2008, Emiliano Moscato <moski666@gmail.com> wrote:
> I have to do some stuff writing stored procedures for Postgres in C. I saw
> the oficial documentation but it was hard for me to find out how to do a
> simple function, let's call it "query()" , that receives a string and uses
> this string to do a query and return the results. Has anyone some examples?

You don't mentioned where else you looked - did you come across these?

http://linuxgazette.net/139/peterson.html

http://www.faqs.org/docs/ppbook/x15284.htm

http://www.observercentral.net/~selkovjr/postgres/tutorial/html/exttut-getstart.html



> Thanks in advance...
> Regards,
>
> Emiliano

Cheers,
Andrej


--
Please don't top post, and don't use HTML e-Mail :}  Make your quotes concise.

http://www.american.edu/econ/notes/htmlmail.htm

Re: Stored procedures in C

From

"Emiliano Moscato"

Date:

24 April 2008, 12:45:15

Thanks a lot for the response Andrej!

One of these texts was known for me. But all of them are VERY basic. No one explains how to do a query and manage results :(
The only one that manage querys is the source code placed at contrib/tablefunc in the Postgres distribution. But is VERY hard to follow for me :( I would like to find an intermediate step.
Thanks again

Emiliano

2008/4/23 Andrej Ricnik-Bay <andrej.groups@gmail.com>:

On 24/04/2008, Emiliano Moscato <moski666@gmail.com> wrote:
> I have to do some stuff writing stored procedures for Postgres in C. I saw
> the oficial documentation but it was hard for me to find out how to do a
> simple function, let's call it "query()" , that receives a string and uses
> this string to do a query and return the results. Has anyone some examples?

You don't mentioned where else you looked - did you come across these?

http://linuxgazette.net/139/peterson.html

http://www.faqs.org/docs/ppbook/x15284.htm

http://www.observercentral.net/~selkovjr/postgres/tutorial/html/exttut-getstart.html

> Thanks in advance...
> Regards,
>
> Emiliano

Cheers,
Andrej

--
Please don't top post, and don't use HTML e-Mail :} Make your quotes concise.

http://www.american.edu/econ/notes/htmlmail.htm

--
mOsKi
"No hay nada que uno haga mal , lo que hay es poco vino." Autor Anonimo

Re: Stored procedures in C

From

"Martin Gainty"

Date:

24 April 2008, 13:30:54

Good Morning Emiliano-

since postgres is written in 'C'
and Most of us on this list have programmed in C ..although my experience was 'used in last millenia'

if we reference contrib/query/tsearch2/query.c

when you see statements such as

PG_FUNCTION_INFO_V1(tsquery_in);

you are calling a header file at \include\server\fmgr\fmgr.h which is macro-subbing at

specifically:

#define PG_FUNCTION_INFO_V1(funcname) \
extern Pg_finfo_record * CppConcat(pg_finfo_,funcname) (void); \
Pg_finfo_record * \
CppConcat(pg_finfo_,funcname) (void) \
{ \
static Pg_finfo_record my_finfo = { 1 }; \
return &my_finfo; \
} \
extern int no_such_variable

In any case please feel free to ping us for any questions you might have
It sounds like you have quite a challenge ahead of you

Buena Suerte
Martin-

----- Original Message -----
From: Emiliano Moscato
To: Andrej Ricnik-Bay
Cc: pgsql-general@postgresql.org
Sent: Thursday, April 24, 2008 8:44 AM
Subject: Re: [GENERAL] Stored procedures in C

Thanks a lot for the response Andrej!

One of these texts was known for me. But all of them are VERY basic. No one explains how to do a query and manage results :(
The only one that manage querys is the source code placed at contrib/tablefunc in the Postgres distribution. But is VERY hard to follow for me :( I would like to find an intermediate step.
Thanks again

Emiliano

2008/4/23 Andrej Ricnik-Bay <andrej.groups@gmail.com>:
On 24/04/2008, Emiliano Moscato <moski666@gmail.com> wrote:
> I have to do some stuff writing stored procedures for Postgres in C. I saw
> the oficial documentation but it was hard for me to find out how to do a
> simple function, let's call it "query()" , that receives a string and uses
> this string to do a query and return the results. Has anyone some examples?

You don't mentioned where else you looked - did you come across these?

http://linuxgazette.net/139/peterson.html

http://www.faqs.org/docs/ppbook/x15284.htm

http://www.observercentral.net/~selkovjr/postgres/tutorial/html/exttut-getstart.html

> Thanks in advance...
> Regards,
>
> Emiliano

Cheers,
Andrej

--
Please don't top post, and don't use HTML e-Mail :} Make your quotes concise.

http://www.american.edu/econ/notes/htmlmail.htm

--
mOsKi
"No hay nada que uno haga mal , lo que hay es poco vino." Autor Anonimo

Re: Stored procedures in C

From

"Merlin Moncure"

Date:

24 April 2008, 14:49:15

On Wed, Apr 23, 2008 at 6:38 PM, Martin Gainty <mgainty@hotmail.com> wrote:
>
>
> Emiliano and Mike
>
> The real challenge is trying to determine what a datatype is in cobol..for
> that matter what is stack variable or heap in Cobol?
> In the end you're better off <re>writing this mess (preferably in Java)..
> unless of course you need the billable hours for
> the first rewrite to C
> then later rewrite to Java

(have no idea how this relates to the OP's original question).  If you
are trying to port a cobol app to postgres, your best bet is to go
through the client interface, libpq.  If you had to do it on the
server side, I would stick to cobol environments that are C ABI
compatible.  Writing general purpose data procedures in C is just not
a very good idea most of the time...it's difficult and dangerous...C
SPI has great uses, it's just not for everything.

I personally think cobol is better suited for data processing type
problems than java.  Mapping cobol data types to SQL is not terribly
difficult.  cobol is notoriously difficult to port to another
langauges...probably cheaper to connect it to the database via ISAM
wrapper if the app is over a certain size.  Many modern cobol
environments support external data sources through various
techniques...extfh for example.  AcuCobol (crypticly) allows linking a
ISAM emulation layer directly to the cobol runtime, one approach I've
used in the past.

merlin

Re: Stored procedures in C

From

"Emiliano Moscato"

Date:

24 April 2008, 16:56:34

Guys, it has nothing to do with my question :D
I don't know why Martin answer to me anything about Cobol.
I just was looking how to query database in a stored procedure in C. Some people pointed me to SPI documentation (at chapter 41 of oficial Postgres documentation) that is being useful.
Thanks for worry about it :)

Emiliano

2008/4/24 Merlin Moncure <mmoncure@gmail.com>:

On Wed, Apr 23, 2008 at 6:38 PM, Martin Gainty <mgainty@hotmail.com> wrote:
>
>
> Emiliano and Mike
>
> The real challenge is trying to determine what a datatype is in cobol..for
> that matter what is stack variable or heap in Cobol?
> In the end you're better off <re>writing this mess (preferably in Java)..
> unless of course you need the billable hours for
> the first rewrite to C
> then later rewrite to Java

(have no idea how this relates to the OP's original question). If you
are trying to port a cobol app to postgres, your best bet is to go
through the client interface, libpq. If you had to do it on the
server side, I would stick to cobol environments that are C ABI
compatible. Writing general purpose data procedures in C is just not
a very good idea most of the time...it's difficult and dangerous...C
SPI has great uses, it's just not for everything.

I personally think cobol is better suited for data processing type
problems than java. Mapping cobol data types to SQL is not terribly
difficult. cobol is notoriously difficult to port to another
langauges...probably cheaper to connect it to the database via ISAM
wrapper if the app is over a certain size. Many modern cobol
environments support external data sources through various
techniques...extfh for example. AcuCobol (crypticly) allows linking a
ISAM emulation layer directly to the cobol runtime, one approach I've
used in the past.

merlin

--
mOsKi
"No hay nada que uno haga mal , lo que hay es poco vino." Autor Anonimo