Rethinking plpgsql's assignment implementation - Mailing list pgsql-hackers

From Tom Lane
Subject Rethinking plpgsql's assignment implementation
Date
Msg-id 4165684.1607707277@sss.pgh.pa.us
Whole thread Raw
Responses Re: Rethinking plpgsql's assignment implementation
Re: Rethinking plpgsql's assignment implementation
List pgsql-hackers
We've had complaints in the past about how plpgsql can't handle
assignments to fields in arrays of records [1], that is cases like

    arrayvar[n].field := something;

and also complaints about how plpgsql can't handle assignments
to array slices [2], ie

    arrayvar[m:n] := something;

As of commit c7aba7c14, we have another problem, namely that
plpgsql's subscripted assignment only works for regular arrays;
it won't work for other types that might define subscript
assignment handlers.

So I started to think about how to fix that, and eventually
decided that what we ought to do is nuke plpgsql's array-assignment
code altogether.  The core code already has support for everything
we want here in the context of field/element assignments in UPDATE
commands; if we could get plpgsql to make use of that infrastructure
instead of rolling its own, we'd be a lot better off.

The hard part of that is that the core parser will only generate
the structures we need (FieldStores and assignment SubscriptingRefs)
within UPDATE commands.  We could export the relevant functions
(particularly transformAssignmentIndirection); but that won't help
plpgsql very much, because it really wants to be able to run all this
stuff through SPI.  That means we have to have SQL syntax that can
generate an expression of that form.

That led me to think about introducing a new statement, say

    SET variable_name opt_indirection := a_expr

where opt_indirection is gram.y's symbol for "field selections and/or
subscripts".  The idea here is that a plpgsql statement like

    x[2].fld := something;

would be parsed using this new statement, producing an expression
that uses an assignment SubscriptingRef and a FieldStore operating
on a Param that gives the initial value of the array-of-composite
variable "x".  Then plpgsql would just evaluate this expression and
assign the result to x.  Problem solved.

This almost works as-is, modulo annoying parse conflicts against the
existing variants of SET.  However there's a nasty little detail
about what "variable_name" can be in plpgsql: it can be either one or
two identifiers, since there might be a block label involved, eg

    <<mylabel>> declare x int; begin mylabel.x := ...

Between that and the parse-conflict problem, I ended up
with this syntax:

    SET n: variable_name opt_indirection := a_expr

where "n" is an integer literal indicating how many dot-separated names
should be taken as the base variable name.  Another annoying point is
that plpgsql historically has allowed fun stuff like

    mycount := count(*) from my_table where ...;

that is, after the expression you can have all the rest of an ordinary
SELECT command.  That's not terribly hard to deal with, but it means
that this new statement has to have all of SELECT's other options too.

The other area that doesn't quite work without some kind of hack is
that plpgsql's casting rules for which types can be assigned to what
are far laxer than what the core parser thinks should be allowed in
UPDATE.  The cast has to happen within the assignment expression
for this to work at all, so plpgsql can't fix it by itself.  The
solution I adopted was just to invent a new CoercionContext value
COERCION_PLPGSQL, representing "use pl/pgsql's rules".  (Basically
what that means nowadays is to apply CoerceViaIO if assignment cast
lookup doesn't find a cast pathway.)

A happy side-effect of this approach is that it actually makes
some cases faster.  In particular I can measure speedups for
(a) assignments to subscripted variables and (b) cases where a
coercion must be performed to produce the result to be assigned.
I believe the reason for this is that the patch effectively
merges what had been separate expressions (subscripts or casts,
respectively) into the main result-producing expression.  This
eliminates a nontrivial amount of overhead for plancache validity
checking, execution startup, etc.

Another side-effect is that the report of the statement in error
cases might look different.  For example, in v13 a typo in a
subscript expression produces

regression=# do $$ declare x int[]; begin x[!2] = 43; end $$;
ERROR:  operator does not exist: ! integer
LINE 1: SELECT !2
               ^
HINT:  No operator matches the given name and argument type. You might need to add an explicit type cast.
QUERY:  SELECT !2
CONTEXT:  PL/pgSQL function inline_code_block line 1 at assignment

With this patch, you get

regression=# do $$ declare x int[]; begin x[!2] = 43; end $$;
ERROR:  operator does not exist: ! integer
LINE 1: SET 1: x[!2] = 43
                 ^
HINT:  No operator matches the given name and argument type. You might need to add an explicit type cast.
QUERY:  SET 1: x[!2] = 43
CONTEXT:  PL/pgSQL function inline_code_block line 1 at assignment

It seems like a clear improvement to me that the whole plpgsql statement
is now quoted, but the "SET n:" bit in front of it might confuse people,
especially if we don't document this new syntax (which I'm inclined not
to, since it's useless in straight SQL).  On the other hand, the
"SELECT" that you got with the old code was confusing to novices too.
Maybe something could be done to suppress those prefixes in error
reports?  Seems like a matter for another patch.  We could also use
some other prefix --- there's nothing particularly magic about the
word "SET" here, except that it already exists as a keyword --- but
I didn't think of anything I liked better.

This is still WIP: I've not added any new regression test cases
nor looked at the docs, and there's more cleanup needed in plpgsql.
But it passes check-world, so I thought I'd put it out for comments.

            regards, tom lane

[1] https://www.postgresql.org/message-id/A3691E98-CCA5-4DEB-B43C-92AD0437E09E%40mikatiming.de
[2] https://www.postgresql.org/message-id/1070.1451345954%40sss.pgh.pa.us

diff --git a/src/backend/commands/functioncmds.c b/src/backend/commands/functioncmds.c
index c3ce480c8f..edea27697e 100644
--- a/src/backend/commands/functioncmds.c
+++ b/src/backend/commands/functioncmds.c
@@ -1628,6 +1628,7 @@ CreateCast(CreateCastStmt *stmt)
         case COERCION_ASSIGNMENT:
             castcontext = COERCION_CODE_ASSIGNMENT;
             break;
+            /* COERCION_PLPGSQL is intentionally not covered here */
         case COERCION_EXPLICIT:
             castcontext = COERCION_CODE_EXPLICIT;
             break;
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index 70f8b718e0..67dab37a54 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -3199,6 +3199,29 @@ _copySetOperationStmt(const SetOperationStmt *from)
     return newnode;
 }

+static ExtensionSetStmt *
+_copyExtensionSetStmt(const ExtensionSetStmt *from)
+{
+    ExtensionSetStmt *newnode = makeNode(ExtensionSetStmt);
+
+    COPY_NODE_FIELD(target);
+    COPY_STRING_FIELD(name);
+    COPY_NODE_FIELD(indirection);
+    COPY_NODE_FIELD(val);
+    COPY_NODE_FIELD(fromClause);
+    COPY_NODE_FIELD(whereClause);
+    COPY_NODE_FIELD(groupClause);
+    COPY_NODE_FIELD(havingClause);
+    COPY_NODE_FIELD(windowClause);
+    COPY_NODE_FIELD(sortClause);
+    COPY_NODE_FIELD(limitOffset);
+    COPY_NODE_FIELD(limitCount);
+    COPY_SCALAR_FIELD(limitOption);
+    COPY_NODE_FIELD(lockingClause);
+
+    return newnode;
+}
+
 static AlterTableStmt *
 _copyAlterTableStmt(const AlterTableStmt *from)
 {
@@ -5220,6 +5243,9 @@ copyObjectImpl(const void *from)
         case T_SetOperationStmt:
             retval = _copySetOperationStmt(from);
             break;
+        case T_ExtensionSetStmt:
+            retval = _copyExtensionSetStmt(from);
+            break;
         case T_AlterTableStmt:
             retval = _copyAlterTableStmt(from);
             break;
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index 541e0e6b48..28884da7ec 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -1085,6 +1085,27 @@ _equalSetOperationStmt(const SetOperationStmt *a, const SetOperationStmt *b)
     return true;
 }

+static bool
+_equalExtensionSetStmt(const ExtensionSetStmt *a, const ExtensionSetStmt *b)
+{
+    COMPARE_NODE_FIELD(target);
+    COMPARE_STRING_FIELD(name);
+    COMPARE_NODE_FIELD(indirection);
+    COMPARE_NODE_FIELD(val);
+    COMPARE_NODE_FIELD(fromClause);
+    COMPARE_NODE_FIELD(whereClause);
+    COMPARE_NODE_FIELD(groupClause);
+    COMPARE_NODE_FIELD(havingClause);
+    COMPARE_NODE_FIELD(windowClause);
+    COMPARE_NODE_FIELD(sortClause);
+    COMPARE_NODE_FIELD(limitOffset);
+    COMPARE_NODE_FIELD(limitCount);
+    COMPARE_SCALAR_FIELD(limitOption);
+    COMPARE_NODE_FIELD(lockingClause);
+
+    return true;
+}
+
 static bool
 _equalAlterTableStmt(const AlterTableStmt *a, const AlterTableStmt *b)
 {
@@ -3275,6 +3296,9 @@ equal(const void *a, const void *b)
         case T_SetOperationStmt:
             retval = _equalSetOperationStmt(a, b);
             break;
+        case T_ExtensionSetStmt:
+            retval = _equalExtensionSetStmt(a, b);
+            break;
         case T_AlterTableStmt:
             retval = _equalAlterTableStmt(a, b);
             break;
diff --git a/src/backend/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c
index 963f71e99d..166fbeddb2 100644
--- a/src/backend/nodes/nodeFuncs.c
+++ b/src/backend/nodes/nodeFuncs.c
@@ -3669,6 +3669,36 @@ raw_expression_tree_walker(Node *node,
                     return true;
             }
             break;
+        case T_ExtensionSetStmt:
+            {
+                ExtensionSetStmt *stmt = (ExtensionSetStmt *) node;
+
+                if (walker(stmt->target, context))
+                    return true;
+                if (walker(stmt->indirection, context))
+                    return true;
+                if (walker(stmt->val, context))
+                    return true;
+                if (walker(stmt->fromClause, context))
+                    return true;
+                if (walker(stmt->whereClause, context))
+                    return true;
+                if (walker(stmt->groupClause, context))
+                    return true;
+                if (walker(stmt->havingClause, context))
+                    return true;
+                if (walker(stmt->windowClause, context))
+                    return true;
+                if (walker(stmt->sortClause, context))
+                    return true;
+                if (walker(stmt->limitOffset, context))
+                    return true;
+                if (walker(stmt->limitCount, context))
+                    return true;
+                if (walker(stmt->lockingClause, context))
+                    return true;
+            }
+            break;
         case T_A_Expr:
             {
                 A_Expr       *expr = (A_Expr *) node;
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index d78b16ed1d..ef85556fc0 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -2775,6 +2775,27 @@ _outSelectStmt(StringInfo str, const SelectStmt *node)
     WRITE_NODE_FIELD(rarg);
 }

+static void
+_outExtensionSetStmt(StringInfo str, const ExtensionSetStmt *node)
+{
+    WRITE_NODE_TYPE("EXTENSIONSET");
+
+    WRITE_NODE_FIELD(target);
+    WRITE_STRING_FIELD(name);
+    WRITE_NODE_FIELD(indirection);
+    WRITE_NODE_FIELD(val);
+    WRITE_NODE_FIELD(fromClause);
+    WRITE_NODE_FIELD(whereClause);
+    WRITE_NODE_FIELD(groupClause);
+    WRITE_NODE_FIELD(havingClause);
+    WRITE_NODE_FIELD(windowClause);
+    WRITE_NODE_FIELD(sortClause);
+    WRITE_NODE_FIELD(limitOffset);
+    WRITE_NODE_FIELD(limitCount);
+    WRITE_ENUM_FIELD(limitOption, LimitOption);
+    WRITE_NODE_FIELD(lockingClause);
+}
+
 static void
 _outFuncCall(StringInfo str, const FuncCall *node)
 {
@@ -4211,6 +4232,9 @@ outNode(StringInfo str, const void *obj)
             case T_SelectStmt:
                 _outSelectStmt(str, obj);
                 break;
+            case T_ExtensionSetStmt:
+                _outExtensionSetStmt(str, obj);
+                break;
             case T_ColumnDef:
                 _outColumnDef(str, obj);
                 break;
diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c
index 084e00f73d..9ef1ed4d4c 100644
--- a/src/backend/parser/analyze.c
+++ b/src/backend/parser/analyze.c
@@ -42,8 +42,10 @@
 #include "parser/parse_param.h"
 #include "parser/parse_relation.h"
 #include "parser/parse_target.h"
+#include "parser/parse_type.h"
 #include "parser/parsetree.h"
 #include "rewrite/rewriteManip.h"
+#include "utils/builtins.h"
 #include "utils/rel.h"


@@ -70,6 +72,8 @@ static Query *transformUpdateStmt(ParseState *pstate, UpdateStmt *stmt);
 static List *transformReturningList(ParseState *pstate, List *returningList);
 static List *transformUpdateTargetList(ParseState *pstate,
                                        List *targetList);
+static Query *transformExtensionSetStmt(ParseState *pstate,
+                                        ExtensionSetStmt *stmt);
 static Query *transformDeclareCursorStmt(ParseState *pstate,
                                          DeclareCursorStmt *stmt);
 static Query *transformExplainStmt(ParseState *pstate,
@@ -304,6 +308,11 @@ transformStmt(ParseState *pstate, Node *parseTree)
             }
             break;

+        case T_ExtensionSetStmt:
+            result = transformExtensionSetStmt(pstate,
+                                               (ExtensionSetStmt *) parseTree);
+            break;
+
             /*
              * Special cases
              */
@@ -367,6 +376,7 @@ analyze_requires_snapshot(RawStmt *parseTree)
         case T_DeleteStmt:
         case T_UpdateStmt:
         case T_SelectStmt:
+        case T_ExtensionSetStmt:
             result = true;
             break;

@@ -2393,6 +2403,209 @@ transformReturningList(ParseState *pstate, List *returningList)
 }


+/*
+ * transformExtensionSetStmt -
+ *      transform an Extension SET Statement,
+ *      SET n: name opt_indirection := a_expr ...
+ *
+ * This undocumented syntax allows PL/pgSQL to make use of the regular parser
+ * for assignment statements.  Everything after the colon is taken verbatim
+ * from the PL/pgSQL assignment statement, while the integer "n" says how many
+ * dotted names comprise the target ColumnRef.  (That part is handled by
+ * gram.y, so we just see a ColumnRef and a suitably-truncated indirection
+ * list here.)  If there is no opt_indirection, the transformed statement
+ * looks like "SELECT a_expr ...", except the expression has been cast to
+ * the type of the target.  With indirection, it's still a SELECT, but the
+ * expression will incorporate FieldStore and/or assignment SubscriptingRef
+ * nodes to compute a new value for a container-type variable represented by
+ * the target.  The expression references the target as the container source.
+ */
+static Query *
+transformExtensionSetStmt(ParseState *pstate,
+                          ExtensionSetStmt *stmt)
+{
+    Query       *qry = makeNode(Query);
+    Node       *target;
+    Oid            targettype;
+    int32        targettypmod;
+    Oid            targetcollation;
+    TargetEntry *tle;
+    Oid            type_id;
+    Node       *qual;
+    ListCell   *l;
+
+    /*
+     * Transform the target reference.  Typically we will get back a Param
+     * node, but there's no reason to be too picky about its type.
+     */
+    target = transformExpr(pstate, (Node *) stmt->target,
+                           EXPR_KIND_UPDATE_TARGET);
+    targettype = exprType(target);
+    targettypmod = exprTypmod(target);
+    targetcollation = exprCollation(target);
+
+    /*
+     * The rest mostly matches transformSelectStmt, except that we needn't
+     * consider WITH or DISTINCT, and we build a targetlist our own way.
+     */
+
+    qry->commandType = CMD_SELECT;
+    pstate->p_is_insert = false;
+
+    /* make FOR UPDATE/FOR SHARE info available to addRangeTableEntry */
+    pstate->p_locking_clause = stmt->lockingClause;
+
+    /* make WINDOW info available for window functions, too */
+    pstate->p_windowdefs = stmt->windowClause;
+
+    /* process the FROM clause */
+    transformFromClause(pstate, stmt->fromClause);
+
+    /* Transform the assignment source (cf. transformUpdateTargetList) */
+    tle = transformTargetEntry(pstate,
+                               stmt->val,
+                               NULL,
+                               EXPR_KIND_SELECT_TARGET,
+                               NULL,
+                               false);
+
+    /*
+     * This next bit is similar to transformAssignedExpr; the key difference
+     * is we use COERCION_PLPGSQL not COERCION_ASSIGNMENT.
+     */
+    type_id = exprType((Node *) tle->expr);
+
+    pstate->p_expr_kind = EXPR_KIND_UPDATE_TARGET;
+
+    if (stmt->indirection)
+    {
+        tle->expr = (Expr *)
+            transformAssignmentIndirection(pstate,
+                                           target,
+                                           stmt->name,
+                                           false,
+                                           targettype,
+                                           targettypmod,
+                                           targetcollation,
+                                           COERCION_PLPGSQL,
+                                           stmt->indirection,
+                                           list_head(stmt->indirection),
+                                           (Node *) tle->expr,
+                                           exprLocation(target));
+    }
+    else if (targettype != type_id &&
+             (targettype == RECORDOID || ISCOMPLEX(targettype)) &&
+             (type_id == RECORDOID || ISCOMPLEX(type_id)))
+    {
+        /*
+         * Hack: do not let coerce_to_target_type() deal with inconsistent
+         * composite types.  Just pass the expression result through as-is,
+         * and let the PL/pgSQL executor do the conversion its way.  This is
+         * rather bogus, but it's needed for backwards compatibility.
+         */
+    }
+    else
+    {
+        /*
+         * For normal non-qualified target column, do type checking and
+         * coercion.
+         */
+        Node       *orig_expr = (Node *) tle->expr;
+
+        tle->expr = (Expr *)
+            coerce_to_target_type(pstate,
+                                  orig_expr, type_id,
+                                  targettype, targettypmod,
+                                  COERCION_PLPGSQL,
+                                  COERCE_IMPLICIT_CAST,
+                                  -1);
+        /* With COERCION_PLPGSQL, this error is probably unreachable */
+        if (tle->expr == NULL)
+            ereport(ERROR,
+                    (errcode(ERRCODE_DATATYPE_MISMATCH),
+                     errmsg("variable \"%s\" is of type %s"
+                            " but expression is of type %s",
+                            stmt->name,
+                            format_type_be(targettype),
+                            format_type_be(type_id)),
+                     errhint("You will need to rewrite or cast the expression."),
+                     parser_errposition(pstate, exprLocation(orig_expr))));
+    }
+
+    pstate->p_expr_kind = EXPR_KIND_NONE;
+
+    qry->targetList = list_make1(tle);
+
+    /* transform WHERE */
+    qual = transformWhereClause(pstate, stmt->whereClause,
+                                EXPR_KIND_WHERE, "WHERE");
+
+    /* initial processing of HAVING clause is much like WHERE clause */
+    qry->havingQual = transformWhereClause(pstate, stmt->havingClause,
+                                           EXPR_KIND_HAVING, "HAVING");
+
+    /*
+     * Transform sorting/grouping stuff.  Do ORDER BY first because both
+     * transformGroupClause and transformDistinctClause need the results. Note
+     * that these functions can also change the targetList, so it's passed to
+     * them by reference.
+     */
+    qry->sortClause = transformSortClause(pstate,
+                                          stmt->sortClause,
+                                          &qry->targetList,
+                                          EXPR_KIND_ORDER_BY,
+                                          false /* allow SQL92 rules */ );
+
+    qry->groupClause = transformGroupClause(pstate,
+                                            stmt->groupClause,
+                                            &qry->groupingSets,
+                                            &qry->targetList,
+                                            qry->sortClause,
+                                            EXPR_KIND_GROUP_BY,
+                                            false /* allow SQL92 rules */ );
+
+    /* No DISTINCT clause */
+    qry->distinctClause = NIL;
+    qry->hasDistinctOn = false;
+
+    /* transform LIMIT */
+    qry->limitOffset = transformLimitClause(pstate, stmt->limitOffset,
+                                            EXPR_KIND_OFFSET, "OFFSET",
+                                            stmt->limitOption);
+    qry->limitCount = transformLimitClause(pstate, stmt->limitCount,
+                                           EXPR_KIND_LIMIT, "LIMIT",
+                                           stmt->limitOption);
+    qry->limitOption = stmt->limitOption;
+
+    /* transform window clauses after we have seen all window functions */
+    qry->windowClause = transformWindowDefinitions(pstate,
+                                                   pstate->p_windowdefs,
+                                                   &qry->targetList);
+
+    qry->rtable = pstate->p_rtable;
+    qry->jointree = makeFromExpr(pstate->p_joinlist, qual);
+
+    qry->hasSubLinks = pstate->p_hasSubLinks;
+    qry->hasWindowFuncs = pstate->p_hasWindowFuncs;
+    qry->hasTargetSRFs = pstate->p_hasTargetSRFs;
+    qry->hasAggs = pstate->p_hasAggs;
+
+    foreach(l, stmt->lockingClause)
+    {
+        transformLockingClause(pstate, qry,
+                               (LockingClause *) lfirst(l), false);
+    }
+
+    assign_query_collations(pstate, qry);
+
+    /* this must be done after collations, for reliable comparison of exprs */
+    if (pstate->p_hasAggs || qry->groupClause || qry->groupingSets || qry->havingQual)
+        parseCheckAggregates(pstate, qry);
+
+    return qry;
+}
+
+
 /*
  * transformDeclareCursorStmt -
  *    transform a DECLARE CURSOR Statement
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 8f341ac006..5279d23b7b 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -275,7 +275,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
         DropCastStmt DropRoleStmt
         DropdbStmt DropTableSpaceStmt
         DropTransformStmt
-        DropUserMappingStmt ExplainStmt FetchStmt
+        DropUserMappingStmt ExplainStmt ExtensionSetStmt FetchStmt
         GrantStmt GrantRoleStmt ImportForeignSchemaStmt IndexStmt InsertStmt
         ListenStmt LoadStmt LockStmt NotifyStmt ExplainableStmt PreparableStmt
         CreateFunctionStmt AlterFunctionStmt ReindexStmt RemoveAggrStmt
@@ -535,7 +535,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
 %type <str>        ColId ColLabel BareColLabel
 %type <str>        NonReservedWord NonReservedWord_or_Sconst
 %type <str>        var_name type_function_name param_name
-%type <str>        createdb_opt_name
+%type <str>        createdb_opt_name extension_set_target
 %type <node>    var_value zone_value
 %type <rolespec> auth_ident RoleSpec opt_granted_by

@@ -916,6 +916,7 @@ stmt :
             | DropdbStmt
             | ExecuteStmt
             | ExplainStmt
+            | ExtensionSetStmt
             | FetchStmt
             | GrantStmt
             | GrantRoleStmt
@@ -11041,6 +11042,73 @@ set_target_list:
         ;


+/*****************************************************************************
+ *
+ *        QUERY:
+ *                SET n: name opt_indirection := a_expr ...
+ *
+ * This undocumented syntax allows PL/pgSQL to make use of the regular parser
+ * for its assignment statements.  The stuff after the a_expr should match
+ * what can appear after the targetlist in SELECT.
+ *****************************************************************************/
+
+ExtensionSetStmt: SET Iconst ':' extension_set_target opt_indirection
+            extension_set_assignment a_expr
+            from_clause where_clause
+            group_clause having_clause window_clause
+            opt_sort_clause opt_select_limit opt_for_locking_clause
+                {
+                    ExtensionSetStmt *n = makeNode(ExtensionSetStmt);
+                    ColumnRef  *cref = makeNode(ColumnRef);
+                    int            nnames = $2;
+                    List       *indirection = $5;
+
+                    cref->fields = list_make1(makeString($4));
+                    cref->location = @4;
+                    while (--nnames > 0 && indirection != NIL)
+                    {
+                        Node   *ind = (Node *) linitial(indirection);
+
+                        if (!IsA(ind, String))
+                            elog(ERROR, "invalid name count in extension SET");
+                        cref->fields = lappend(cref->fields, ind);
+                        indirection = list_delete_first(indirection);
+                    }
+                    n->target = cref;
+                    n->name = $4;
+                    n->indirection = check_indirection(indirection, yyscanner);
+                    n->val = $7;
+                    n->fromClause = $8;
+                    n->whereClause = $9;
+                    n->groupClause = $10;
+                    n->havingClause = $11;
+                    n->windowClause = $12;
+                    n->sortClause = $13;
+                    if ($14)
+                    {
+                        n->limitOffset = $14->limitOffset;
+                        n->limitCount = $14->limitCount;
+                        if (!n->sortClause &&
+                            $14->limitOption == LIMIT_OPTION_WITH_TIES)
+                            ereport(ERROR,
+                                    (errcode(ERRCODE_SYNTAX_ERROR),
+                                     errmsg("WITH TIES cannot be specified without ORDER BY clause")));
+                        n->limitOption = $14->limitOption;
+                    }
+                    n->lockingClause = $15;
+                    $$ = (Node *) n;
+                }
+        ;
+
+extension_set_target: ColId                        { $$ = $1; }
+            | PARAM                                { $$ = psprintf("$%d", $1); }
+        ;
+
+extension_set_assignment: COLON_EQUALS
+            | '='
+        ;
+
+
 /*****************************************************************************
  *
  *        QUERY:
diff --git a/src/backend/parser/parse_coerce.c b/src/backend/parser/parse_coerce.c
index da6c3ae4b5..462e2af74e 100644
--- a/src/backend/parser/parse_coerce.c
+++ b/src/backend/parser/parse_coerce.c
@@ -2815,6 +2815,14 @@ find_coercion_pathway(Oid targetTypeId, Oid sourceTypeId,
         }
     }

+    /*
+     * When parsing PL/pgSQL assignments, allow an I/O cast to be used
+     * whenever no normal coercion is available.
+     */
+    if (result == COERCION_PATH_NONE &&
+        ccontext == COERCION_PLPGSQL)
+        result = COERCION_PATH_COERCEVIAIO;
+
     return result;
 }

diff --git a/src/backend/parser/parse_target.c b/src/backend/parser/parse_target.c
index 3dda8e2847..55a9a808da 100644
--- a/src/backend/parser/parse_target.c
+++ b/src/backend/parser/parse_target.c
@@ -34,23 +34,13 @@

 static void markTargetListOrigin(ParseState *pstate, TargetEntry *tle,
                                  Var *var, int levelsup);
-static Node *transformAssignmentIndirection(ParseState *pstate,
-                                            Node *basenode,
-                                            const char *targetName,
-                                            bool targetIsSubscripting,
-                                            Oid targetTypeId,
-                                            int32 targetTypMod,
-                                            Oid targetCollation,
-                                            List *indirection,
-                                            ListCell *indirection_cell,
-                                            Node *rhs,
-                                            int location);
 static Node *transformAssignmentSubscripts(ParseState *pstate,
                                            Node *basenode,
                                            const char *targetName,
                                            Oid targetTypeId,
                                            int32 targetTypMod,
                                            Oid targetCollation,
+                                           CoercionContext ccontext,
                                            List *subscripts,
                                            bool isSlice,
                                            List *indirection,
@@ -558,6 +548,7 @@ transformAssignedExpr(ParseState *pstate,
                                            attrtype,
                                            attrtypmod,
                                            attrcollation,
+                                           COERCION_ASSIGNMENT,
                                            indirection,
                                            list_head(indirection),
                                            (Node *) expr,
@@ -648,9 +639,9 @@ updateTargetListEntry(ParseState *pstate,
  * operation.
  *
  * In the initial call, basenode is a Var for the target column in UPDATE,
- * or a null Const of the target's type in INSERT.  In recursive calls,
- * basenode is NULL, indicating that a substitute node should be consed up if
- * needed.
+ * or a null Const of the target's type in INSERT, or a Param for the target
+ * variable in extension SET operations.  In recursive calls, basenode is
+ * NULL, indicating that a substitute node should be consed up if needed.
  *
  * targetName is the name of the field or subfield we're assigning to, and
  * targetIsSubscripting is true if we're subscripting it.  These are just for
@@ -660,6 +651,10 @@ updateTargetListEntry(ParseState *pstate,
  * collation of the object to be assigned to (initially the target column,
  * later some subobject).
  *
+ * ccontext is the coercion level to use while coercing the "rhs".  For
+ * normal statements it'll be COERCION_ASSIGNMENT, but extension SET uses
+ * a special value.
+ *
  * indirection is the list of indirection nodes, and indirection_cell is the
  * start of the sublist remaining to process.  When it's NULL, we're done
  * recursing and can just coerce and return the RHS.
@@ -672,7 +667,7 @@ updateTargetListEntry(ParseState *pstate,
  * might want to decorate indirection cells with their own location info,
  * in which case the location argument could probably be dropped.)
  */
-static Node *
+Node *
 transformAssignmentIndirection(ParseState *pstate,
                                Node *basenode,
                                const char *targetName,
@@ -680,6 +675,7 @@ transformAssignmentIndirection(ParseState *pstate,
                                Oid targetTypeId,
                                int32 targetTypMod,
                                Oid targetCollation,
+                               CoercionContext ccontext,
                                List *indirection,
                                ListCell *indirection_cell,
                                Node *rhs,
@@ -752,6 +748,7 @@ transformAssignmentIndirection(ParseState *pstate,
                                                      targetTypeId,
                                                      targetTypMod,
                                                      targetCollation,
+                                                     ccontext,
                                                      subscripts,
                                                      isSlice,
                                                      indirection,
@@ -804,6 +801,7 @@ transformAssignmentIndirection(ParseState *pstate,
                                                  fieldTypeId,
                                                  fieldTypMod,
                                                  fieldCollation,
+                                                 ccontext,
                                                  indirection,
                                                  lnext(indirection, i),
                                                  rhs,
@@ -840,6 +838,7 @@ transformAssignmentIndirection(ParseState *pstate,
                                              targetTypeId,
                                              targetTypMod,
                                              targetCollation,
+                                             ccontext,
                                              subscripts,
                                              isSlice,
                                              indirection,
@@ -853,7 +852,7 @@ transformAssignmentIndirection(ParseState *pstate,
     result = coerce_to_target_type(pstate,
                                    rhs, exprType(rhs),
                                    targetTypeId, targetTypMod,
-                                   COERCION_ASSIGNMENT,
+                                   ccontext,
                                    COERCE_IMPLICIT_CAST,
                                    -1);
     if (result == NULL)
@@ -893,6 +892,7 @@ transformAssignmentSubscripts(ParseState *pstate,
                               Oid targetTypeId,
                               int32 targetTypMod,
                               Oid targetCollation,
+                              CoercionContext ccontext,
                               List *subscripts,
                               bool isSlice,
                               List *indirection,
@@ -946,6 +946,7 @@ transformAssignmentSubscripts(ParseState *pstate,
                                          typeNeeded,
                                          typmodNeeded,
                                          collationNeeded,
+                                         ccontext,
                                          indirection,
                                          next_indirection,
                                          rhs,
@@ -969,7 +970,7 @@ transformAssignmentSubscripts(ParseState *pstate,
         result = coerce_to_target_type(pstate,
                                        result, resulttype,
                                        targetTypeId, targetTypMod,
-                                       COERCION_ASSIGNMENT,
+                                       ccontext,
                                        COERCE_IMPLICIT_CAST,
                                        -1);
         /* can fail if we had int2vector/oidvector, but not for true domains */
diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c
index a42ead7d69..a2e3b332c7 100644
--- a/src/backend/tcop/utility.c
+++ b/src/backend/tcop/utility.c
@@ -2313,6 +2313,10 @@ CreateCommandTag(Node *parsetree)
             tag = CMDTAG_SELECT;
             break;

+        case T_ExtensionSetStmt:
+            tag = CMDTAG_SELECT;
+            break;
+
             /* utility statements --- same whether raw or cooked */
         case T_TransactionStmt:
             {
@@ -3181,6 +3185,10 @@ GetCommandLogLevel(Node *parsetree)
                 lev = LOGSTMT_ALL;
             break;

+        case T_ExtensionSetStmt:
+            lev = LOGSTMT_ALL;
+            break;
+
             /* utility statements --- same whether raw or cooked */
         case T_TransactionStmt:
             lev = LOGSTMT_ALL;
diff --git a/src/include/nodes/nodes.h b/src/include/nodes/nodes.h
index 3684f87a88..273b8181e4 100644
--- a/src/include/nodes/nodes.h
+++ b/src/include/nodes/nodes.h
@@ -314,6 +314,7 @@ typedef enum NodeTag
     T_DeleteStmt,
     T_UpdateStmt,
     T_SelectStmt,
+    T_ExtensionSetStmt,
     T_AlterTableStmt,
     T_AlterTableCmd,
     T_AlterDomainStmt,
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 48a79a7657..4d073f862d 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -1675,6 +1675,41 @@ typedef struct SetOperationStmt
 } SetOperationStmt;


+/* ----------------------
+ *        Extension SET Statement
+ *
+ * Like SelectStmt, this is transformed into a SELECT Query.
+ * However, the targetlist of the result looks more like an UPDATE.
+ * ----------------------
+ */
+typedef struct ExtensionSetStmt
+{
+    NodeTag        type;
+
+    /* These fields represent "target opt_indirection := value" */
+    ColumnRef  *target;            /* ColumnRef for the target variable */
+    char       *name;            /* initial column name */
+    List       *indirection;    /* subscripts and field names, or NIL */
+    Node       *val;            /* the value expression to assign */
+
+    /*
+     * Historically, PL/pgSQL has allowed an assignment to have any decoration
+     * that could occur after the targetlist of SELECT, so we preserve that
+     * capability.
+     */
+    List       *fromClause;        /* the FROM clause */
+    Node       *whereClause;    /* WHERE qualification */
+    List       *groupClause;    /* GROUP BY clauses */
+    Node       *havingClause;    /* HAVING conditional-expression */
+    List       *windowClause;    /* WINDOW window_name AS (...), ... */
+    List       *sortClause;        /* sort clause (a list of SortBy's) */
+    Node       *limitOffset;    /* # of result tuples to skip */
+    Node       *limitCount;        /* # of result tuples to return */
+    LimitOption limitOption;    /* limit type */
+    List       *lockingClause;    /* FOR UPDATE (list of LockingClause's) */
+} ExtensionSetStmt;
+
+
 /*****************************************************************************
  *        Other Statements (no optimizations required)
  *
diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h
index dd85908fe2..71a58804dc 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -457,6 +457,7 @@ typedef enum CoercionContext
 {
     COERCION_IMPLICIT,            /* coercion in context of expression */
     COERCION_ASSIGNMENT,        /* coercion in context of assignment */
+    COERCION_PLPGSQL,            /* if no assignment cast, use CoerceViaIO */
     COERCION_EXPLICIT            /* explicit cast operation */
 } CoercionContext;

diff --git a/src/include/parser/parse_target.h b/src/include/parser/parse_target.h
index 7039df29cb..6f5741f824 100644
--- a/src/include/parser/parse_target.h
+++ b/src/include/parser/parse_target.h
@@ -36,6 +36,18 @@ extern void updateTargetListEntry(ParseState *pstate, TargetEntry *tle,
                                   char *colname, int attrno,
                                   List *indirection,
                                   int location);
+extern Node *transformAssignmentIndirection(ParseState *pstate,
+                                            Node *basenode,
+                                            const char *targetName,
+                                            bool targetIsSubscripting,
+                                            Oid targetTypeId,
+                                            int32 targetTypMod,
+                                            Oid targetCollation,
+                                            CoercionContext ccontext,
+                                            List *indirection,
+                                            ListCell *indirection_cell,
+                                            Node *rhs,
+                                            int location);
 extern List *checkInsertTargets(ParseState *pstate, List *cols,
                                 List **attrnos);
 extern TupleDesc expandRecordVariable(ParseState *pstate, Var *var,
diff --git a/src/pl/plpgsql/src/pl_exec.c b/src/pl/plpgsql/src/pl_exec.c
index ccbc50fc45..22fe037f90 100644
--- a/src/pl/plpgsql/src/pl_exec.c
+++ b/src/pl/plpgsql/src/pl_exec.c
@@ -8005,10 +8005,14 @@ get_cast_hashentry(PLpgSQL_execstate *estate,
         placeholder->collation = get_typcollation(srctype);

         /*
-         * Apply coercion.  We use ASSIGNMENT coercion because that's the
-         * closest match to plpgsql's historical behavior; in particular,
-         * EXPLICIT coercion would allow silent truncation to a destination
-         * varchar/bpchar's length, which we do not want.
+         * Apply coercion.  We use the special coercion context
+         * COERCION_PLPGSQL to match plpgsql's historical behavior, namely
+         * that any cast not available at ASSIGNMENT level will be implemented
+         * as an I/O coercion.  (It's somewhat dubious that we prefer I/O
+         * coercion over cast pathways that exist at EXPLICIT level.  Changing
+         * that would cause assorted minor behavioral differences though, and
+         * a user who wants the explicit-cast behavior can always write an
+         * explicit cast.)
          *
          * If source type is UNKNOWN, coerce_to_target_type will fail (it only
          * expects to see that for Const input nodes), so don't call it; we'll
@@ -8021,7 +8025,7 @@ get_cast_hashentry(PLpgSQL_execstate *estate,
             cast_expr = coerce_to_target_type(NULL,
                                               (Node *) placeholder, srctype,
                                               dsttype, dsttypmod,
-                                              COERCION_ASSIGNMENT,
+                                              COERCION_PLPGSQL,
                                               COERCE_IMPLICIT_CAST,
                                               -1);

@@ -8029,7 +8033,8 @@ get_cast_hashentry(PLpgSQL_execstate *estate,
          * If there's no cast path according to the parser, fall back to using
          * an I/O coercion; this is semantically dubious but matches plpgsql's
          * historical behavior.  We would need something of the sort for
-         * UNKNOWN literals in any case.
+         * UNKNOWN literals in any case.  (This is probably now only reachable
+         * in the case where srctype is UNKNOWN/RECORD.)
          */
         if (cast_expr == NULL)
         {
@@ -8338,7 +8343,8 @@ exec_check_rw_parameter(PLpgSQL_expr *expr, int target_dno)
         return;

     /*
-     * Top level of expression must be a simple FuncExpr or OpExpr.
+     * Top level of expression must be a simple FuncExpr, OpExpr, or
+     * SubscriptingRef.
      */
     if (IsA(expr->expr_simple_expr, FuncExpr))
     {
@@ -8354,6 +8360,33 @@ exec_check_rw_parameter(PLpgSQL_expr *expr, int target_dno)
         funcid = opexpr->opfuncid;
         fargs = opexpr->args;
     }
+    else if (IsA(expr->expr_simple_expr, SubscriptingRef))
+    {
+        SubscriptingRef *sbsref = (SubscriptingRef *) expr->expr_simple_expr;
+
+        /* We only trust standard varlena arrays to be safe */
+        if (get_typsubscript(sbsref->refcontainertype, NULL) !=
+            F_ARRAY_SUBSCRIPT_HANDLER)
+            return;
+
+        /* refexpr can be a simple Param, otherwise must not contain target */
+        if (!(sbsref->refexpr && IsA(sbsref->refexpr, Param)) &&
+            contains_target_param((Node *) sbsref->refexpr, &target_dno))
+            return;
+
+        /* the other subexpressions must not contain target */
+        if (contains_target_param((Node *) sbsref->refupperindexpr,
+                                  &target_dno) ||
+            contains_target_param((Node *) sbsref->reflowerindexpr,
+                                  &target_dno) ||
+            contains_target_param((Node *) sbsref->refassgnexpr,
+                                  &target_dno))
+            return;
+
+        /* OK, we can pass target as a read-write parameter */
+        expr->rwparam = target_dno;
+        return;
+    }
     else
         return;

diff --git a/src/pl/plpgsql/src/pl_gram.y b/src/pl/plpgsql/src/pl_gram.y
index 8227bf0449..561b0dec59 100644
--- a/src/pl/plpgsql/src/pl_gram.y
+++ b/src/pl/plpgsql/src/pl_gram.y
@@ -949,16 +949,21 @@ stmt_call        : K_CALL
                     }
                 ;

-stmt_assign        : assign_var assign_operator expr_until_semi
+stmt_assign        : T_DATUM
                     {
                         PLpgSQL_stmt_assign *new;
+                        int nnames = $1.ident ? 1 : list_length($1.idents);

+                        check_assignable($1.datum, @1);
                         new = palloc0(sizeof(PLpgSQL_stmt_assign));
                         new->cmd_type = PLPGSQL_STMT_ASSIGN;
                         new->lineno   = plpgsql_location_to_lineno(@1);
                         new->stmtid = ++plpgsql_curr_compile->nstatements;
-                        new->varno = $1->dno;
-                        new->expr  = $3;
+                        new->varno = $1.datum->dno;
+                        /* Push back the head name to include it in the stmt */
+                        plpgsql_push_back_token(T_DATUM);
+                        new->expr = read_sql_stmt(psprintf("SET %d: ",
+                                                           nnames));

                         $$ = (PLpgSQL_stmt *)new;
                     }

pgsql-hackers by date:

Previous
From: Peter Eisentraut
Date:
Subject: Clean up ancient test style
Next
From: Tom Lane
Date:
Subject: Re: Clean up ancient test style