Search for ternary-like operation in plpgsql that accepts a mix of types

[a***@gmail.com]

Hello list!

I'm playing around with writing some functions using plpgsql and
have a need to write a lot of dynamic queries using EXECUTE. When
building complicated strings to pass to EXECUTE, I sometimes need to
make decisions based on input. For example, let's say I have an input
argument of type INTEGER that I would like to use in a dynamic query,
but if the argument is NULL, I would like to handle that case, also.
This is what I originally tried doing, but failed:

CREATE OR REPLACE FUNCTION insert_test(INTEGER) AS $$
BEGIN
EXECUTE 'INSERT INTO test_' || uid || ' (number) VALUES (' ||
COALESCE(number, 'DEFAULT'::text) || ');';
END;
$$ LANGUAGE plpgsql;

Unfortunately, COALESCE does not allow mixing input types. I.e., I
cannot place a text value and an integer value together in a call to
COALESCE. I figured that maybe I could just write my own ternary type
function like so:

CREATE OR REPLACE FUNCTION ternary(BOOLEAN, ANYELEMENT, ANYELEMENT)
RETURNS anyelement AS $$
BEGIN
IF $1 THEN
RETURN $2;
ELSE
RETURN $3;
END IF;
END;
$$ LANGUAGE plpgsql;

Which works for same type arguments:

afazio=# select ternary(FALSE, 100, 123);
ternary
---------
123
(1 row)


But again, PG complains when I try to mix types:

SELECT ternary(TRUE, 'hello'::text, 123);
ERROR: function ternary(boolean, text, integer) does not exist
HINT: No function matches the given name and argument types. You may
need to add explicit type casts.

At the moment, the only way I know of to accomplish what I want is to
explicitly code the logic with IF ELSE:

CREATE OR REPLACE FUNCTION insert_test(INTEGER) AS $$
DECLARE
int_representation TEXT;
BEGIN
IF $1 IS NULL THEN
int_representation := 'DEFAULT';
ELSE
int_representation := $1;
END IF;

EXECUTE 'INSERT INTO test_' || uid || ' (number) VALUES (' ||
int_representation || ');';
END;
$$ LANGUAGE plpgsql;

The more input variables I have, the worse this gets. Adding six
lines of code for every input argument is tedious and makes the code
hideous. I am obviously failing to understanding something about the
anyelement type. Any advice in this area is very welcome... such as
an RTFM with a pointer in the right direction.

Thanks :)

Alfred J. Fazio,
***@gmail.com

[a***@gmail.com]

Post by a***@gmail.com
CREATE OR REPLACE FUNCTION insert_test(INTEGER) AS $$
DECLARE
int_representation TEXT;
BEGIN
IF $1 IS NULL THEN
int_representation := 'DEFAULT';
ELSE
int_representation := $1;
END IF;

Oops, made a typo. I meant to type the following on that second to
last line:

int_representation := $1::text;

Alfred J. Fazio,
***@gmail.com

[Lew]

Post by a***@gmail.com
Hello list!
I'm playing around with writing some functions using plpgsql and
have a need to write a lot of dynamic queries using EXECUTE. When
building complicated strings to pass to EXECUTE, I sometimes need to
make decisions based on input. For example, let's say I have an input
argument of type INTEGER that I would like to use in a dynamic query,
but if the argument is NULL, I would like to handle that case, also.
CREATE OR REPLACE FUNCTION insert_test(INTEGER) AS $$
BEGIN
EXECUTE 'INSERT INTO test_' || uid || ' (number) VALUES (' ||
COALESCE(number, 'DEFAULT'::text) || ');';
END;
$$ LANGUAGE plpgsql;
Unfortunately, COALESCE does not allow mixing input types. I.e., I
cannot place a text value and an integer value together in a call to
COALESCE.

So how about making the types the same?

As you did in your solution that worked - you cast

Post by a***@gmail.com
int_representation := $1::text;

but not in your CASE version. That's why one worked and the other didn't.

[a***@gmail.com]

Post by Lew
So how about making the types the same?
Lew

Wow... talk about right under my nose. Thanks for holding my hand on
this one Lew. :)

Alfred J. Fazio,
***@smoothstone.com