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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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 be a 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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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 be a 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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general