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.
pgpfSFcZcyoMQ.pgp
Description: PGP signature
