Re: STored Procedures

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