The following bug has been logged online:

Bug reference:      3435
Logged by:          Carlos Quintero
Email address:      carlos(dot)quintero(dot)sspa(at)juntadeandalucia(dot)es
PostgreSQL version: 8.2.4
Operating system:   Linux RedHat Enterprise 3
Description:        problem with substring function
Details: 

Hi, 

I like to use a numerical expression as parameter for the substring
function, but it works always as i use regular expressions. Please, try
this:

select substring('1234' from to_number('3', '999999') for 3);

What returns a empty string:

 substring 
-----------
 
(1 fila)

But the right result must be this:

select substring('1234' from 3 for 3);                         

 substring 
-----------
 34
(1 fila)

If i use other numbers, i got even some errors:

select substring('1234' from to_number('33', '999999') for 3);

ERROR:  la expresin regular no es vlida: invalid backreference number
CONTEXTO:  funcin SQL substring en la sentencia 1

This is a sample, really i like to use an expression using date_part() as
the first parameter for substring.


Best Regards,
Carlos

Hello

It has little bit strange behave on 8.3. It works well, but I have to
use casting.

postgres=# select substring('1234' from to_number('3', '999999') for 3);
ERROR:  function pg_catalog.substring(unknown, numeric, integer) does not exist
LINE 1: select substring('1234' from to_number('3', '999999') for 3)...
               ^
HINT:  No function matches the given name and argument types. You
might need to add explicit type casts.

postgres=# select substring('1234' from to_number('3', '999999')::int for 3);
 substring
-----------
 34
(1 row)

Regards
Pavel Stehule



2007/7/10, Carlos Quintero <carlos(dot)quintero(dot)sspa(at)juntadeandalucia(dot)es>:
>
> The following bug has been logged online:
>
> Bug reference:      3435
> Logged by:          Carlos Quintero
> Email address:      carlos(dot)quintero(dot)sspa(at)juntadeandalucia(dot)es
> PostgreSQL version: 8.2.4
> Operating system:   Linux RedHat Enterprise 3
> Description:        problem with substring function
> Details:
>
> Hi,
>
> I like to use a numerical expression as parameter for the substring
> function, but it works always as i use regular expressions. Please, try
> this:
>
> select substring('1234' from to_number('3', '999999') for 3);
>
> What returns a empty string:
>
>  substring
> -----------
>
> (1 fila)
>
> But the right result must be this:
>
> select substring('1234' from 3 for 3);
>
>  substring
> -----------
>  34
> (1 fila)
>
> If i use other numbers, i got even some errors:
>
> select substring('1234' from to_number('33', '999999') for 3);
>
> ERROR:  la expresión regular no es válida: invalid backreference number
> CONTEXTO:  función SQL «substring» en la sentencia 1
>
> This is a sample, really i like to use an expression using date_part() as
> the first parameter for substring.
>
>
> Best Regards,
> Carlos
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>

"Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com> writes:
> It has little bit strange behave on 8.3. It works well, but I have to
> use casting.

> postgres=# select substring('1234' from to_number('3', '999999')::int for 3);
>  substring
> -----------
>  34
> (1 row)

Yeah, the OP is getting burnt by an implicit cast to text, which the
parser picks because there is no implicit cast from numeric to integer
... but substring(text,text,text) has completely different behavior
from substring(text,int,int).

The reason we got rid of most implicit casts to text for 8.3 was exactly
to stop surprising choices like this one.

>> This is a sample, really i like to use an expression using date_part() as
>> the first parameter for substring.

There's no implicit cast from float8 to integer, either.

			regards, tom lane