Обсуждение: How to detoast a column of type BYTEAOID
I am attempting to create a new language implementation. The language is Andl (andl.org), so the handler is plandl. I am having a bit of trouble executing SPI queries from inside plandl. The particular problem arises after calling SPI_cursor_fetch() and then SPI_getbinval() on a column of type BYTEA (BYTEAOID 17). I was expecting to get back a Datum of type bytea, but the length is crazy. From the look of the data, I suspect that what I have is a compressed TOAST, and I don't know how to handle those. 1. Does SPI_getbinval() indeed return compressed TOAST values? 2. If so, what should I do (in general) to ensure the values I retrieve are de-toasted? Regards David M Bennett FACS Andl - A New Database Language - andl.org
david@andl.org wrote: > I am attempting to create a new language implementation. The language is > Andl (andl.org), so the handler is plandl. > I am having a bit of trouble executing SPI queries from inside plandl. > > The particular problem arises after calling SPI_cursor_fetch() and then > SPI_getbinval() on a column of type BYTEA (BYTEAOID 17). I was expecting to > get back a Datum of type bytea, but the length is crazy. From the look of > the data, I suspect that what I have is a compressed TOAST, and I don't know > how to handle those. > > 1. Does SPI_getbinval() indeed return compressed TOAST values? > 2. If so, what should I do (in general) to ensure the values I retrieve are > de-toasted? You should use the PG_DETOAST_DATUM* macros from fmgr.h; see the documentation there. Yours, Laurenz Albe
OK, got it. I really wasn't expecting to have to deal with TOASTs in what looks like a rather ordinary query -- perhaps there might bea note in the documentation? But thanks, that works just fine. Problem solved. Regards David M Bennett FACS Andl - A New Database Language - andl.org > -----Original Message----- > From: Albe Laurenz [mailto:laurenz.albe@wien.gv.at] > Sent: Monday, 18 April 2016 6:05 PM > To: 'david@andl.org *EXTERN*' <david@andl.org>; pgsql-general@postgresql.org > Subject: RE: [GENERAL] How to detoast a column of type BYTEAOID > > david@andl.org wrote: > > I am attempting to create a new language implementation. The language > > is Andl (andl.org), so the handler is plandl. > > I am having a bit of trouble executing SPI queries from inside plandl. > > > > The particular problem arises after calling SPI_cursor_fetch() and > > then > > SPI_getbinval() on a column of type BYTEA (BYTEAOID 17). I was > > expecting to get back a Datum of type bytea, but the length is crazy. > > From the look of the data, I suspect that what I have is a compressed > > TOAST, and I don't know how to handle those. > > > > 1. Does SPI_getbinval() indeed return compressed TOAST values? > > 2. If so, what should I do (in general) to ensure the values I > > retrieve are de-toasted? > > You should use the PG_DETOAST_DATUM* macros from fmgr.h; see the > documentation there. > > Yours, > Laurenz Albe
OK, got it. I really wasn't expecting to have to deal with TOASTs in what looks like a rather ordinary query -- perhaps there might bea note in the documentation? But thanks, that works just fine. Problem solved. Regards David M Bennett FACS Andl - A New Database Language - andl.org > -----Original Message----- > From: Albe Laurenz [mailto:laurenz.albe@wien.gv.at] > Sent: Monday, 18 April 2016 6:05 PM > To: 'david@andl.org *EXTERN*' <david@andl.org>; pgsql-general@postgresql.org > Subject: RE: [GENERAL] How to detoast a column of type BYTEAOID > > david@andl.org wrote: > > I am attempting to create a new language implementation. The language > > is Andl (andl.org), so the handler is plandl. > > I am having a bit of trouble executing SPI queries from inside plandl. > > > > The particular problem arises after calling SPI_cursor_fetch() and > > then > > SPI_getbinval() on a column of type BYTEA (BYTEAOID 17). I was > > expecting to get back a Datum of type bytea, but the length is crazy. > > From the look of the data, I suspect that what I have is a compressed > > TOAST, and I don't know how to handle those. > > > > 1. Does SPI_getbinval() indeed return compressed TOAST values? > > 2. If so, what should I do (in general) to ensure the values I > > retrieve are de-toasted? > > You should use the PG_DETOAST_DATUM* macros from fmgr.h; see the > documentation there. > > Yours, > Laurenz Albe