Re: Implicit cast of literal in SQL statements

I've got some 'legacy' code that I'm dealing with - sometimes it will
receive query requests that are simply the union of two easier requests
it already knows the sql for.
These 'easier' queries have 'distincts' in them, and the code doesn't go
to the trouble of removing by hand when doing a union.
so the query ends up looking like:
SELECT DISTINCT firstName, middleName, lastName FROM completeNameTable
WHERE (...)
UNION
SELECT DISTINCT firstName, ' ', lastName FROM partialNameTable WHERE(...)

ugly, I know. ( and probably inefficient, I should check the plan )

thanks for the quick response

On Thursday, December 27, 2001, at 03:28 PM, Thomas Lockhart wrote:

>> I've seen a few postings in multiple newsgroups saying that in 7.1.x
>> and
>> up, literals in SQL statements are implicitly cast to strings.
>
> In some contexts, that statement is true, yes. The cases where this is
> true is when the parser is trying to match literals with available
> function calls. If there is a literal of unknown type, and if there is a
> function which could take a string literal as input, then that function
> is the one chosen.
>
>> does this really work for everybody else? Can someone point me to a
>> compile flag I may be missing, or the code that actually does the
>> implicit cast?
>
> It looks like we are not handling the case where there is no explicit
> function call, and there a string literal in the target list (so no
> underlying column to infer a type from), and there is a subsequent
> ordering operation. That might be fixable, but it may not be a useful
> real world example afaict.
>
> Do you have another example to illustrate the problem for a query which
> one might actually need to use?
>
> - Thomas
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>

_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com