Re: plpgsql raise - parameters can be expressions - Mailing list pgsql-patches

Pavel Stehule wrote:
>     I did trivial patch which eliminate limit raise command.

Looks pretty good. Attached is a cleaned-up version that I'll apply to
HEAD tomorrow, barring any objections.

BTW, one regression is that an undefined variable in the RAISE list is
no longer a compile-time error:

create function foo() returns void as '
begin
raise notice ''hello, world: %'', baz;
end;' language plpgsql;

neilc=#  select foo();
ERROR:  column "baz" does not exist

I don't see an easy way to get around this, though, and it's not too
concerning. Amusingly it does completely break the SQLSTATE and SQLERRM
tests we added a few days ago :)

BTW, another easy improvement in this area is changing the RAISE format
string to allow it to be an expression, rather than only a string literal.

-Neil
Index: doc/src/sgml/plpgsql.sgml
===================================================================
RCS file: /var/lib/cvs/pgsql/doc/src/sgml/plpgsql.sgml,v
retrieving revision 1.71
diff -c -r1.71 plpgsql.sgml
*** doc/src/sgml/plpgsql.sgml    10 Jun 2005 16:23:09 -0000    1.71
--- doc/src/sgml/plpgsql.sgml    13 Jun 2005 05:38:55 -0000
***************
*** 2533,2541 ****
     <para>
      Inside the format string, <literal>%</literal> is replaced by the
      next optional argument's string representation. Write
!     <literal>%%</literal> to emit a literal <literal>%</literal>. Note
!     that the optional arguments must presently be simple variables,
!     not expressions, and the format must be a simple string literal.
     </para>

     <!--
--- 2533,2541 ----
     <para>
      Inside the format string, <literal>%</literal> is replaced by the
      next optional argument's string representation. Write
!     <literal>%%</literal> to emit a literal <literal>%</literal>.
!     Arguments can be simple variables or expressions,
!     and the format must be a simple string literal.
     </para>

     <!--
Index: src/pl/plpgsql/src/gram.y
===================================================================
RCS file: /var/lib/cvs/pgsql/src/pl/plpgsql/src/gram.y,v
retrieving revision 1.75
diff -c -r1.75 gram.y
*** src/pl/plpgsql/src/gram.y    10 Jun 2005 16:23:11 -0000    1.75
--- src/pl/plpgsql/src/gram.y    13 Jun 2005 05:44:02 -0000
***************
*** 135,142 ****
  %type <exception>    proc_exception
  %type <condition>    proc_conditions

! %type <list>    raise_params
! %type <ival>    raise_level raise_param
  %type <str>        raise_msg

  %type <list>    getdiag_list
--- 135,142 ----
  %type <exception>    proc_exception
  %type <condition>    proc_conditions

!
! %type <ival>    raise_level
  %type <str>        raise_msg

  %type <list>    getdiag_list
***************
*** 1157,1163 ****
                      }
                  ;

! stmt_raise        : K_RAISE lno raise_level raise_msg raise_params ';'
                      {
                          PLpgSQL_stmt_raise        *new;

--- 1157,1163 ----
                      }
                  ;

! stmt_raise        : K_RAISE lno raise_level raise_msg
                      {
                          PLpgSQL_stmt_raise        *new;

***************
*** 1167,1187 ****
                          new->lineno        = $2;
                          new->elog_level = $3;
                          new->message    = $4;
-                         new->params        = $5;

!                         $$ = (PLpgSQL_stmt *)new;
!                     }
!                 | K_RAISE lno raise_level raise_msg ';'
!                     {
!                         PLpgSQL_stmt_raise        *new;

!                         new = palloc(sizeof(PLpgSQL_stmt_raise));

!                         new->cmd_type    = PLPGSQL_STMT_RAISE;
!                         new->lineno        = $2;
!                         new->elog_level = $3;
!                         new->message    = $4;
!                         new->params        = NIL;

                          $$ = (PLpgSQL_stmt *)new;
                      }
--- 1167,1186 ----
                          new->lineno        = $2;
                          new->elog_level = $3;
                          new->message    = $4;

!                         switch (yylex())
!                         {
!                             case ';':
!                                 new->params_expr = NULL;
!                                 break;

!                             case ',':
!                                 new->params_expr = plpgsql_read_expression(';', ";");
!                                 break;

!                             default:
!                                 yyerror("syntax error");
!                         }

                          $$ = (PLpgSQL_stmt *)new;
                      }
***************
*** 1219,1240 ****
                      }
                  ;

- raise_params    : raise_params raise_param
-                     {
-                         $$ = lappend_int($1, $2);
-                     }
-                 | raise_param
-                     {
-                         $$ = list_make1_int($1);
-                     }
-                 ;
-
- raise_param        : ',' T_SCALAR
-                     {
-                         $$ = yylval.scalar->dno;
-                     }
-                 ;
-
  loop_body        : proc_sect K_END K_LOOP ';'
                      { $$ = $1; }
                  ;
--- 1218,1223 ----
Index: src/pl/plpgsql/src/pl_exec.c
===================================================================
RCS file: /var/lib/cvs/pgsql/src/pl/plpgsql/src/pl_exec.c,v
retrieving revision 1.143
diff -c -r1.143 pl_exec.c
*** src/pl/plpgsql/src/pl_exec.c    10 Jun 2005 16:23:11 -0000    1.143
--- src/pl/plpgsql/src/pl_exec.c    13 Jun 2005 06:04:21 -0000
***************
*** 1911,1920 ****
  {
      char       *cp;
      PLpgSQL_dstring ds;
!     ListCell   *current_param;

      plpgsql_dstring_init(&ds);
!     current_param = list_head(stmt->params);

      for (cp = stmt->message; *cp; cp++)
      {
--- 1911,1939 ----
  {
      char       *cp;
      PLpgSQL_dstring ds;
!     int   param_idx = 0;
!     int params_count = 0;

      plpgsql_dstring_init(&ds);
!
!     if (stmt->params_expr)
!     {
!         int rc = exec_run_select(estate, stmt->params_expr, 2, NULL);
!
!         if (rc != SPI_OK_SELECT || estate->eval_processed == 0)
!             ereport(ERROR,
!                     (errcode(ERRCODE_WRONG_OBJECT_TYPE),
!                      errmsg("query \"%s\" did not return data", stmt->params_expr->query)));
!
!         /* Check that the expression returned a single Datum */
!         if (estate->eval_processed > 1)
!             ereport(ERROR,
!                     (errcode(ERRCODE_CARDINALITY_VIOLATION),
!                      errmsg("query \"%s\" returned more than one row",
!                             stmt->params_expr->query)));
!
!         params_count = estate->eval_tuptable->tupdesc->natts;
!     }

      for (cp = stmt->message; *cp; cp++)
      {
***************
*** 1936,1966 ****
                  continue;
              }

!             if (current_param == NULL)
                  ereport(ERROR,
                          (errcode(ERRCODE_SYNTAX_ERROR),
                           errmsg("too few parameters specified for RAISE")));

!             exec_eval_datum(estate, estate->datums[lfirst_int(current_param)],
!                             InvalidOid,
!                             ¶mtypeid, ¶mvalue, ¶misnull);
              if (paramisnull)
                  extval = "<NULL>";
              else
                  extval = convert_value_to_string(paramvalue, paramtypeid);
              plpgsql_dstring_append(&ds, extval);
!             current_param = lnext(current_param);
              continue;
          }

          plpgsql_dstring_append_char(&ds, cp[0]);
      }

      /*
       * If more parameters were specified than were required to process
       * the format string, throw an error
       */
!     if (current_param != NULL)
          ereport(ERROR,
                  (errcode(ERRCODE_SYNTAX_ERROR),
                   errmsg("too many parameters specified for RAISE")));
--- 1955,1989 ----
                  continue;
              }

!             if (param_idx == params_count)
                  ereport(ERROR,
                          (errcode(ERRCODE_SYNTAX_ERROR),
                           errmsg("too few parameters specified for RAISE")));

!             paramvalue = SPI_getbinval(estate->eval_tuptable->vals[0],
!                                        estate->eval_tuptable->tupdesc,
!                                        param_idx + 1, ¶misnull);
!             paramtypeid = SPI_gettypeid(estate->eval_tuptable->tupdesc, param_idx + 1);
!
              if (paramisnull)
                  extval = "<NULL>";
              else
                  extval = convert_value_to_string(paramvalue, paramtypeid);
              plpgsql_dstring_append(&ds, extval);
!             param_idx++;
              continue;
          }

          plpgsql_dstring_append_char(&ds, cp[0]);
      }

+     exec_eval_cleanup(estate);
+
      /*
       * If more parameters were specified than were required to process
       * the format string, throw an error
       */
!     if (param_idx != params_count)
          ereport(ERROR,
                  (errcode(ERRCODE_SYNTAX_ERROR),
                   errmsg("too many parameters specified for RAISE")));
Index: src/pl/plpgsql/src/pl_funcs.c
===================================================================
RCS file: /var/lib/cvs/pgsql/src/pl/plpgsql/src/pl_funcs.c,v
retrieving revision 1.41
diff -c -r1.41 pl_funcs.c
*** src/pl/plpgsql/src/pl_funcs.c    10 Jun 2005 16:23:11 -0000    1.41
--- src/pl/plpgsql/src/pl_funcs.c    13 Jun 2005 05:57:17 -0000
***************
*** 883,894 ****
  static void
  dump_raise(PLpgSQL_stmt_raise *stmt)
  {
-     ListCell *l;
-
      dump_ind();
      printf("RAISE '%s'", stmt->message);
!     foreach (l, stmt->params)
!         printf(" %d", lfirst_int(l));
      printf("\n");
  }

--- 883,891 ----
  static void
  dump_raise(PLpgSQL_stmt_raise *stmt)
  {
      dump_ind();
      printf("RAISE '%s'", stmt->message);
!     dump_expr(stmt->params_expr);
      printf("\n");
  }

Index: src/pl/plpgsql/src/plpgsql.h
===================================================================
RCS file: /var/lib/cvs/pgsql/src/pl/plpgsql/src/plpgsql.h,v
retrieving revision 1.62
diff -c -r1.62 plpgsql.h
*** src/pl/plpgsql/src/plpgsql.h    10 Jun 2005 16:23:11 -0000    1.62
--- src/pl/plpgsql/src/plpgsql.h    13 Jun 2005 05:38:55 -0000
***************
*** 515,521 ****
      int            lineno;
      int            elog_level;
      char       *message;
!     List       *params;
  } PLpgSQL_stmt_raise;


--- 515,521 ----
      int            lineno;
      int            elog_level;
      char       *message;
!     PLpgSQL_expr  *params_expr;
  } PLpgSQL_stmt_raise;


Index: src/test/regress/expected/plpgsql.out
===================================================================
RCS file: /var/lib/cvs/pgsql/src/test/regress/expected/plpgsql.out,v
retrieving revision 1.33
diff -c -r1.33 plpgsql.out
*** src/test/regress/expected/plpgsql.out    10 Jun 2005 16:23:11 -0000    1.33
--- src/test/regress/expected/plpgsql.out    13 Jun 2005 06:28:26 -0000
***************
*** 2418,2444 ****
  --
  -- SQLSTATE and SQLERRM test
  --
- -- should fail: SQLSTATE and SQLERRM are only in defined EXCEPTION
- -- blocks
- create function excpt_test() returns void as $$
- begin
-     raise notice '% %', sqlstate, sqlerrm;
- end; $$ language plpgsql;
- ERROR:  syntax error at or near "sqlstate" at character 79
- LINE 3:     raise notice '% %', sqlstate, sqlerrm;
-                                 ^
- -- should fail
- create function excpt_test() returns void as $$
- begin
-     begin
-         begin
-             raise notice '% %', sqlstate, sqlerrm;
-         end;
-     end;
- end; $$ language plpgsql;
- ERROR:  syntax error at or near "sqlstate" at character 108
- LINE 5:          raise notice '% %', sqlstate, sqlerrm;
-                                      ^
  create function excpt_test() returns void as $$
  begin
      begin
--- 2418,2423 ----
***************
*** 2469,2471 ****
--- 2448,2468 ----
  (1 row)

  drop function excpt_test();
+ -- parameters of raise stmt can be expressions
+ create function raise_exprs() returns void as $$
+ declare
+     a integer[] = '{10,20,30}';
+     c varchar = 'xyz';
+     i integer;
+ begin
+     i := 2;
+     raise notice '%; %; %; %; %', a, a[i], c, (select c || 'abc'), row(10,'aaa',30);
+ end;$$ language plpgsql;
+ select raise_exprs();
+ NOTICE:  {10,20,30}; 20; xyz; xyzabc; (10,aaa,30)
+  raise_exprs
+ -------------
+
+ (1 row)
+
+ drop function raise_exprs();
Index: src/test/regress/sql/plpgsql.sql
===================================================================
RCS file: /var/lib/cvs/pgsql/src/test/regress/sql/plpgsql.sql,v
retrieving revision 1.28
diff -c -r1.28 plpgsql.sql
*** src/test/regress/sql/plpgsql.sql    10 Jun 2005 16:23:11 -0000    1.28
--- src/test/regress/sql/plpgsql.sql    13 Jun 2005 06:25:17 -0000
***************
*** 2054,2077 ****
  --
  -- SQLSTATE and SQLERRM test
  --
-
- -- should fail: SQLSTATE and SQLERRM are only in defined EXCEPTION
- -- blocks
- create function excpt_test() returns void as $$
- begin
-     raise notice '% %', sqlstate, sqlerrm;
- end; $$ language plpgsql;
-
- -- should fail
- create function excpt_test() returns void as $$
- begin
-     begin
-         begin
-             raise notice '% %', sqlstate, sqlerrm;
-         end;
-     end;
- end; $$ language plpgsql;
-
  create function excpt_test() returns void as $$
  begin
      begin
--- 2054,2059 ----
***************
*** 2094,2096 ****
--- 2076,2092 ----

  select excpt_test();
  drop function excpt_test();
+
+ -- parameters of raise stmt can be expressions
+ create function raise_exprs() returns void as $$
+ declare
+     a integer[] = '{10,20,30}';
+     c varchar = 'xyz';
+     i integer;
+ begin
+     i := 2;
+     raise notice '%; %; %; %; %', a, a[i], c, (select c || 'abc'), row(10,'aaa',30);
+ end;$$ language plpgsql;
+
+ select raise_exprs();
+ drop function raise_exprs();