Subject: Allowed to return possibly TOASTed datums?

From: Jan Behrens <[email-masked]>
Date: Sat, 14 Dec 2024 14:28:27 +0100

Hi,

I would like to implement a function that simply returns its argument
(for the unary plus operator of a custum data type).

Is it allowed to use the following function, assuming the data type is
TOASTable?

PG_FUNCTION_INFO_V1(mytype_pos);
Datum mytype_pos(PG_FUNCTION_ARGS) {                             
  PG_RETURN_DATUM(GETARG_DATUM(0));
}

CREATE FUNCTION "mytype_pos"("mytype") RETURNS "mytype"
  STRICT IMMUTABLE LANGUAGE c AS 'mylib.so', 'mytype_pos';

CREATE OPERATOR + (
  rightarg = "mytype",
  function = "mytype_pos"
);

Or do I need to always return a DETOASTed value? In addition to the
answer, I would like to know where I can find such information. It
seems to be difficult (for me) to find it in the source. Is there any
other documentation I can use, or any hints on WHERE in the source I
find more information about when to DETOAST and when not?

Also: Is my approach an idiomatic way to implement unary plus? Are there
pre-existing functions for that?

Many thanks in advance for your help and kind regards

Jan Behrens

---

From: Tom Lane <[email-masked]>
Date: Sat, 14 Dec 2024 12:01:49 -0500

>   PG_RETURN_DATUM(GETARG_DATUM(0));

Absolutely.  You can find things equivalent to that in the
core code, I think.

			regards, tom lane

---

From: Ron Johnson <[email-masked]>
Date: Sat, 14 Dec 2024 13:13:55 -0500

> Absolutely.  You can find things equivalent to that in the

(I'm not a developer, so this is a curiosity question, not a challenge.)

Since the purpose of TOAST is just (I think) to store "large field values"
on disk by dividing into multiple chunks, what would be the purpose of
returning the TOASTed value to Jan's function?

--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

---

From: Tom Lane <[email-masked]>
Date: Sat, 14 Dec 2024 16:37:30 -0500

> returning the TOASTed value to Jan's function?

Probably not much.  There are scenarios where not detoasting
saves work overall, because the object never has to get detoasted;
but I think that's rare.  It'd probably look like

	UPDATE t SET c = no_op_function(c) WHERE ...

which is something few people would write.

			regards, tom lane