Re: ERROR: failed to find conversion function from "unknown" to text - Mailing list pgsql-hackers
| From | Gurjeet Singh |
|---|---|
| Subject | Re: ERROR: failed to find conversion function from "unknown" to text |
| Date | |
| Msg-id | 65937bea0901060104n399cdec8ye5e2b8e247e5139a@mail.gmail.com Whole thread Raw |
| In response to | Re: ERROR: failed to find conversion function from "unknown" to text ("Pavel Stehule" <pavel.stehule@gmail.com>) |
| Responses |
Re: [GENERAL] ERROR: failed to find conversion function from "unknown" to text
Re: [GENERAL] ERROR: failed to find conversion function from "unknown" to text |
| List | pgsql-hackers |
I took your cue, and have formulated this solution for 8.3.1 :
create or replace function unknown2text(unknown) returns text as
$$ begin return text($1::char); end $$ language plpgsql;
drop cast (unknown as text);
create cast (unknown as text) with function unknown2text( unknown ) as implicit;
select '' union all select * from (select '' ) as s;
Thanks for your help Pavel.
Best regards,
PS: I was getting the same error as yours (stack depth) in EDB version 8.3.0.12, so I had to use the following code for unknown2text:
return charin( unknownout($1) );
It works for PG 8.3.1 too.
--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com
EnterpriseDB http://www.enterprisedb.com
Mail sent from my BlackLaptop device
create or replace function unknown2text(unknown) returns text as
$$ begin return text($1::char); end $$ language plpgsql;
drop cast (unknown as text);
create cast (unknown as text) with function unknown2text( unknown ) as implicit;
select '' union all select * from (select '' ) as s;
Thanks for your help Pavel.
Best regards,
PS: I was getting the same error as yours (stack depth) in EDB version 8.3.0.12, so I had to use the following code for unknown2text:
return charin( unknownout($1) );
It works for PG 8.3.1 too.
On Tue, Jan 6, 2009 at 12:15 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
2009/1/6 Gurjeet Singh <singh.gurjeet@gmail.com>:> As I mentioned, we cannot change the query, so adding casts to the query isI am sorry, I blind - I tested casting on 8.3.0 and it doesn't work
> not an option. I was looking for something external to the query, like a
> CREATE CAST command that'd resolve the issue.
(but I am have old 8.3)
postgres=# create function unknown2text(unknown) returns text as
$$select $1::text$$ language sql;
CREATE FUNCTION
postgres=# create cast(unknown as text) with function
unknown2text(unknown) as implicit;
CREATE CAST
postgres=# select '' union all select * from (select '' ) as s;
ERROR: stack depth limit exceeded
HINT: Increase the configuration parameter "max_stack_depth", after
ensuring the platform's stack depth limit is adequate.
CONTEXT: SQL function "unknown2text" during startup
SQL function "unknown2text" statement 1
SQL function "unknown2text" statement 1
SQL function "unknown2text" statement 1
SQL function "unknown2text" statement 1
SQL function "unknown2text" statement 1
It working on 8.4
postgres=# create cast (unknown as text) with inout as implicit;
CREATE CAST
postgres=# select '' union all select * from (select '' ) as s;?column?
----------
(2 rows)
regards
Pavel Stehule
>
> Best regards,
>
> On Tue, Jan 6, 2009 at 12:00 PM, Pavel Stehule <pavel.stehule@gmail.com>
> wrote:
>>
>> Hello
>>
>> 2009/1/6 Gurjeet Singh <singh.gurjeet@gmail.com>:
>> > Q1: select '' union all select ''
>> > Q2: select '' union all select * from (select '' ) as s
>> >
>> > version: PostgreSQL 8.3.1, compiled by Visual C++ build 1400
>> >
>> > Hi All,
>> >
>> > Q1 works just fine, but Q2 fails with:
>> >
>> > ERROR: failed to find conversion function from "unknown" to text
>> >
>> > Q2 is a generalization of a huge query we are facing, which we
>> > cannot
>> > modify. I don't think this is a 'removed-casts' problem generally faced
>> > in
>> > 8.3, but I may be wrong. Will adding some cast resolve this?
>>
>> yes
>>
>> postgres=# select '' union all select * from (select ''::text ) as s;
>> ?column?
>> ----------
>>
>>
>> (2 rows)
>>
>> regards
>> Pavel Stehule
>>
>> >
>> > Best regards,
>> > --
>> > gurjeet[.singh]@EnterpriseDB.com
>> > singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com
>> >
>> > EnterpriseDB http://www.enterprisedb.com
>> >
>> > Mail sent from my BlackLaptop device
>> >
>
>
>
> --
> gurjeet[.singh]@EnterpriseDB.com
> singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com
>
> EnterpriseDB http://www.enterprisedb.com
>
> Mail sent from my BlackLaptop device
>
--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com
EnterpriseDB http://www.enterprisedb.com
Mail sent from my BlackLaptop device
pgsql-hackers by date: