On Mon, Feb 16, 2009 at 08:03:42PM +0100, Pavel Stehule wrote:
> 2009/2/16 Sam Mason <sam@samason.me.uk>:
> > But to do it properly inside PG would be difficult; how would your hooks
> > know to transform:
> >
> > SELECT s FROM foo WHERE s IS NULL;
> >
> > into:
> >
> > SELECT s FROM foo WHERE (s = '' OR s IS NULL);
>
> I don't need it. Oracle store NULL without ''. So expression some IS
> NULL is stable.
OK, I was under the impression that you wanted general Oracle
compatibility from PG. Apparently this isn't the case.
> > that all looks a bit tricky to me. Hum... actually it's not. All you
> > need to do is to rewrite any string reference "s" into NULLIF(s,'').
> > That would tank performance as indexes wouldn't be used most of the
> > time, but never mind.
>
> look to source what I do. It' just simple. But you have to emulate
> Oracle behave everywhere. Then all is simple, because Oracle doesn't
> know ''.
Yes, I read your code. You'll still get zero length strings back from
things like substring('hello world',1,0) and not a NULL as I expect
you'd get back from Oracle.
[ context removed by Pavel; but the example was supporting Informix
style named parameters by PG ]
> > That would be a *much* bigger change; you're actually changing PG's
> > parser there and not just modifying the parse tree. If it was done
> > externally it would be a much easier thing to do.
>
> No I don't do it. Loadable modules are really external. I need only
> hook inside parser.
As far as I checked, your code gets passed some subset of the parse
tree. For the parser to have a chance of getting the code to your hook
it would need to be considered valid syntax. Informix style named
parameters isn't considered valid by PG's parser and hence the user will
get an error before the hook would get a chance to rewrite the parse
tree and make it valid. This is basically what Tom was alluding to
here:
http://archives.postgresql.org/pgsql-hackers/2009-02/msg00574.php
-- Sam http://samason.me.uk/