If you’re getting a PostgreSQL error that reads something like “function generate_subscripts(text[], integer, integer) does not exist“, it’s probably because your third argument is of the wrong type when using the generate_subscripts()
function.
The above error specifically implies that an integer was passed as the third argument, but it must be a boolean value.
The third argument of the generate_subscripts()
function is optional, but if passed, it must be a boolean expression.
To fix this error, either pass a boolean value as the third argument, or eliminate the third argument altogether.
Example of Error
Here’s an example of code that produces the error:
SELECT generate_subscripts(
ARRAY['Cat','Dog','Rabbit'],
1,
1
) AS "Reverse Order";
Result:
ERROR: function generate_subscripts(text[], integer, integer) does not exist
LINE 1: SELECT generate_subscripts(
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
I got the error because my third argument wasn’t a boolean expression. In this case it was an integer.
Solution 1
We can fix the problem by changing the third argument to a boolean value:
SELECT generate_subscripts(
ARRAY['Cat','Dog','Rabbit'],
1,
true
) AS "Reverse Order";
Result:
Reverse Order
---------------
3
2
1
This time the function worked without error. That’s because I passed a boolean value as the third argument.
The function also accepts boolean string representations. So, if we really wanted to keep the 1
instead of true
, we can surround it with quotes:
SELECT generate_subscripts(
ARRAY['Cat','Dog','Rabbit'],
1,
'1'
) AS "Reverse Order";
Result:
Reverse Order
---------------
3
2
1
Or we could cast the 1
to a boolean:
SELECT generate_subscripts(
ARRAY['Cat','Dog','Rabbit'],
1,
1::boolean
) AS "Reverse Order";
Result:
Reverse Order
---------------
3
2
1
And we could be even more explicit by doing both:
SELECT generate_subscripts(
ARRAY['Cat','Dog','Rabbit'],
1,
'1'::boolean
) AS "Reverse Order";
Result:
Reverse Order
---------------
3
2
1
Other boolean string representations include true
, yes
, on
, t
, and y
meaning we could replace '1'
with 'true'
, 'yes'
, 'on'
, 't'
, or 'y'
(i.e. enclosed in quotes).
Solution 2
The above solution assumes that we need to output the result in reverse order. If we don’t want to do that, then we can omit the third argument altogether:
SELECT generate_subscripts(
ARRAY['Cat','Dog','Rabbit'],
1
) AS "Default Order";
Result:
Default Order
---------------
1
2
3
Omitting the third argument results in generate_subscripts()
using the default value, which is false
(i.e. reverse ordering is not true).