The attached code patch (seems to work, but no regression tests or doc
fixes yet) allows plpgsql variable declarations like
declare
x text collate "en_US";
Such a collation spec overrides the default variable collation that is
otherwise inherited from the function's input collation. I believe
this is essentially what we agreed to do a couple weeks ago.
Comments, objections?
regards, tom lane
diff --git a/src/pl/plpgsql/src/gram.y b/src/pl/plpgsql/src/gram.y
index fbd441a1bc986835d9dc8729ff61ab61ee4d06d4..4e2b7058f0c471182b8276a101e33fa96ed01bdb 100644
*** a/src/pl/plpgsql/src/gram.y
--- b/src/pl/plpgsql/src/gram.y
***************
*** 21,26 ****
--- 21,27 ----
#include "parser/parse_type.h"
#include "parser/scanner.h"
#include "parser/scansup.h"
+ #include "utils/builtins.h"
/* Location tracking support --- simpler than bison's default */
*************** static List *read_raise_options(void);
*** 122,127 ****
--- 123,129 ----
PLcword cword;
PLwdatum wdatum;
bool boolean;
+ Oid oid;
struct
{
char *name;
*************** static List *read_raise_options(void);
*** 167,172 ****
--- 169,175 ----
%type <boolean> decl_const decl_notnull exit_type
%type <expr> decl_defval decl_cursor_query
%type <dtype> decl_datatype
+ %type <oid> decl_collate
%type <datum> decl_cursor_args
%type <list> decl_cursor_arglist
%type <nsitem> decl_aliasitem
*************** static List *read_raise_options(void);
*** 245,250 ****
--- 248,254 ----
%token <keyword> K_BY
%token <keyword> K_CASE
%token <keyword> K_CLOSE
+ %token <keyword> K_COLLATE
%token <keyword> K_CONSTANT
%token <keyword> K_CONTINUE
%token <keyword> K_CURSOR
*************** decl_stmt : decl_statement
*** 428,437 ****
}
;
! decl_statement : decl_varname decl_const decl_datatype decl_notnull decl_defval
{
PLpgSQL_variable *var;
var = plpgsql_build_variable($1.name, $1.lineno,
$3, true);
if ($2)
--- 432,458 ----
}
;
! decl_statement : decl_varname decl_const decl_datatype decl_collate decl_notnull decl_defval
{
PLpgSQL_variable *var;
+ /*
+ * If a collation is supplied, insert it into the
+ * datatype. We assume decl_datatype always returns
+ * a freshly built struct not shared with other
+ * variables.
+ */
+ if (OidIsValid($4))
+ {
+ if (!OidIsValid($3->collation))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATATYPE_MISMATCH),
+ errmsg("collations are not supported by type %s",
+ format_type_be($3->typoid)),
+ parser_errposition(@4)));
+ $3->collation = $4;
+ }
+
var = plpgsql_build_variable($1.name, $1.lineno,
$3, true);
if ($2)
*************** decl_statement : decl_varname decl_const
*** 444,453 ****
errmsg("row or record variable cannot be CONSTANT"),
parser_errposition(@2)));
}
! if ($4)
{
if (var->dtype == PLPGSQL_DTYPE_VAR)
! ((PLpgSQL_var *) var)->notnull = $4;
else
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
--- 465,474 ----
errmsg("row or record variable cannot be CONSTANT"),
parser_errposition(@2)));
}
! if ($5)
{
if (var->dtype == PLPGSQL_DTYPE_VAR)
! ((PLpgSQL_var *) var)->notnull = $5;
else
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
*************** decl_statement : decl_varname decl_const
*** 455,464 ****
parser_errposition(@4)));
}
! if ($5 != NULL)
{
if (var->dtype == PLPGSQL_DTYPE_VAR)
! ((PLpgSQL_var *) var)->default_val = $5;
else
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
--- 476,485 ----
parser_errposition(@4)));
}
! if ($6 != NULL)
{
if (var->dtype == PLPGSQL_DTYPE_VAR)
! ((PLpgSQL_var *) var)->default_val = $6;
else
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
*************** decl_datatype :
*** 685,690 ****
--- 706,724 ----
}
;
+ decl_collate :
+ { $$ = InvalidOid; }
+ | K_COLLATE T_WORD
+ {
+ $$ = get_collation_oid(list_make1(makeString($2.ident)),
+ false);
+ }
+ | K_COLLATE T_CWORD
+ {
+ $$ = get_collation_oid($2.idents, false);
+ }
+ ;
+
decl_notnull :
{ $$ = false; }
| K_NOT K_NULL
*************** read_datatype(int tok)
*** 2432,2438 ****
yyerror("incomplete data type declaration");
}
/* Possible followers for datatype in a declaration */
! if (tok == K_NOT || tok == '=' || tok == COLON_EQUALS || tok == K_DEFAULT)
break;
/* Possible followers for datatype in a cursor_arg list */
if ((tok == ',' || tok == ')') && parenlevel == 0)
--- 2466,2473 ----
yyerror("incomplete data type declaration");
}
/* Possible followers for datatype in a declaration */
! if (tok == K_COLLATE || tok == K_NOT ||
! tok == '=' || tok == COLON_EQUALS || tok == K_DEFAULT)
break;
/* Possible followers for datatype in a cursor_arg list */
if ((tok == ',' || tok == ')') && parenlevel == 0)
diff --git a/src/pl/plpgsql/src/pl_scanner.c b/src/pl/plpgsql/src/pl_scanner.c
index e8a2628f2f1dfbdd6ba466d7216c3a2ee0906d19..e1c0b625954a6c6cee45932e849df681e8d1508b 100644
*** a/src/pl/plpgsql/src/pl_scanner.c
--- b/src/pl/plpgsql/src/pl_scanner.c
*************** static const ScanKeyword reserved_keywor
*** 64,69 ****
--- 64,70 ----
PG_KEYWORD("by", K_BY, RESERVED_KEYWORD)
PG_KEYWORD("case", K_CASE, RESERVED_KEYWORD)
PG_KEYWORD("close", K_CLOSE, RESERVED_KEYWORD)
+ PG_KEYWORD("collate", K_COLLATE, RESERVED_KEYWORD)
PG_KEYWORD("continue", K_CONTINUE, RESERVED_KEYWORD)
PG_KEYWORD("declare", K_DECLARE, RESERVED_KEYWORD)
PG_KEYWORD("default", K_DEFAULT, RESERVED_KEYWORD)