Jsonb as input parameter

[GrandFelix]

Hi,

in my function I need to get as input parameter jsonb type. Is this supported ? I have tried with interface{} and it is not ok. Do you have any suggestion how I should resolve this?

[microo8]

that's a tough question, I'll check how it is done in pq. meanwhile, can you test if you can Marshall the json and input it as a string? And then convert it to jsonb in the query?

insert into table (json_col) values ($1::text::jsonb)

[GrandFelix]

Hmmm @microo8 maybe this can help you https://github.com/plv8/plv8/blob/95ac8f77a8c13c0119eefc4b4d6f3b11d23acf25/plv8_type.cc#L303 Plv8 also have jsonb supported. I would love to help you out with this but I have just started with go and I think I cant cope with it yet :/

Ok, I will try that with text and jsonb conversion.

[GrandFelix]

I think this with converting to text and vice versa should go but we don't want to converting it for input and output so we are looking for better option.

[microo8]

I'll check what I can do with looking on OID of the type. But only when I will have time.

[GrandFelix]

thank you @microo8. Meantime I will also try to implement it on my own. If I can solve it I will make pr.

[remotejob]

I am as well need get 'Jsonb' type in trigger. As I understand interface{} don't work? :)

[GrandFelix]

@remotejob yes, if you use interface{} as input type you get error that it is not supported type. Because of that we must add support for jsonb.

[microo8]

You can now use json and jsonb in query results

type ExampleJson struct {
     Var1 string `json:"var1"` 
     Var2 int       `json:"var2"` 
} 

stmt, _ := db.Prepare("select jsonbcol from table", nil)
row, _ := stmt.QueryRow()
var ej ExampleJson
row.Scan(&ej)

and also in queries

stmt, _ := db.Prepare("insert into table (jsonbcol) values ($1)", []string{"jsonb"})
stmt.Exec(ej)

try it out and when it works you can close the issue

[remotejob]

In my case (trigger) variant:

func CrimeTrg(td plgo.TriggerData) plgo.TriggerRow {

var id string
var source map[string]interface{}

td.NewRow.Scan(&id,&source)

log.Println("id2",id,"source",source)
return td.NewRow

}

work for me, probably it was necessary test it before instead of try put jsonb into interface{} ?! :) Greate!!!!

[GrandFelix]

Ok, thats funny. Now Im getting error when building it :/ I think that something updated on my vagrant machine :/ Im getting:

# command-line-arguments /usr/bin/ld: -r and -shared may not be used together collect2: error: ld returned 1 exit status Cannot build package: exit status 2

Any tip?

[microo8]

It's similar to the #1 issue. Try to update your plgo binary

$ cd $GOPATH/src/github.com/microo8/plgo/plgo 
$ git pull
$ go install

When you run plgo it creates the package source in /tmp/plgo<somenumber> folder.

You can go there and compile it CGO_LDFLAGS_ALLOW='.*' go build -v -x -buildmode=c-shared. Can you then post the full output?

Meantime I try to find why is cgo adding -r to ld command

[microo8]

Also in the /tmp/plgo<somenumber> try to edit the CFLAGS and add to it -fPIC. Something like:

#cgo CFLAGS: -I/usr/include/postgresql/server -fPIC

[GrandFelix]

Sorry for delay. Here is the output of the command that you have said to run https://pastebin.com/paw32kjR

[remotejob]

How change cgo CFLAGS: without install other Postgresql version?

I tryed

export CGO_CFLAGS=-I../postgresql-9.6.2/src/include

Don't help in

/tmp/plgo<somenumber>

pl.go:#cgo CFLAGS: -I/usr/include/postgresql/9.5/server

As I understand necessary change

pg_config

[microo8]

Try editing the pl.go file in /tmp/plgo<somenumber> to look like:

#cgo CFLAGS: -I/usr/include/postgresql/9.5/server -fPIC

the -I path must be the output of pg_config --includedir-server

I cannot help you with this. Maybe if I try to make a docker container with your version of postgres, go and gcc?

[remotejob]

Now No package main in . Usage: plgo [path/to/package]

maybe add other optional parameter for example --include

I mean will be something like:

plgo --include /usr/local/pgsql/include/server plmodule/ext

[GrandFelix]

Hi. I have tried now to pull everything again and set everything as you said but I get same error about using -r and -shared flags?

https://gist.github.com/GrandFelix/b3514feaa1e33856a519702b29582745

[GrandFelix]

Hi @remotejob Im still stuck here. Do you have any tip what Im doing it wrong or maybe where is the problem ?

[microo8]

What C compiler you have?

[GrandFelix]

Is this ok?

Using built-in specs.
COLLECT_GCC=gcc
COLLECT_LTO_WRAPPER=/usr/libexec/gcc/x86_64-redhat-linux/4.8.5/lto-wrapper
Target: x86_64-redhat-linux
Configured with: ../configure --prefix=/usr --mandir=/usr/share/man --infodir=/usr/share/info --with-bugurl=http://bugzilla.redhat.com/bugzilla --enable-bootstrap --enable-shared --enable-threads=posix --enable-checking=release --with-system-zlib --enable-__cxa_atexit --disable-libunwind-exceptions --enable-gnu-unique-object --enable-linker-build-id --with-linker-hash-style=gnu --enable-languages=c,c++,objc,obj-c++,java,fortran,ada,go,lto --enable-plugin --enable-initfini-array --disable-libgcj --with-isl=/builddir/build/BUILD/gcc-4.8.5-20150702/obj-x86_64-redhat-linux/isl-install --with-cloog=/builddir/build/BUILD/gcc-4.8.5-20150702/obj-x86_64-redhat-linux/cloog-install --enable-gnu-indirect-function --with-tune=generic --with-arch_32=x86-64 --build=x86_64-redhat-linux
Thread model: posix
gcc version 4.8.5 20150623 (Red Hat 4.8.5-16) (GCC) 

[microo8]

I don't know, was just thinking about the output from gcc --version Mine is: gcc (GCC) 8.1.0. Can you try it on a newer compiler?

[GrandFelix]

Hi @microo8 now I have finally got time and have installed everything :) I did have to build gcc from scratch to version 8.1.0 as you have suggested it. Now I have one more question. I want to return from my function result as SETOF ? Is this possible?

[microo8]

SETOF isn't yet supported. But I can look at it. Will close this issue, because its title is completely something else, what we are discussing.

[GrandFelix]

@microo8 thanks