I wrote the attached patch to optionally emit warnings when column or table
aliases are used without the AS keyword after errors caused by typos in
statements turning unintended things into aliases came up twice this week.
First in a discussion with a colleague who was surprised by a 1 row result
for the query 'SELECT COUNT(*) files' and again in the "pl/pgsql 2" thread
as plpgsql currently doesn't throw an error if there are more result columns
than output columns (SELECT a b INTO f1, f2).
The patch is still missing documentation and it needs another patch to
modify all the statements in psql & co to use AS so you can use things like
\d and tab-completion without triggering the warnings. I can implement
those changes if others think this patch makes sense.
/ Oskari
>From 478e694e5281a3bf91e44177ce925e6625cb44a5 Mon Sep 17 00:00:00 2001
From: Oskari Saarenmaa <o...@ohmu.fi>
Date: Fri, 5 Sep 2014 22:31:22 +0300
Subject: [PATCH] parser: optionally warn about missing AS for column and table
aliases
Add a new GUC "missing_as_warning" (defaults to false, the previous
behavior) to raise a WARNING when a column or table alias is used without
the AS keyword.
This allows catching some types of errors where another keyword or a comma
was missing and a label is being used as an alias instead of something else,
for example cases like:
SELECT COUNT(*) files;
SELECT * FROM files users;
SELECT path size FROM files INTO f_path, f_size;
---
src/backend/parser/gram.y | 24 +++++
src/backend/utils/misc/guc.c | 11 +++
src/include/parser/parser.h | 2 +
src/test/regress/expected/select.out | 170 +++++++++++++++++++++++++++++++++++
src/test/regress/sql/select.sql | 47 ++++++++++
5 files changed, 254 insertions(+)
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index b46dd7b..06a71dd 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -65,6 +65,10 @@
#include "utils/xml.h"
+/* GUCs */
+bool missing_as_warning = false;
+
+
/*
* Location tracking support --- simpler than bison's default, since we only
* want to track the start position not the end position of each nonterminal.
@@ -10119,12 +10123,20 @@ alias_clause:
}
| ColId '(' name_list ')'
{
+ if (missing_as_warning)
+ ereport(WARNING,
+ (errmsg("alias
without explicit AS and missing_as_warning enabled"),
+
parser_errposition(@3)));
$$ = makeNode(Alias);
$$->aliasname = $1;
$$->colnames = $3;
}
| ColId
{
+ if (missing_as_warning)
+ ereport(WARNING,
+ (errmsg("alias
without explicit AS and missing_as_warning enabled"),
+
parser_errposition(@1)));
$$ = makeNode(Alias);
$$->aliasname = $1;
}
@@ -10156,6 +10168,10 @@ func_alias_clause:
| ColId '(' TableFuncElementList ')'
{
Alias *a = makeNode(Alias);
+ if (missing_as_warning)
+ ereport(WARNING,
+ (errmsg("alias
without explicit AS and missing_as_warning enabled"),
+
parser_errposition(@1)));
a->aliasname = $1;
$$ = list_make2(a, $3);
}
@@ -10244,6 +10260,10 @@ relation_expr_opt_alias: relation_expr
%prec UMINUS
| relation_expr ColId
{
Alias *alias = makeNode(Alias);
+ if (missing_as_warning)
+ ereport(WARNING,
+ (errmsg("alias
without explicit AS and missing_as_warning enabled"),
+
parser_errposition(@2)));
alias->aliasname = $2;
$1->alias = alias;
$$ = $1;
@@ -12577,6 +12597,10 @@ target_el: a_expr AS ColLabel
*/
| a_expr IDENT
{
+ if (missing_as_warning)
+ ereport(WARNING,
+ (errmsg("alias
without explicit AS and missing_as_warning enabled"),
+
parser_errposition(@2)));
$$ = makeNode(ResTarget);
$$->name = $2;
$$->indirection = NIL;
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index af667f5..03b7457 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -1398,6 +1398,17 @@ static struct config_bool ConfigureNamesBool[] =
},
{
+ {"missing_as_warning", PGC_USERSET, COMPAT_OPTIONS_PREVIOUS,
+ gettext_noop("Warn about missing AS for column or table
aliases."),
+ NULL,
+ GUC_REPORT
+ },
+ &missing_as_warning,
+ false,
+ NULL, NULL, NULL
+ },
+
+ {
{"escape_string_warning", PGC_USERSET, COMPAT_OPTIONS_PREVIOUS,
gettext_noop("Warn about backslash escapes in ordinary
string literals."),
NULL
diff --git a/src/include/parser/parser.h b/src/include/parser/parser.h
index 8d2068a..b3e8a71 100644
--- a/src/include/parser/parser.h
+++ b/src/include/parser/parser.h
@@ -30,6 +30,8 @@ extern int backslash_quote;
extern bool escape_string_warning;
extern PGDLLIMPORT bool standard_conforming_strings;
+/* GUC variables in gram.y */
+extern bool missing_as_warning;
/* Primary entry point for the raw parsing functions */
extern List *raw_parser(const char *str);
diff --git a/src/test/regress/expected/select.out
b/src/test/regress/expected/select.out
index c376523..8cce371 100644
--- a/src/test/regress/expected/select.out
+++ b/src/test/regress/expected/select.out
@@ -781,3 +781,173 @@ select * from (values (2),(null),(1)) v(k) where k = k;
1
(2 rows)
+-- Test warnings for missing AS for aliases
+SET missing_as_warning TO false;
+select 1 foo, 2 bar;
+ foo | bar
+-----+-----
+ 1 | 2
+(1 row)
+
+select 1 as foo, 2 as bar;
+ foo | bar
+-----+-----
+ 1 | 2
+(1 row)
+
+select count(*) foo;
+ foo
+-----
+ 1
+(1 row)
+
+select 1 from onek o1 limit 1;
+ ?column?
+----------
+ 1
+(1 row)
+
+select 1 from onek o1, onek o2 limit 1;
+ ?column?
+----------
+ 1
+(1 row)
+
+select 1 from onek as o1, onek as o2 limit 1;
+ ?column?
+----------
+ 1
+(1 row)
+
+select un1 from onek ok (un1) where un1 = 88888888;
+ un1
+-----
+(0 rows)
+
+select un1 from onek as ok (un1) where un1 = 88888888;
+ un1
+-----
+(0 rows)
+
+update onek ok set unique1 = -88888888 where unique1 = 88888888;
+update onek as ok set unique1 = -88888888 where unique1 = 88888888;
+create function func_for_missing_as() returns record language sql as 'select
1::int';
+select * from func_for_missing_as() f (id int);
+ id
+----
+ 1
+(1 row)
+
+select * from func_for_missing_as() as f (id int);
+ id
+----
+ 1
+(1 row)
+
+SET missing_as_warning TO true;
+select 1 foo, 2 bar;
+WARNING: alias without explicit AS and missing_as_warning enabled
+LINE 1: select 1 foo, 2 bar;
+ ^
+WARNING: alias without explicit AS and missing_as_warning enabled
+LINE 1: select 1 foo, 2 bar;
+ ^
+ foo | bar
+-----+-----
+ 1 | 2
+(1 row)
+
+select 1 as foo, 2 as bar;
+ foo | bar
+-----+-----
+ 1 | 2
+(1 row)
+
+select count(*) foo;
+WARNING: alias without explicit AS and missing_as_warning enabled
+LINE 1: select count(*) foo;
+ ^
+ foo
+-----
+ 1
+(1 row)
+
+do $$
+declare
+ i1 int;
+ i2 int;
+begin
+ select unique1 unique2 FROM onek into i1, i2;
+ select unique1, unique2 FROM onek into i1, i2;
+end;
+$$;
+WARNING: alias without explicit AS and missing_as_warning enabled
+LINE 6: select unique1 unique2 FROM onek into i1, i2;
+ ^
+WARNING: alias without explicit AS and missing_as_warning enabled
+LINE 1: select unique1 unique2 FROM onek
+ ^
+QUERY: select unique1 unique2 FROM onek
+CONTEXT: PL/pgSQL function inline_code_block line 6 at SQL statement
+select 1 from onek o1 limit 1;
+WARNING: alias without explicit AS and missing_as_warning enabled
+LINE 1: select 1 from onek o1 limit 1;
+ ^
+ ?column?
+----------
+ 1
+(1 row)
+
+select 1 from onek o1, onek o2 limit 1;
+WARNING: alias without explicit AS and missing_as_warning enabled
+LINE 1: select 1 from onek o1, onek o2 limit 1;
+ ^
+WARNING: alias without explicit AS and missing_as_warning enabled
+LINE 1: select 1 from onek o1, onek o2 limit 1;
+ ^
+ ?column?
+----------
+ 1
+(1 row)
+
+select 1 from onek as o1, onek as o2 limit 1;
+ ?column?
+----------
+ 1
+(1 row)
+
+select un1 from onek ok (un1) where un1 = 88888888;
+WARNING: alias without explicit AS and missing_as_warning enabled
+LINE 1: select un1 from onek ok (un1) where un1 = 88888888;
+ ^
+ un1
+-----
+(0 rows)
+
+select un1 from onek as ok (un1) where un1 = 88888888;
+ un1
+-----
+(0 rows)
+
+update onek ok set unique1 = -88888888 where unique1 = 88888888;
+WARNING: alias without explicit AS and missing_as_warning enabled
+LINE 1: update onek ok set unique1 = -88888888 where unique1 = 88888...
+ ^
+update onek as ok set unique1 = -88888888 where unique1 = 88888888;
+select * from func_for_missing_as() f (id int);
+WARNING: alias without explicit AS and missing_as_warning enabled
+LINE 1: select * from func_for_missing_as() f (id int);
+ ^
+ id
+----
+ 1
+(1 row)
+
+select * from func_for_missing_as() as f (id int);
+ id
+----
+ 1
+(1 row)
+
+drop function func_for_missing_as();
+SET missing_as_warning TO false;
diff --git a/src/test/regress/sql/select.sql b/src/test/regress/sql/select.sql
index b99fb13..50823b5 100644
--- a/src/test/regress/sql/select.sql
+++ b/src/test/regress/sql/select.sql
@@ -207,3 +207,50 @@ drop function sillysrf(int);
-- (see bug #5084)
select * from (values (2),(null),(1)) v(k) where k = k order by k;
select * from (values (2),(null),(1)) v(k) where k = k;
+
+-- Test warnings for missing AS for aliases
+SET missing_as_warning TO false;
+select 1 foo, 2 bar;
+select 1 as foo, 2 as bar;
+select count(*) foo;
+
+select 1 from onek o1 limit 1;
+select 1 from onek o1, onek o2 limit 1;
+select 1 from onek as o1, onek as o2 limit 1;
+select un1 from onek ok (un1) where un1 = 88888888;
+select un1 from onek as ok (un1) where un1 = 88888888;
+update onek ok set unique1 = -88888888 where unique1 = 88888888;
+update onek as ok set unique1 = -88888888 where unique1 = 88888888;
+
+create function func_for_missing_as() returns record language sql as 'select
1::int';
+select * from func_for_missing_as() f (id int);
+select * from func_for_missing_as() as f (id int);
+
+SET missing_as_warning TO true;
+select 1 foo, 2 bar;
+select 1 as foo, 2 as bar;
+select count(*) foo;
+
+do $$
+declare
+ i1 int;
+ i2 int;
+begin
+ select unique1 unique2 FROM onek into i1, i2;
+ select unique1, unique2 FROM onek into i1, i2;
+end;
+$$;
+
+select 1 from onek o1 limit 1;
+select 1 from onek o1, onek o2 limit 1;
+select 1 from onek as o1, onek as o2 limit 1;
+select un1 from onek ok (un1) where un1 = 88888888;
+select un1 from onek as ok (un1) where un1 = 88888888;
+update onek ok set unique1 = -88888888 where unique1 = 88888888;
+update onek as ok set unique1 = -88888888 where unique1 = 88888888;
+
+select * from func_for_missing_as() f (id int);
+select * from func_for_missing_as() as f (id int);
+
+drop function func_for_missing_as();
+SET missing_as_warning TO false;
--
1.9.3
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
