Cast value to type (TEXT) using string representation of type ("TEXT")

Question

I would like to create a function that attempts to cast a group of values into a user-specified type (with a default of text). A very simple function would look like so:

CREATE OR REPLACE FUNCTION cast_to(variable jsonb, key text, target_type anyelement DEFAULT 'TEXT'::regtype) RETURNS anyelement as $$
begin
    RETURN CAST(variable->>key AS target_type);
end
$$
language plpgsql;

I have tried the following:

1. SELECT CAST('foo' AS 'text');: Gives syntax error
2. SELECT CAST('foo' AS 'text'::regtype);: Same error as 1
3. SELECT CAST('foo' AS pg_typeof(null::text)); says that type pg_typeof does not exist

The last attempt was a thought that I could pass in a variable with the target type instead of a text representation. Using the function would then look like SELECT cast_to('text', NULL::text);.

How to accomplish this or similar functionality?

Edit: As suggested in the comments, I tried to use dynamic SQL. I am not having much luck. I created a very basic case that doesn't use any variables:

CREATE OR REPLACE FUNCTION audit.cast_to() RETURNS text as $$
DECLARE 
_sql TEXT := 'SELECT CAST($1 AS $2)';
out TEXT;
begin
    EXECUTE _sql
    USING 'foo', 'TEXT'
    INTO out;
    return out;
end
$$
language plpgsql;

That however throws the error:

syntax error at or near "$2"

Answer 1

Actually, it can be done. Even without dynamic SQL. Pretty simple at the surface, too.

CREATE FUNCTION cast_to(_js jsonb, INOUT _target_type anyelement = NULL::text) AS
$func$
BEGIN
   SELECT _js ->> pg_typeof(_target_type)::text
   INTO   _target_type;
END
$func$  LANGUAGE plpgsql;

db<>fiddle here

But this minimalistic function packs a couple of advanced / tricky details: See the last chapter here for basics:

• Refactor a PL/pgSQL function to return the output of various SELECT queries

The final piece of the puzzle is to cast the text value returned by the ->> operator to the return type. A simple SQL function is strict about that and does not accept a text for, say, an integer. (Nor for an integer defined by actual input to the polymorphic input parameter. The same goes for a PL/pgSQL function trying to simply RETURN. An explicit cast would be required.

CAST (expression AS type) is not a normal function. Nor is the short syntax expression::type. Those are constructs or syntax elements. You may have noticed that the type name is placed without single quotes, i.e.: as identifier. (Or you have missed that detail, which is the cause for the first 3 syntax errors you report.) And identifiers cannot be parameterized in SQL. That would require dynamic SQL.

However, we can assign the text result of the expression to a (necessarily typed) variable or parameter to effortlessly achieve the same. The INTO clause will achieve that. For convenience, I assign to the INOUT parameter _target_type directly. So _target_type serves several purposes:

1. Defines the polymorphic return type.

2. Defines the key name for use with the JSON ->> operator. That name is carried by the type of _target_type and I extract it with pg_typeof() - which actually returns regtype, so we need to cast that to text explicitly.
   Note that always results in the Postgres standard type name, e.g: 'integer', not 'int' nor 'int4'. If your key names differ from default Postgres type names, you'll have to pass an additional parameter like you had in your original design!

3. Serves as OUT parameter (so we do not need to DECLARE a variable) that can be assigned to.

4. Has a DEFAULT value attached to it: = NULL::text, so the second parameter can be omitted to just return text.
   You tried that in your original, but DEFAULT 'TEXT'::regtype was off target.

You may want to split some of these purposes to multiple parameters / variables.

So it can be done.
Question is: why do you want to do it? Often, there is a faster and less tricky solution around the corner - even if more verbose.