Re: Proposal to add --single-row to psql - Mailing list pgsql-hackers

On Sunday, May 12, 2013 4:53 AM Robert Haas wrote:
> On Sat, May 11, 2013 at 12:27 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > By the time you've got an expression tree, the problem is mostly
> solved,
> > at least so far as parser extension is concerned.
> 
> Right.
> 
> > More years ago than I care to admit, I worked on systems that had
> > run-time-extensible parsers at Hewlett-Packard, so technology for
> this
> > does exist.  But my (vague) memory of those systems is that the
> parser's
> > language capabilities were more limited than bison's, perhaps only
> > LL(1).  Parsing spec-compatible SQL that way might be a challenge.
> 
> If I understand bison correctly, it basically looks at the current
> parser state and the next token and decides to either shift that token
> onto the stack or reduce the stack using some rule.  If there's no
> matching rule, we error out.  If someone wants to inject new rules
> into the grammar, those state tables are all going to change.  But if
> we could contrive things so that the state tables are built
> dynamically and can be change as rules are added and removed, then it
> seems to me that we could let a loadable module supply (or delete)
> whatever grammar rules it likes.  Whenever it does this, we recompile
> the grammar on next use (and complain if we get ambiguities).  This
> does not sound all that easy to code, but at least in theory it seems
> doable.

Can we think of a pluggable parser whose responsibility is to produce raw
parse tree structure similar to PostgreSQL, but internally it can handle
different database syntax (e.g Oracle).
For example below syntaxes of Oracle can be converted to PostgreSQL raw
parse tree

Oracle syntax: 
SELECT 1 + 10 * 100 + 1000 from DUAL; 
PostgreSQL syntax: 
SELECT 1 + 10 * 100 + 1000; 


Oracle syntax: 
CREATE USER xxxx IDENTIFIED BY 'password'; 
ALTER USER xxxx IDENTIFiED BY 'password'; 
PostgreSQL syntax: 
CREATE USER xxxx PASSWORD 'password'; 
ALTER USER xxxx PASSWORD 'password';

Currently if somebody has to migrate from Sybase, Oracle, SQL Server, MySQL
to PostgreSQL, he has to either use some converter tool or enhance existing
gram.y to support it.
In case current PG parser is pluggable, he can write it's parser which can
produce PG specific parse tree for another database syntax. User can load
database specific 
Parser during server start time by specifying corresponding parser dll/so in
shared_preload_libraries. 
Advantage of writing PG based parser for other database syntax is user can
directly use script files of other database, no need of conversion every
time.

Though I am not sure, but I think 70~80% of commonly used SQL syntax is
common among different databases as all standard databases are SQL-spec
compliant, so if such a parser facility
is provided, it can save lot of time taken for conversion for customers who
want to migrate from other database syntax to PostgreSQL.


With Regards,
Amit Kapila.