[Devel] postgres problem with mysql proprietary SQL queries

[Bogdan-Andrei Iancu]

Hi Klaus,

I agree that the lcr module should stick to the DB API - by this all DB 
implementation will be fully supported without any discrimination 
(postgres, dbtext, ldap in the future, etc).

but looking into the code, I found this query:
    "SELECT %.*s.%.*s, %.*s.%.*s FROM %.*s, %.*s WHERE '%.*s' LIKE 
%.*s.%.*s AND '%.*s' LIKE CONCAT(%.*s.%.*s, '%%') AND %.*s.%.*s = 
%.*s.%.*s ORDER BY CHAR_LENGTH(%.*s.%.*s), %.*s.%.*s DESC, RAND()"

which is quite impossible to implement via API :(....If someone had some 
clue how to bring this query to a one supported by the DB API, I will be 
happy to switch to it....

for the moment, for postgres, the additional functions are the only way 
to make it work.....


regards,
bogdan


Klaus Darilion wrote:

> Ingo Wolfsberger wrote:
>
>> Hi
>>
>> Postgres is a real db ;)
>>
>> It should be possible to "create" the needed functions.
>> see: http://www.openrdf.org/issues/browse/SES-112
>>
>> Not tested:
>>
>> CONCAT:
>> CREATE FUNCTION "concat" (text,text) RETURNS text AS     'SELECT $1 
>> || $2;' LANGUAGE 'sql';
>>
>> RAND:
>> CREATE FUNCTION "rand" () RETURNS dp AS  'SELECT random();' LANGUAGE 
>> 'sql';
>
>
> This must be changed to:
> CREATE FUNCTION "rand" () RETURNS double precision AS
>   'SELECT random();' LANGUAGE 'sql';
>
> Then it works :-)
>
> Nevertheless, is "building mysql functions in postgres" the proper way 
> or should we make 2 different SQL queries in lcr module?
>
> Will there be any performance issues in postgres because of the self 
> created functions?
>
> klaus