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.
On Tue, Jan 6, 2009 at 12:15 PM, Pavel Stehule <pavel.steh...@gmail.com>wrote:
> 2009/1/6 Gurjeet Singh <singh.gurj...@gmail.com>:
> > As I mentioned, we cannot change the query, so adding casts to the query
> is
> > not an option. I was looking for something external to the query, like a
> > CREATE CAST command that'd resolve the issue.
>
> I am sorry, I blind - I tested casting on 8.3.0 and it doesn't work
> (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.steh...@gmail.com>
> > wrote:
> >>
> >> Hello
> >>
> >> 2009/1/6 Gurjeet Singh <singh.gurj...@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[.sin...@enterprisedb.com
> >> > singh.gurj...@{ gmail | hotmail | indiatimes | yahoo }.com
> >> >
> >> > EnterpriseDB http://www.enterprisedb.com
> >> >
> >> > Mail sent from my BlackLaptop device
> >> >
> >
> >
> >
> > --
> > gurjeet[.sin...@enterprisedb.com
> > singh.gurj...@{ gmail | hotmail | indiatimes | yahoo }.com
> >
> > EnterpriseDB http://www.enterprisedb.com
> >
> > Mail sent from my BlackLaptop device
> >
>
--
gurjeet[.sin...@enterprisedb.com
singh.gurj...@{ gmail | hotmail | indiatimes | yahoo }.com
EnterpriseDB http://www.enterprisedb.com
Mail sent from my BlackLaptop device
