PostgreSQL cast using ::

ezemasa · Post by **ezemasa** » 14.01.2010, 18:22

Just add the marked line, otherwise it'll be treated as a parameter and throws syntax error

constructor TZPostgreSQLSymbolState.Create;
begin
  inherited Create;
  Add('<=');
  Add('>=');
  Add('<>');
  Add('<<');
  Add('>>');
+  Add('::');  
end;

sample query:

SELECT ID, 'HELLO'::varchar FROM TABLE

thaks a lot

excelent work

Wild_Pointer · Post by **Wild_Pointer** » 15.01.2010, 08:30

Hello, ezemasa

:: is treated as : in query. So instead of SELECT ID, 'HELLO'::varchar FROM TABLE you should write SELECT ID, 'HELLO'::::varchar FROM TABLE

I don't think this should be fixed (or at least that way) because then there would be no way to put single : into sql

ezemasa · Post by **ezemasa** » 15.01.2010, 15:13

Adding that line does not avoid the use of single ':', I tested queries with params and they work fine. The same way the previous line ( Add('>>') ) doesn't avoid the use of a single '>'.

On the other hand, the current solution requires to escape characters when writing queries.
Not a big problem only if the queries are hard coded, but if they come form different sources and even the user (when he can generate his own reports) this is a big problem and gets bigger if the customer has a lot of queries written already.

I didn't see the need to wrap de ZQuery just to escape the '::'; if the ZTokenizer powerfull enough to perform this task.

Wild_Pointer · Post by **Wild_Pointer** » 15.01.2010, 16:08

Ok,

there is is just one hypothetical question - how would one pass single : to postgresql server. (I cant think of reason why you should do it, but...) If I'd want do that now I'd use :: in SQL.text, but if :: is interpreted as :: then what is interpreted as : ? Or am I missing the point here?

ezemasa · Post by **ezemasa** » 15.01.2010, 19:00

I don't understand your question but maybe this example will help:

select 'HELLO:WORLD'::varchar, * from TEST_TABLE where ID = :ID and NAME <> 'SOME:THING'

This query works fine, the tokenizer doesn't get confused between ':' and '::', so you write de query the same just like you do in pg_admin. Or if you have de query text in the source code you only need to escape de quotes as usual, because of delphi strings, without changing anything because of ZeosLib

Wild_Pointer · Post by **Wild_Pointer** » 18.01.2010, 08:53

Hello, ezemasa

What I wanted to ask is: for example in postgresql 8.9 operator : appears. In pgAdmin you will write SELECT column:something FROM table. Now in TZQuery we would write SELECT column::something FROM table, as :: is converted to : . How would this query look like after applying your patch?

Also how about backwards compatibility? Would people who used :::: have to rewrite queries?

ezemasa · Post by **ezemasa** » 18.01.2010, 20:24

Ok, now I undersand, I didn't know about the PostgreSQL arrays. I fixed it so it works, but the legacy is a big issue (that's why I made this change in the firt place, to deal with my own legacy queries).

I don't think this should be included if breaks any backwards compatibility.