Extern C-Function in SQL

Question

How can i split a string with a unknown delimiter?

I need to split up a string like

'3.584731 60.739211,3.590472 60.738030,3.592740: 60.736220', * *)

Between the * * i want to input a delimiter, who would split up this string by the delimiter.

For Example

 If *:*
 3.584731 60.739211,3.590472 60.738030,3.592740
 60.736220

I know that strtok() split up string. But how i have to use it that the code dont know the delimiter and the user have to insert it. This is my Code.

PG_FUNCTION_INFO_V1(benchmark);
Datum
benchmark(PG_FUNCTION_ARGS)
{

FuncCallContext     *funcctx;
int                  call_cntr;
int                  max_calls;
TupleDesc            tupdesc;
AttInMetadata       *attinmeta;
//As Counters
int i = 0, j = 0;
//To get the every SingleString from the Table
char *k = text_to_cstring(PG_GETARG_TEXT_P(0));
//The Delimiter
char *c (0);
//the temporary "memory"
char * temp = NULL;

//The final result
//Need as an Array
//From ["x1 y1,x2 y2,...xn yn"] Split on the ',' 

//to   ["x1 y1","x2 y2",..."xn yn"] Split on the ' ' 

//to the final ["x1","y1","x2","y2"..."xn","yn"]
char**result[a] = {0};

if (SRF_IS_FIRSTCALL())
    {
        {
        MemoryContext   oldcontext;

        //create a function context for cross-call persistence 
        funcctx = SRF_FIRSTCALL_INIT();

        //reqdim = (PG_NARGS()  MAXDIM)
   //         SRF_RETURN_DONE(funcctx);

        // switch to memory context appropriate for multiple function calls 
        oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);

        /* total number of tuples to be returned */
        funcctx->max_calls = PG_GETARG_UINT32(0);

        /* Build a tuple descriptor for our result type */
        if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
            ereport(ERROR,
                (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
                 errmsg("function returning record called in context "
                        "that cannot accept type record")));

        //Still dont know what attinmeta is
        attinmeta = TupleDescGetAttInMetadata(tupdesc);
        funcctx->attinmeta = attinmeta;
        MemoryContextSwitchTo(oldcontext);

    }

            call_cntr = funcctx->call_cntr;
            max_calls = funcctx->max_calls;
            attinmeta = funcctx->attinmeta;
            result[a] = funcctx->result[a];
            temp      = funcctx->temp;
            *k        = funcctx->*k;
            delim     = funcctx->delim;
            c         = funcctx->c;


            //here is the difficult part

            if(*k)
            {
                temp = strtok(k,delim) //the delimiter
                while (temp)
                {
                //palloc or malloc whats the difference ?
                result[a] = palloc...
                strcpy(result[a],temp)
                temp = strtok (NULL,delim)
                i++;
                }
                    for (j = 0; j < i; j++)
                    printf("[%d] sub-string is %s\n", (j+1), result[j]);

                    for (j = 0; j < i; j++)
                    SRF_RETURN_NEXT(funcctx,result[a]);


            }
            else
            {
                SRF_RETURN_DONE(funcctx);
            }
}

Would this be right? palloc or malloc ? Whats the difference and how to use it in this case? I try to make much notes as i can hope you understand this :) And dont to be to hard i just started with programs :)

Answer 1

PostgreSQL source code is good start for taking some ideas and patterns. When you like to write SETOF function, then you should to start with some SETOF function. Google keyword is "SRF_RETURN_NEXT". There are some examples in PostgreSQL doc http://www.postgresql.org/docs/9.3/static/xfunc-c.html - and you can find some other examples.

Some years ago I wrote array iterator:

#include "funcapi.h"

typedef struct generate_iterator_fctx
{
        int4    lower;
        int4    upper;
        bool    reverse;
} generate_iterator_fctx;

Datum array_subscripts(PG_FUNCTION_ARGS);
PG_FUNCTION_INFO_V1(array_subscripts);

/* 
 * array_subscripts(array anyarray, dim int, reverse bool)
 */
Datum
array_subscripts(PG_FUNCTION_ARGS)
{
        FuncCallContext *funcctx;
        MemoryContext oldcontext;
        generate_iterator_fctx *fctx;

        /* stuff done only on the first call of the function */
        if (SRF_IS_FIRSTCALL())
        {
                ArrayType *v = PG_GETARG_ARRAYTYPE_P(0);
                int reqdim;
                int     *lb, *dimv;

                /* create a function context for cross-call persistence */
                funcctx = SRF_FIRSTCALL_INIT();

                reqdim = (PG_NARGS()  MAXDIM)
                        SRF_RETURN_DONE(funcctx);

                /* Sanity check: was the requested dim valid */
                if (reqdim  ARR_NDIM(v))
                        SRF_RETURN_DONE(funcctx);

                /*
                 * switch to memory context appropriate for multiple function calls
                 */
                oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
                fctx = (generate_iterator_fctx *) palloc(sizeof(generate_iterator_fctx));

                lb = ARR_LBOUND(v);
                dimv = ARR_DIMS(v);

                fctx->lower = lb[reqdim - 1];
                fctx->upper = dimv[reqdim - 1] + lb[reqdim - 1] - 1;
                fctx->reverse = (PG_NARGS() user_fctx = fctx;

                MemoryContextSwitchTo(oldcontext);
        }

        funcctx = SRF_PERCALL_SETUP();

        fctx = funcctx->user_fctx;

        if (fctx->lower upper)
        {
                if (!fctx->reverse)
                        SRF_RETURN_NEXT(funcctx, Int32GetDatum(fctx->lower++));
                else
                        SRF_RETURN_NEXT(funcctx, Int32GetDatum(fctx->upper--));
        }
        else
                /* do when there is no more left */
                SRF_RETURN_DONE(funcctx);
}

Registration:

CREATE FUNCTION array_subscripts(anyarray)
RETURNS SETOF int
AS 'MODULE_PATHNAME'
LANGUAGE C STRICT;

CREATE FUNCTION array_subscripts(anyarray, integer)
RETURNS SETOF int
AS 'MODULE_PATHNAME'
LANGUAGE C STRICT;

CREATE FUNCTION array_subscripts(anyarray, integer, bool)
RETURNS SETOF int
AS 'MODULE_PATHNAME'
LANGUAGE C STRICT;

Usage:

CREATE FUNCTION array_expand(anyarray)
RETURNS SETOF anyelements
AS
$$
  SELECT $1[i] 
     FROM array_subscripts($1) g(i);
$$ LANGUAGE SQL;

Answer 2

Please check the below code. Hope its self-explanatory.

Let us know if you have any difficulties in understanding.

#include <stdio.h>
#include <stdlib.h>
#include <string.h>

#define SIZ 128

int main()
{
    char *op[SIZ];              //array to hold the o/p
    int i = 0, j = 0;           //used as counters
    char input[] = "3.584731 60.739211, / 3.590472 60.738030,3.592740 / 60.736220"; //the input
    char *delim = "/";          //the delimiter
    char * temp = NULL;

    temp = strtok(input, delim);
    while (temp)
    {
        op[i] = malloc(SIZ);
        strcpy(op[i], temp);
        temp = strtok(NULL, delim);
        i++;
    }

    for (j = 0; j < i; j++)
    printf("[%d] sub-string is %s\n", (j+1), op[j]);

    for (j = 0; j < i; j++)
    free(op[j]);

    return 0;
}

---

EDIT

version 0.5. Hope this split_string() function will solve your problem. Now, work a bit on asking user inputs using fgets(). Read the man page here. It should not be too tough. Best of luck.

#include <stdio.h>
#include <stdlib.h>
#include <string.h>

#define SIZ 128

void split_string(char * ip, char * sep);

int main()
{
        char input[] = "3.584731 60.739211, / 3.590472 60.738030,3.592740 / 60.736220"; //the input
        char *delim = "/";          //the delimiter

        split_string(input, delim);

        return 0;
}

void split_string(char * ip, char * sep)
{
        char * op[SIZ] = {0};
        int i = 0, j = 0;           //used as counters
        char * temp = NULL;

        if (ip)
        {
                temp = strtok(ip, sep);
                while (temp)
                {
                        op[i] = malloc(SIZ);
                        strcpy(op[i], temp);
                        temp = strtok(NULL, sep);
                        i++;
                }

                for (j = 0; j < i; j++)
                        printf("[%d] sub-string is %s\n", (j+1), op[j]);

                for (j = 0; j < i; j++)
                        free(op[j]);
        }
}