So one of the elephants in the room in this (rather dead-end)
discussion is that one of the things "unknown" is good for is the fact
that most clients don't bind their parameter types to specific types.
Doing so is extremely cumbersome in just about every interface because
it forces you to think about SQL types and look up constants for every
parameter type. It's even worse if you have user-defined types on the
server and have to start figuring out how to look these up
dynamically.
We use unknown to normally dtrt when a client passes a text literal
representation without forcing them to tell us what type to interpret
it as. Most client interfaces can just leave every parameter set to
type unknown and let Postgres figure out what to do with everything.
However it occurs to me that that doesn't work very well for
substring(). If your client interface doesn't implicitly bind the
second argument to integer it'll be interpreted as text by default and
you get what is usually going to not be what you want;
postgres=# select substring('foobar456',4);
substring
-----------
bar456
(1 row)
postgres=# select substring('foobar456','4');
substring
-----------
4
(1 row)
This for example makes it awkward to use from Perl:
$ perl -e 'use Data::Dumper; use DBI; use DBD::Pg; $d =
DBI->connect("dbi:Pg:database=postgres;host=localhost;port=1234");
print Dumper $d->selectall_arrayref(q{select
substring('\''foobar'\'',?)},{},'4');'
$VAR1 = [
[
undef
]
];
$ perl -e 'use Data::Dumper; use DBI; use DBD::Pg; $d =
DBI->connect("dbi:Pg:database=postgres;host=localhost;port=1234");
print Dumper $d->selectall_arrayref(q{select
substring('\''foobar'\'',?)},{},4);'
$VAR1 = [
[
undef
]
];