Обсуждение: STored Procedures

Hi there,

I have a problem with a stored procedure. I want to implement a
search and replace function to Postgre (because it has no support
for that ... only translate but this does not work on whole strings
but on single characters as far as I know).

So I set up a C-routine, which looks like this :

-x-x-
char *pgsql_strreplace(char *s, char *t, char *u)
{
   char *p, *r = 0;

   if (p = strstr(s, t)) {
     r = malloc(strlen(s) - strlen(t) + strlen(u) + 1);
     strcpy(r, s); *(r + (p - s)) = 0;
     strcat(r, u);
     strcat(r, p + strlen(t));
   }

   return r;
}
-x-x-

This code works standalone, but not when I set it up as a
stoerd procedure in Postgre.

So I did a :

cc -fpic -c nidagfuncs.c�
cc -shared -o nidagfuncs.so nidagfuncs.o

And copied the .so file to lib-dir in PostgreSql.

Then I did a :

-x-x-
CREATE FUNCTION pgsql_strreplace(varchar, varchar, varchar) RETURNS varchar
AS '/usr/local/pgsql/current/lib/nidagfuncs.so' LANGUAGE 'C'
WITH (isStrict);
-x-x-

Query executed OK!

Now I tried to use this function, but then it crashed :

-x-x-
Query : SELECT pgsql_strreplace(email,"web","yahoo") from users where
id=1234;

Result: pqReadData() -- backend closed the channel unexpectedly.
        This probably means the backend terminated abnormally
        before or while processing the request.
    connection to server was lost
-x-x-

Any ideas why this code does not work ... i'm stuck!

Thanks in advance...

Oliver Neumann

Oliver Neumann wrote:
> Hi there,
>
> I have a problem with a stored procedure. I want to implement a
> search and replace function to Postgre (because it has no support
> for that ... only translate but this does not work on whole strings
> but on single characters as far as I know).
>
> So I set up a C-routine, which looks like this :
>
> -x-x-
> char *pgsql_strreplace(char *s, char *t, char *u)
> {
>    char *p, *r = 0;
>
>    if (p = strstr(s, t)) {
>      r = malloc(strlen(s) - strlen(t) + strlen(u) + 1);
>      strcpy(r, s); *(r + (p - s)) = 0;
>      strcat(r, u);
>      strcat(r, p + strlen(t));
>    }
>
>    return r;
> }
> -x-x-
>
> This code works standalone, but not when I set it up as a
> stoerd procedure in Postgre.

You need to write this in an fmgr compatable way. See the docs:
   http://www.postgresql.org/idocs/index.php?xfunc-c.html
You want to use the "Version-1" calling conventions. You might also want (or
need) to consider what happens if you're using this function in a multibyte
database.

Note that 7.3 (which is currently in beta) has a replace function:
   replace(string text, from text, to text)
   Replace all occurrences in 'string' of substring 'from' with substring 'to'

regression=# select replace('abcdefabcdef', 'cd', 'XX');
    replace
--------------
  abXXefabXXef
(1 row)

HTH,

Joe

"Joe Conway" <mail@joeconway.com> schrieb im Newsbeitrag
news:3D86249B.4070801@joeconway.com...
> > This code works standalone, but not when I set it up as a
> > stoerd procedure in Postgre.
>
> You need to write this in an fmgr compatable way. See the docs:
>    http://www.postgresql.org/idocs/index.php?xfunc-c.html
> You want to use the "Version-1" calling conventions. You might also want
(or
> need) to consider what happens if you're using this function in a
multibyte
> database.

Thanks for the tipp ... so I rewrote this function as V1 .. now it looks
like this, but
the error stays the same. Can you help me?

-x-x-
#include "postgres.h"
#include "fmgr.h"

PG_FUNCTION_INFO_V1(pgsql_strreplace);

Datum
pgsql_strreplace(PG_FUNCTION_ARGS)
{
    char *s = (char *) PG_GETARG_VARCHAR_P(0);
    char *t = (char *) PG_GETARG_VARCHAR_P(1);
    char *u = (char *) PG_GETARG_VARCHAR_P(2);

    char *p, *r = 0;

    if (p = strstr(s, t)) {
      r = malloc(strlen(s) - strlen(t) + strlen(u) + 1);
      strcpy(r, s); *(r + (p - s)) = 0;
      strcat(r, u);
      strcat(r, p + strlen(t));
    }

    PG_RETURN_VARCHAR_P(r);
}
-x-x-

Error-Message :

-x-x-
pqReadData() -- backend closed the channel unexpectedly.
        This probably means the backend terminated abnormally
        before or while processing the request.
connection to server was lost
-x-x-

> Note that 7.3 (which is currently in beta) has a replace function:

That's impossible ... we have to use PostgreSQL 7.2.1 ... unfortunately ...

Thanks
Oliver