Re: WIP: hooking parser - Mailing list pgsql-hackers

2009/2/16 Sam Mason <sam@samason.me.uk>:
> On Mon, Feb 16, 2009 at 03:21:12PM +0100, Pavel Stehule wrote:
>> 2009/2/16 Sam Mason <sam@samason.me.uk>:
>> > On Mon, Feb 16, 2009 at 02:35:54PM +0100, Pavel Stehule wrote:
>> >> attachment contains module that transform every empty string to null.
>> >
>> > Why would anyone ever want to do this?  This would appear to break all
>> > sorts of things in very non-obvious ways:
>>
>> I agree, so this behave is strange - but Oracle does it.
>>
>> so normal query in Oracle for empty value looks like
>>
>> select * from people where surname is null;
>>
>> and some application expect transformation from '' to null.
>>
>> http://www.thunderguy.com/semicolon/2003/04/26/oracle-empty-string-null/
>
> that's pretty grim!
>
> <rant>
>  I'd agree with the comment saying "A string variable that can't be
>  set empty is like a number variable that can't be set zero".
>
>  Oracle have just thrown out (or, at best, rewritten) the inductive
>  base case for strings.  For numbers you (logically) start with a Zero
>  and a Succ (successor function) and model numbers as an arbitrary
>  number of applications of Succ to Zero ("2" is (Succ (Succ Zero))).
>  For strings, you start with an empty string and an append function
>  ("hi" being (Append (Append '' \h) \i)).
> </rant>
>
>> so these modules (decode, oraemptystr) decrease differences between
>> PostgreSQL and Oracle.
>
> wouldn't it be better/easier to extend something like pgpool to
> transform Oracle style SQL code to PG style code?  You'd certainly
> be able to get it more complete in reasonable amounts of time, but
> performance would suffer when you went to look up table definitions to
> check the types of various things.
>

then you should to rewrite complete PostgreSQL parser :) and
performance will be worse (you have to parse query string two times).
For this transformation you need query's semantic tree and access to
dictionary (some caches) . Lot of things should by done via
extensibility features of PostgreSQL.  Sure - you can do this things
difficult outside of PostgreSQL or simply via parser's hook.

These samples are only for Oracle. But I am sure, so this technique
should be used for different databases too. Example. Informix uses
convention for named params like paramname = value. PostgreSQL 8.5
will use syntax paramname AS value. So you need change app. code. With
hook I am able transform transparently Informix syntax to PostgreSQL
syntax without significant increase of load or complexity.

regards
Pavel Stehule

> --
>  Sam  http://samason.me.uk/
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>