It's even sillier than that:
test=# SELECT substring ('1234567890' FOR 4::bigint);substring
-----------
(1 row)
test=# SELECT substring ('1234567890' FOR 4::int);substring
-----------1234
(1 row)
Looking at the explain verbose make it look like it's using the wrong
version of substring. It's using the oid 2074 one:
test=# select oid, oid::regprocedure from pg_proc where proname =
'substring'; oid | oid
-------+------------------------------------- 936 | "substring"(text,integer,integer) 937 | "substring"(text,integer)
1680| "substring"(bit,integer,integer) 1699 | "substring"(bit,integer) 2012 | "substring"(bytea,integer,integer) 2013 |
"substring"(bytea,integer)2073 | "substring"(text,text) 2074 | "substring"(text,text,text) <----16579 |
"substring"(citext,integer,integer)16580| "substring"(citext,integer)
(10 rows)
That substring is for regular expressions. Nasty, not sure how to deal
with that one...
Have a nice day,
On Fri, Nov 11, 2005 at 02:43:23PM +0100, Harald Fuchs wrote:
> Consider the following:
>
> CREATE TEMP TABLE tbl (
> id SERIAL NOT NULL,
> PRIMARY KEY (id)
> );
>
> COPY tbl (id) FROM stdin;
> 1
> 2
> 3
> 4
> \.
>
> SELECT substring ('1234567890' FOR (SELECT count (*) FROM tbl)::int);
>
> This returns '1234', as expected. But
>
> SELECT substring ('1234567890' FOR (SELECT count (*) FROM tbl));
>
> returns NULL. I think the problem is that "SELECT count(*)" returns a
> BIGINT whereas "substring" expects an INT. Shouldn't there be a warning?
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.