Received: from localhost (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 0CB6963219E;
	Tue,  6 Jan 2009 02:45:27 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 80849-06; Tue,  6 Jan 2009 02:45:23 -0400 (AST)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from mail-fx0-f26.google.com (mail-fx0-f26.google.com
	[209.85.220.26])
	by mail.postgresql.org (Postfix) with ESMTP id 7473063219D;
	Tue,  6 Jan 2009 02:45:23 -0400 (AST)
Received: by fxm7 with SMTP id 7so1418969fxm.19
	for <multiple recipients>; Mon, 05 Jan 2009 22:45:21 -0800 (PST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:received:received:message-id:date:from:to
	:subject:cc:in-reply-to:mime-version:content-type
	:content-transfer-encoding:content-disposition:references;
	bh=IQhxAw40Dvrido0o/mDnJ6YiD+OdGuYkRPFL9tR68O4=;
	b=RcL/lZfPusykpsP3gBnsoF/VPREOBFxofIFoidTiaky16ENEC7Fqs2GxGrE/zPrwIa
	iwAkQ6PsOsBy8/nPn8E1CJ38Y5S+LT/1WpZRIMptuaMlrRHipRjGyjaDwHznCBGhgJfZ
	zi6jHYDnd42N7uv7f25QJ2wwbFGbl74ZZ00uk=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:to:subject:cc:in-reply-to:mime-version
	:content-type:content-transfer-encoding:content-disposition
	:references;
	b=Bj2EcoRmt9Y/YN6iIVWBYeG8LJp0jr5AhzMI2rHyVR4HFDDv6g+o8dpNAwYBZjeFTf
	a+ve5fmziTM32I+EIq0OgTh//pBnv+ZI0m/RL0kiuoYp/eqvU2qJgCEOtv+gWqF7k6zq
	60UtSzIgfoKroILgGMEU8KDt5sYxBd93bSM10=
Received: by 10.181.226.2 with SMTP id d2mr7415700bkr.15.1231224321693;
	Mon, 05 Jan 2009 22:45:21 -0800 (PST)
Received: by 10.180.226.20 with HTTP; Mon, 5 Jan 2009 22:45:21 -0800 (PST)
Message-ID: <162867790901052245l4e4fa9b2ydc03781b45b13276@mail.gmail.com>
Date: Tue, 6 Jan 2009 07:45:21 +0100
From: "Pavel Stehule" <pavel.stehule@gmail.com>
To: "Gurjeet Singh" <singh.gurjeet@gmail.com>
Subject: Re: [HACKERS] ERROR: failed to find conversion function from
	"unknown" to text
Cc: "PGSQL General" <pgsql-general@postgresql.org>,
	"PGSQL Hackers" <pgsql-hackers@postgresql.org>
In-Reply-To: <65937bea0901052237j50657573i8c980f9f5f4814d5@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <65937bea0901052223w162a977dyeaaf888a854f7324@mail.gmail.com>
	<162867790901052230l25b10f08qa35f56856b9cc4c6@mail.gmail.com>
	<65937bea0901052237j50657573i8c980f9f5f4814d5@mail.gmail.com>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none
X-Spam-Level: 
X-Archive-Number: 200901/69
X-Sequence-Number: 142154

2009/1/6 Gurjeet Singh <singh.gurjeet@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.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
>