PostgreSQL里我们随便运行一个不带别名的子查询:
postgres=# select * from (select * from pg_class);
ERROR: subquery in FROM must have an alias
LINE 1: select * from (select * from pg_class);
^
HINT: For example, FROM (SELECT ...) [AS] foo.
postgres=#
而带子查询的是这样:
postgres=# select amname from (select * from pg_am) as a limit 3;
amname
--------
btree
hash
gist
(3 rows)
postgres=#
Oracle下,有无别名均可:
SQL> select * from (select * from tab);
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
COUNTRIES TABLE
DEPARTMENTS TABLE
EMPLOYEES TABLE
EMP_DETAILS_VIEW VIEW
JOBS TABLE
JOB_HISTORY TABLE
LOCATIONS TABLE
REGIONS TABLE
8 rows selected.
SQL>
没什么大的影响,但如果遇到Oracle程序向PG迁移,改起来就有点罗嗦,这时候我们不妨让PG变化一下。
这个目标是非常明确,直接去源代码里边:grep -r "subquery in FROM must have an alias",发现它在 src/backend/parser/gram.y
沿着代码往下看,可以看到子查询别名为空时报错,办法就很直截了当了,未指定别名时给他产生一个。
出错代码替换为:
Alias *a = makeNode(Alias);
a->aliasname = "alias_xxxx";
n->alias = a;
编译之后运行:
template1=# select amname from (select * from pg_am) limit 3;
amname
--------
btree
hash
gist
(3 rows)
template1=#
小打小闹修改PG就是如此简单,当然这里还有更多工作要做,比如别名应该随机生成而不是像这样写死。
template1=# select amname from (select * from pg_am), (select * from pg_database);
ERROR: table name "alias_xxxx" specified more than once
template1=#
这肯定不是我们想要的结果
作为一个演示的例子,到此已经足够。
神州飞象(北京)数据科技有限公司,专业PostgreSQL产品与服务提供商