Thread: Add value in an Array

Add value in an Array

From
"Jerome Chochon"
Date:
Hi all.
 
I am a french student working on a PostgreSQL database.
So i want to create a view which use a fonction (write in PLPGSQL) .
This function will take a colum from a table (the column is integer[] based) and add a value in the array.
So i dont find an operator to make this command.
 
Someone can help me ?
 
Best regards.
 
Jérôme Chochon
 

Compiling a user C function in 7.2.1

From
John Gunther
Date:
I'm writing additional C functions to extend PostgreSQL 7.2.1 on Red Hat
Linux 7.3, a process whose details are apparently not well documented.

I've got my first function written, using the functions in the source file
numeric.c as a model. I tried compiling it using the advice offered by Bruce
Momjian in his book (highly recommended), PostgreSQL: Introduction and
Concepts:


 The next step is to compile the C file into an object file that contains
CPU instructions. As part of this step, you must create a special
object file that can be dynamically linked  into
the POSTGRESQL server. Many operating systems require special
flags to create an object file that can be dynamically linked. The best way
to find the required flags is to go to pgsql/src/test/regress and
type make clean and then make regress.so. <a
 name="tex2html247"
 href="http://www.ca.postgresql.org/docs/aw_pgsql_book/footnode.html#foot21643">24.1
This command will display the compile commands used to generate the dynamically
linkable object file regress.so. The -I compile
flags allow searching for include files. Other flags are used for generating
dynamic object files; use them to compile your C code into a dynamically
linkable object file. You may need to consult your operating system documentation
for assistance in locating the proper flags.

This is where I'm stumped. make clean yields the error message "GNUmakefile:16:
../../../src/Makefile.global: No such file or directory". I then tried
renaming the file Makefile.global.in to Makefile.global
and running make clean again. This got a little farther but resulted
in the message "../../../src/Makefile.global:269: ../../../src/Makefile.port:
No such file or directory". I can't find a Makefile.port file
anywhere. Any idea what I'm doing wrong?

Thanks in advance for your guidance.

Re: Compiling a user C function in 7.2.1

From
Tom Lane
Date:
John Gunther <inbox@bucksvsbytes.com> writes:
> Any idea what I'm doing wrong?<br>

(a) you didn't run configure, which creates the files you are missing.

(b) you posted HTML mail to the list.  Please don't do that.

            regards, tom lane

Re: Compiling a user C function in 7.2.1

From
John Gunther
Date:
Tom Lane wrote:

>(a) you didn't run configure, which creates the files you are missing.
>
>
Thanks, Tom. Your reply caused me to learn a lot about building
PostgreSQL from source.

Now I'm much further along: I have created a dynamic library
(bvbpglib.so) containing one PostgreSQL C function (bvbpgsortword). This
function calls a "regular" (non-PostgreSQL) C function (bvbmakesortstring).

My current problem is that my psql statement:
CREATE FUNCTION bvbpgsortword(TEXT) RETURNS TEXT AS
'/usr/include/bvbpglib' LANGUAGE C WITH (ISSTRICT);
fails with "undefined symbol: bvbmakesortstring"

This is probably the result of some fundamental deficit in my
understanding of C writing/compiling/linking, but what puzzles me is
that bvbmakesortstring is called successfully from another C program I
wrote. Only in psql does it come back undefined.

Thanks.

John Gunther

For anyone is interested in enlightening me, relevant source follows:

SOURCE FOR bvbpglib.c (used to create bvbpglib.so, contains PostgreSQL
function bvbpgsortword):
#include "postgres.h"
#include "fmgr.h"
#include <string.h>
#include "bvblib.h"
Datum bvbpgsortword(PG_FUNCTION_ARGS);
PG_FUNCTION_INFO_V1(bvbpgsortword);
Datum bvbpgsortword(PG_FUNCTION_ARGS){
//declarations
    char  *bvbmakesortstring(const char *input, const int bitmodes,
const unsigned short int minlength, const char *keeplist, const char
*outdelim, const char *stoplist);
    text *instr = PG_GETARG_TEXT_P(0);
    text *outstr;
    unsigned int length;
    char *target;
//code
    target=VARDATA(outstr);
    target = bvbmakesortstring(VARDATA(instr),1,0,"","}","");
    length=strlen(target-1);
    target[length]=0;
    VARATT_SIZEP(outstr) = length + VARHDRSZ;
    PG_RETURN_TEXT_P(outstr);
}

SOURCE FOR test.c (this successfully calls bvbmakesortstring):
#include <stdio.h>
#include "bvblib.h"
int /*function*/ main(){

printf("out=%s\n",bvbmakesortstring("test-mctöst'tëst",0,0,",","=","A"));
    return 0;
}

PARTIAL SOURCE FOR bvblib.c (this library contains bvbmakesortstring):
#include <string.h>
#include "bvblib.h"
char    bvbstr[65535];
char *bvbmakesortstring(
    const char *instr,
    const int bitmodes,
    const unsigned short int minlength,
    const char *keeplist,
    const char *outdelim,
    const char *stoplist
)
{<code>...
     return bvbstr;//return the sort-ready string
}

SOURCE FOR bvblib.h:
#ifndef BVBLIB_H     /* has this been included */
#define BVBLIB_H     /* if not say its been done once */
char bvbstr[65535];
char *bvbmakesortstring(
    const char                                *instr, //string to be
processed
    const int                                    bitmodes,
    const unsigned short int    minlength,    //words shorter than this
are ignored
    const char                                  *keeplist,    //chars to
be kept in output words
    const char                                  *outdelim,    //char
string to use as word delimiter
    const char                                  *stoplist
//space-separated words to be ignored
);
#endif                  /* end the #ifndef construct */



Re: Compiling a user C function in 7.2.1

From
John Gunther
Date:
P.S. I forgot to supply the commands I used to compile and link my
bvbpglib.so library:
gcc -O2 -march=i386 -mcpu=i686 -Wall -Wmissing-prototypes
-Wmissing-declarations -fpic
-I/usr/src/redhat/BUILD/postgresql-7.2.1/src/interfaces/libpq
-I/usr/src/redhat/BUILD/postgresql-7.2.1/src/include
-I/usr/kerberos/include  -c -o bvbpglib.o bvbpglib.c
gcc -shared -o /usr/include/bvbpglib.so bvbpglib.o

John G

John Gunther wrote:

> Tom Lane wrote:
>
>> (a) you didn't run configure, which creates the files you are missing.
>>
>>
> Thanks, Tom. Your reply caused me to learn a lot about building
> PostgreSQL from source.
>
> Now I'm much further along: I have created a dynamic library
> (bvbpglib.so) containing one PostgreSQL C function (bvbpgsortword).
> This function calls a "regular" (non-PostgreSQL) C function
> (bvbmakesortstring).
>
> My current problem is that my psql statement:
> CREATE FUNCTION bvbpgsortword(TEXT) RETURNS TEXT AS
> '/usr/include/bvbpglib' LANGUAGE C WITH (ISSTRICT);
> fails with "undefined symbol: bvbmakesortstring"
>
> This is probably the result of some fundamental deficit in my
> understanding of C writing/compiling/linking, but what puzzles me is
> that bvbmakesortstring is called successfully from another C program I
> wrote. Only in psql does it come back undefined.
>
> Thanks.
>
> John Gunther
>
> For anyone is interested in enlightening me, relevant source follows:
>
> SOURCE FOR bvbpglib.c (used to create bvbpglib.so, contains PostgreSQL
> function bvbpgsortword):
> #include "postgres.h"
> #include "fmgr.h"
> #include <string.h>
> #include "bvblib.h"
> Datum bvbpgsortword(PG_FUNCTION_ARGS);
> PG_FUNCTION_INFO_V1(bvbpgsortword);
> Datum bvbpgsortword(PG_FUNCTION_ARGS){
> //declarations
>    char  *bvbmakesortstring(const char *input, const int bitmodes,
> const unsigned short int minlength, const char *keeplist, const char
> *outdelim, const char *stoplist);
>    text *instr = PG_GETARG_TEXT_P(0);
>    text *outstr;
>    unsigned int length;
>    char *target;
> //code
>    target=VARDATA(outstr);
>    target = bvbmakesortstring(VARDATA(instr),1,0,"","}","");
>    length=strlen(target-1);
>    target[length]=0;
>    VARATT_SIZEP(outstr) = length + VARHDRSZ;
>    PG_RETURN_TEXT_P(outstr);
> }
>
> SOURCE FOR test.c (this successfully calls bvbmakesortstring):
> #include <stdio.h>
> #include "bvblib.h"
> int /*function*/ main(){
>
> printf("out=%s\n",bvbmakesortstring("test-mctöst'tëst",0,0,",","=","A"));
>    return 0;
> }
>
> PARTIAL SOURCE FOR bvblib.c (this library contains bvbmakesortstring):
> #include <string.h>
> #include "bvblib.h"
> char    bvbstr[65535];
> char *bvbmakesortstring(
>    const char *instr,
>    const int bitmodes,
>    const unsigned short int minlength,
>    const char *keeplist,
>    const char *outdelim,
>    const char *stoplist
> )
> {<code>...
>     return bvbstr;//return the sort-ready string
> }
>
> SOURCE FOR bvblib.h:
> #ifndef BVBLIB_H     /* has this been included */
> #define BVBLIB_H     /* if not say its been done once */
> char bvbstr[65535];
> char *bvbmakesortstring(
>    const char                                *instr, //string to be
> processed
>    const int                                    bitmodes,
>    const unsigned short int    minlength,    //words shorter than this
> are ignored
>    const char                                  *keeplist,    //chars
> to be kept in output words
>    const char                                  *outdelim,    //char
> string to use as word delimiter
>    const char                                  *stoplist
> //space-separated words to be ignored
> );
> #endif                  /* end the #ifndef construct */
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>



Re: Compiling a user C function in 7.2.1

From
Tom Lane
Date:
John Gunther <inbox@bucksvsbytes.com> writes:
> Now I'm much further along: I have created a dynamic library
> (bvbpglib.so) containing one PostgreSQL C function (bvbpgsortword). This
> function calls a "regular" (non-PostgreSQL) C function (bvbmakesortstring).

> My current problem is that my psql statement:
> CREATE FUNCTION bvbpgsortword(TEXT) RETURNS TEXT AS
> '/usr/include/bvbpglib' LANGUAGE C WITH (ISSTRICT);
> fails with "undefined symbol: bvbmakesortstring"

> This is probably the result of some fundamental deficit in my
> understanding of C writing/compiling/linking, but what puzzles me is
> that bvbmakesortstring is called successfully from another C program I
> wrote. Only in psql does it come back undefined.

You have to make sure that the dynamic linker knows where to find the
library that contains bvbmakesortstring --- when one .so file depends
on another, the first one needs to be explicitly marked that way.

I think it would work to mention the other .so file as a library in
the link command for bvbpglib.so.

Try "ldd" on bvbpglib.so to see which libraries it's marked as needing,
and whether the dynamic linker knows where to find them.

            regards, tom lane

Re: Compiling a user C function in 7.2.1

From
John Gunther
Date:
Tom Lane wrote:

>I think it would work to mention the other .so file as a library in
>the link command for bvbpglib.so.
>
>
Thanks again Tom. That did the trick for getting CREATE FUNCTION to
complete.

Sorry to be such a pain folks, but please be patient or just ignore me.
I now have what appears to be a C function extension to PostgreSQL, but
it's not returning any visible value, most likely because my C code
isn't doing what I want it to. Here's a trivial version of the function
-- all I want it to do is return "abcdef", regardless of the input received.

#include "postgres.h"
#include "fmgr.h"
#include <string.h>
Datum bvbpgsortword(PG_FUNCTION_ARGS);
PG_FUNCTION_INFO_V1(bvbpgsortword);
Datum bvbpgsortword(PG_FUNCTION_ARGS){
//declarations
    text                    *instr = PG_GETARG_TEXT_P(0);
    text                    *outstr;
//code
    memcpy(outstr,"abcdef",6);//needed to move data from char constant
to pg's text type?
    VARATT_SIZEP(outstr) = 6 + VARHDRSZ;//set length for text value?
    PG_RETURN_TEXT_P(outstr);//return text value, i.e. 'abcdef', to
PostgreSQL?
}

Here's my SQL statement:
SELECT proname, bvbpgsortword("proname") FROM pg_proc;

The query result has procedure names in column1 and nothing in column 2.

I'm doubtless making some fundamental mistake here.

John Gunther



Re: Compiling a user C function in 7.2.1

From
Tom Lane
Date:
John Gunther <inbox@bucksvsbytes.com> writes:
> Datum bvbpgsortword(PG_FUNCTION_ARGS){
> //declarations
>     text                    *instr = PG_GETARG_TEXT_P(0);
>     text                    *outstr;
> //code
>     memcpy(outstr,"abcdef",6);//needed to move data from char constant
> to pg's text type?
>     VARATT_SIZEP(outstr) = 6 + VARHDRSZ;//set length for text value?
>     PG_RETURN_TEXT_P(outstr);//return text value, i.e. 'abcdef', to
> PostgreSQL?
> }

You're missing a palloc() (didn't your compiler warn about use of an
uninitialized variable here?).  And the memcpy needs to point at
VARDATA(outstr) not just outstr.

            regards, tom lane