Implementing implicit type cast
As stated in PostgreSQL help, a type cast specifies a conversion from one data type to another. PostgreSQL accepts two equivalent syntaxes for type casts:
CAST ( expression AS type )
expression::type
The CAST syntax conforms to SQL; the syntax with :: is historical PostgreSQL usage.
It is also possible to specify a type cast using a function-like syntax:
typename ( expression )
However, this works only for types whose names are also valid as function names. For example, double precision can't be used this way, but the equivalent float8 can. Also, the names interval, time, and timestamp can only be used in this fashion if they are double-quoted, because of syntactic conflicts. Therefore, the use of the function-like cast syntax leads to inconsistencies and should probably be avoided in new applications.
Programmers should be carefull using historical (::) type casting mechanism. Why one may ask?
The point is that parameter names in Delphi must begin with colon (:) following standard SQL-92 syntax conventions, e.g. :paramname. Thus to pass colon character itself we must double it. This situation is aplicable only if TPSQLQuery.ParamCheck property is set to True.
So the obvious workaround is to set TPSQLQuery.ParamCheck to False. However, this is not acceptable for all cases. Let's enumerate available workarounds when parameters check is on:
Using historical PostgreSQL type casting
Conforming to SQL-92 rules we must double each colon:
PSQLQuery1.SQL.Text := 'SELECT version() :::: varchar(100)';
Using SQL standard type casting
Insert CAST ... AS expression into SQL:
PSQLQuery1.SQL.Text := 'SELECT CAST(version() AS varchar(100))';
Using function-like type casting
This method has another limitation: we can't indicate type modifier, e.g. varchar(n), numeric(m,n) etc.
PSQLQuery1.SQL.Text := 'SELECT text( now() + ''1 day 12 hours 59 min 10 sec'' )';
Cases when type casting is useful
Here we'll show cases when implicit type cast may increase application speed or just make your life a bit easier.
Text (varchar without length) type using
Type text is the most used return type for string functions and operators, e.g. concatenation, convertion, trimming etc. are always returned as text. User defined types are treated as text in PostgresDAC also.
Text, varchar without length and varchar(>8192) types are treated by PostgresDAC as TMemoField. This affects in a different ways. First of all, TMemoField is a descendant of TBlobField which means that all in/out operations will be done using TStream descendants. Let's have a look on TBlobField.GetAsString method:
function TBlobField.GetAsString: string;
var
Len: Integer;
begin
with DataSet.CreateBlobStream(Self, bmRead) do
try
Len := Size;
SetString(Result, nil, Len);
ReadBuffer(Pointer(Result)^, Len);
finally
Free;
end;
end;
Also values of TMemoField aren't displayed in TDBGrid in a usual ways. We must use special controls to work with it, e.g. TDBMemo.
OID (Object IDentifier) type using
There are several intentions for OID type in PostgreSQL. From the one hand, object identifiers (OIDs) are used internally by PostgreSQL as primary keys for various system tables. From the other hand, OIDs are used to identify Large Objects, so all Large Object manipulation functions use OIDs as parametrs and return type.
One may ask: "Why don't you use LO type which is intended to indicate the LO identifier?" There are several issues. First of all, not all of the PostgreSQL versions have LO type. Second, it's defined in public schema, which is a kind of worrying fact, despite owner is postgres role. And third, it's not listed in PostgreSQL header files as having stable OID.
Although, TPSQLQuery and TPSQLTable have OIDAsInt property sometimes we might need to mix Large Objects OIDs with system one. In this case it's recommended to CAST system OIDs to integer (int4).
Since OID type is unsigned integer be careful when mixing integer types in your application!