Thread: 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