Re: Prepared statements and unknown types

On 29 September 2010 19:15, Peter Bex <Peter(dot)Bex(at)xs4all(dot)nl> wrote:
> On Wed, Sep 29, 2010 at 07:08:22PM +0100, Thom Brown wrote:
>> Could someone explain why the following doesn't work?
>>
>> test=# PREPARE meow(unknown) AS
>> test-# SELECT $1 as meow;
>> ERROR:  could not determine data type of parameter $1
>>
>> The problem is that using PDO in PHP, prepared statements aren't
>> possible if values are used instead of columns in the select list.
>
> The type is always string for data that's sent; it's converted to
> an appropriate type when the destination of the parameter is determined.
> If you know the type, you can do
>
> PREPARE meow(text) AS
> SELECT $1 as meow;
>
> or
>
> PREPARE meow(unknown) AS
> SELECT $1::text as meow;
>
> You can also send a parameter as a specific type using the C interface,
> but that requires support from the language/library you're using.
>
>> This appears to be allowed for MySQL and SQL Server.
>
> I don't know how they handle that.  Perhaps they try to read your mind.
> Perhaps PHP adds some kind of type conversion for types it knows for
> those two interfaces.

Okay, I understand what's happening. But does the planner need to
understand the type of literals in the select list if it's not used
anywhere else?

--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935