Thread: Re: [QUESTIONS] is Postgres an SQL-based database?
> Is Postgres an SQL-based database or SQL is only an option?
Yes.
> I'd uploaded 6.3 and I'm palying with it. Congratulations to Developers
> PostgreSQL is now more SQL92-compliant.
> But I can't understand one thing; seems that Postgres is SQL-based. (PostgreSQL)
> ^^^
> Is there a reason why developers implemnts new functions not SQL-compatible ?
Yes. There is more to SQL and ORDBMS than SQL87/89/92.
> example:
> In this release there's a very useful function LENGTH(), thanks to Thomas.
> but it's not SQL. CHARACTER_LENGTH() or CHAR_LENGTH() this is SQL.
PostgreSQL is an ORDBMS engine with an SQL front end. SQL92, which does not address
the possibility of type extensibility, tends to define type-specific functions,
such as you mention, in a heavy and crude manner. We have other types for which
"length" is an obvious useful quality; why put the type name into the function
name? And why require two forms of the same function for every type??
The real implementation issue is this: for built-in functions, every function call
must currently have a unique name. For generic functions such as length, we define
a second "sql function" with the generic name which then refers to the built-in
unique function name. For character types, we would need two more of these "sql
functions" for each character type. That's four function definitions in pg_proc for
each character type as opposed to the two definitions we currently have.
You raise a good point, however, in that we should provide the SQL92-compatible
function name where possible (I think you have found one of the few cases where we
do not). Perhaps we can translate function names in the parser as we do for type
names? I'll look into it...
> Maybe there's a reason why somebody needs to invent again the wheel but I cannot
> understand it.
We are already working with a round wheel (well, at least ovoid), and are trying to
prevent it from becoming square :)
- Tom
> > In this release there's a very useful function LENGTH(), thanks to Thomas.
> > but it's not SQL. CHARACTER_LENGTH() or CHAR_LENGTH() this is SQL.
> Perhaps we can translate function names in the parser as we do for type
> names? I'll look into it...
I have gram.y patches to do this. No visible effect on the regression execution speed,
which thanks to Bruce is on my machine down to a record 2:23 elapsed execution time;
the last speeds were in the 2:31 range.
I'm planning on bumping the gram.y cvs version to 2.0 for this change, due to the
_large_ number of accumulated improvements. Any objections?
- Tom
> > > > In this release there's a very useful function LENGTH(), thanks to Thomas. > > > but it's not SQL. CHARACTER_LENGTH() or CHAR_LENGTH() this is SQL. > > Perhaps we can translate function names in the parser as we do for type > > names? I'll look into it... > > I have gram.y patches to do this. No visible effect on the regression execution speed, > which thanks to Bruce is on my machine down to a record 2:23 elapsed execution time; > the last speeds were in the 2:31 range. > > I'm planning on bumping the gram.y cvs version to 2.0 for this change, due to the > _large_ number of accumulated improvements. Any objections? Go ahead. Are there security problems? -- Bruce Momjian maillist@candle.pha.pa.us
> > I have gram.y patches to do this. No visible effect on the regression execution speed,
> > which thanks to Bruce is on my machine down to a record 2:23 elapsed execution time;
> > the last speeds were in the 2:31 range.
> >
> > I'm planning on bumping the gram.y cvs version to 2.0 for this change, due to the
> > _large_ number of accumulated improvements. Any objections?
>
> Go ahead. Are there security problems?
Security problems with the patch? No, I don't think so. The only thing I'm doing is
implementing xlateSqlFunc() similarly to the existing xlateSqlType(), and defining a
"func_name" state in place of just "name" which was used before.
The only downside is that backend functions implemented with names "char_length" and
"character_length" will be masked by the parser translation so cannot be executed. In this
case I think that is OK, but I should put something in the new docs about that :)
- Tom
On Wed, 4 Feb 1998, Thomas G. Lockhart wrote:
> > Is Postgres an SQL-based database or SQL is only an option?
>
> Yes.
>
> > I'd uploaded 6.3 and I'm palying with it. Congratulations to Developers
> > PostgreSQL is now more SQL92-compliant.
> > But I can't understand one thing; seems that Postgres is SQL-based. (PostgreSQL)
> > ^^^
> > Is there a reason why developers implemnts new functions not SQL-compatible ?
>
> Yes. There is more to SQL and ORDBMS than SQL87/89/92.
I agree with you Tom, PostgreSQL should be more than standard,
but users expect that PostgreSQL at least supports SQL syntax.
I agree with you if you talk about implement functions not supported by SQL
but I can't understand if you write the same function with another syntax.
PostgreSQL has many functions that doesn't follow SQL-standard syntax.
I can understand this:
ROLLBACK [ WORK ] -- SQL-syntax
rollback [transaction|work] -- PostgreSQL-syntax (this is more than standard)
and
ALTER TABLE <class_name> ADD [COLUMN] <attr> <type>; --SQL
alter table <class_name> [*] add column <attr> <type>; --PostgreSQL
In this case [*] show that PostgreSQL is an ORDBMS,
but what about keyword COLUMN? It sould be optional.
but I can't understand things like:
\connect <dbname|-> <user> --PostgreSQL
insted of:
CONNECT TO { DEFAULT | <SQL-server name> --SQL
[ AS <connection name> ]
[ USER <user name> ] }
or
CAST expression AS data-type --PostgreSQL
insted of:
CAST ( expression AS { data-type | domain } ) --SQL
>
> > example:
> > In this release there's a very useful function LENGTH(), thanks to Thomas.
> > but it's not SQL. CHARACTER_LENGTH() or CHAR_LENGTH() this is SQL.
>
> PostgreSQL is an ORDBMS engine with an SQL front end. SQL92, which does not address
> the possibility of type extensibility, tends to define type-specific functions,
> such as you mention, in a heavy and crude manner. We have other types for which
> "length" is an obvious useful quality; why put the type name into the function
> name? And why require two forms of the same function for every type??
>
> The real implementation issue is this: for built-in functions, every function call
> must currently have a unique name. For generic functions such as length, we define
> a second "sql function" with the generic name which then refers to the built-in
> unique function name. For character types, we would need two more of these "sql
> functions" for each character type. That's four function definitions in pg_proc for
> each character type as opposed to the two definitions we currently have.
Ok. That's a good reason. Thanks for your explanation. I understand now.
>
> You raise a good point, however, in that we should provide the SQL92-compatible
> function name where possible (I think you have found one of the few cases where we
> do not). Perhaps we can translate function names in the parser as we do for type
> names? I'll look into it...
Great!
>
> > Maybe there's a reason why somebody needs to invent again the wheel but I cannot
> > understand it.
>
> We are already working with a round wheel (well, at least ovoid), and are trying to
> prevent it from becoming square :)
>
Ok, you are doing an excellent work. Thanks for this great Database.
Ciao, Jose'
> I agree with you Tom, PostgreSQL should be more than standard,
> but users expect that PostgreSQL at least supports SQL syntax.
> I agree with you if you talk about implement functions not supported by SQL
> but I can't understand if you write the same function with another syntax.
Well, one explanation is that sometimes I get it wrong :)
> PostgreSQL has many functions that doesn't follow SQL-standard syntax.
> I can understand this:
>
> ROLLBACK [ WORK ] -- SQL-syntax
> rollback [transaction|work] -- PostgreSQL-syntax (this is more than standard)
>
> and
>
> ALTER TABLE <class_name> ADD [COLUMN] <attr> <type>; --SQL
> alter table <class_name> [*] add column <attr> <type>; --PostgreSQL
>
> In this case [*] show that PostgreSQL is an ORDBMS,
> but what about keyword COLUMN? It sould be optional.
>
> but I can't understand things like:
>
> \connect <dbname|-> <user> --PostgreSQL
> insted of:
> CONNECT TO { DEFAULT | <SQL-server name> --SQL
> [ AS <connection name> ]
> [ USER <user name> ] }
Connections are done by the frontend, which does not parse the SQL. We could think about
implementing this if we allowed the backend to force a database change. Don't know if
this is easy or reasonably so.
> CAST expression AS data-type --PostgreSQL
> insted of:
> CAST ( expression AS { data-type | domain } ) --SQL
Wow, I never noticed that parens are in the syntax! Will look at changing it...
- Tom