Hello

        I did small trivial patch (almost all was written) for storing 
result from executing dynamic query into ROW or RECORD variable.

CREATE TABLE fxx(i integer, y integer);
CREATE TYPE fxt AS (i integer, z integer);

CREATE OR REPLACE FUNCTION foo(varchar) RETURNS RECORD AS $$
DECLARE _r RECORD; _f fxx%ROWTYPE; _t fxt; z fxx;
BEGIN
  DELETE FROM fxx;
  EXECUTE 'INSERT INTO fxx VALUES(10,15)';
  EXECUTE 'SELECT (row).* from (select row(10,1)::fxx)s' INTO _r;
  RAISE NOTICE '%', _r.i;
  EXECUTE 'SELECT * FROM '||$1||' LIMIT 1' INTO _f;
  RETURN _f;
END; $$ LANGUAGE plpgsql;
SELECT foo('fxx');

pokus=# NOTICE:  10
   foo
---------
 (10,15)
(1 row)

Best regards
Pavel Stehule
diff -c -r --new-file pgsql.old/src/pl/plpgsql/src/gram.y 
pgsql.new/src/pl/plpgsql/src/gram.y
*** pgsql.old/src/pl/plpgsql/src/gram.y 2005-04-07 16:53:04.000000000 +0200
--- pgsql.new/src/pl/plpgsql/src/gram.y 2005-06-01 10:33:49.000000000 +0200
***************
*** 108,120 ****
  %type <str>           decl_stmts decl_stmt
  
  %type <expr>  expr_until_semi expr_until_rightbracket
! %type <expr>  expr_until_then expr_until_loop
  %type <expr>  opt_exitcond
  
! %type <ival>  assign_var cursor_variable
  %type <var>           cursor_varptr
  %type <variable>      decl_cursor_arg
  %type <forvariable>   for_variable
  %type <stmt>  for_control
  
  %type <str>           opt_lblname opt_label
--- 108,121 ----
  %type <str>           decl_stmts decl_stmt
  
  %type <expr>  expr_until_semi expr_until_rightbracket
! %type <expr>  expr_until_then expr_until_loop expr_until_intosemi
  %type <expr>  opt_exitcond
  
! %type <ival>  assign_var cursor_variable 
  %type <var>           cursor_varptr
  %type <variable>      decl_cursor_arg
  %type <forvariable>   for_variable
+ %type <forvariable>   opt_into_rec
  %type <stmt>  for_control
  
  %type <str>           opt_lblname opt_label
***************
*** 809,814 ****
--- 810,816 ----
                                        }
                                ;
  
+ 
  stmt_for              : opt_label K_FOR for_control loop_body
                                        {
                                                /* This runs after we've 
scanned the loop body */
***************
*** 1250,1268 ****
                                        }
                                ;
  
! stmt_dynexecute : K_EXECUTE lno expr_until_semi
                                        {
                                                PLpgSQL_stmt_dynexecute *new;
  
                                                new = 
palloc(sizeof(PLpgSQL_stmt_dynexecute));
                                                new->cmd_type = 
PLPGSQL_STMT_DYNEXECUTE;
                                                new->lineno   = $2;
!                                               new->query        = $3;
! 
                                                $$ = (PLpgSQL_stmt *)new;
                                        }
                                ;
! 
  stmt_open             : K_OPEN lno cursor_varptr
                                        {
                                                PLpgSQL_stmt_open *new;
--- 1252,1287 ----
                                        }
                                ;
  
! stmt_dynexecute: K_EXECUTE lno expr_until_intosemi opt_into_rec
                                        {
                                                PLpgSQL_stmt_dynexecute *new;
  
                                                new = 
palloc(sizeof(PLpgSQL_stmt_dynexecute));
                                                new->cmd_type = 
PLPGSQL_STMT_DYNEXECUTE;
                                                new->lineno   = $2;
!                                               new->query    = $3;
!                                               new->rec        = $4.rec;
!                                               new->row        = $4.row;
                                                $$ = (PLpgSQL_stmt *)new;
+ 
                                        }
                                ;
! opt_into_rec:                                 { $$.rec = NULL; $$.row = NULL; 
}
!                       
!               | T_RECORD ';'
!                                       {
!                                               check_assignable((PLpgSQL_datum 
*) yylval.rec);
!                                               $$.rec = yylval.rec; 
!                                               $$.row = NULL;
!                                       }
!               | T_ROW ';'
!                                       {
!                                               check_assignable((PLpgSQL_datum 
*) yylval.row);
!                                               $$.rec = NULL; 
!                                               $$.row = yylval.row;
!                                       }
!                               ;                                       
!                   
  stmt_open             : K_OPEN lno cursor_varptr
                                        {
                                                PLpgSQL_stmt_open *new;
***************
*** 1513,1518 ****
--- 1532,1540 ----
                                        { $$ = plpgsql_read_expression(K_LOOP, 
"LOOP"); }
                                ;
  
+ expr_until_intosemi:                  { $$ = plpgsql_read_expression2(K_INTO, 
';', "INTO/;"); }
+                               ;
+ 
  opt_label             :
                                        {
                                                plpgsql_ns_push(NULL);
***************
*** 1571,1576 ****
--- 1593,1605 ----
        return read_sql_construct(until, 0, expected, "SELECT ", true, true, 
NULL);
  }
  
+ PLpgSQL_expr *
+ plpgsql_read_expression2(int until, int until2, const char *expected)
+ {
+       return read_sql_construct(until, until2, expected, "SELECT ", true, 
true, NULL);
+ }
+ 
+ 
  static PLpgSQL_expr *
  read_sql_stmt(const char *sqlstart)
  {
diff -c -r --new-file pgsql.old/src/pl/plpgsql/src/pl_exec.c 
pgsql.new/src/pl/plpgsql/src/pl_exec.c
*** pgsql.old/src/pl/plpgsql/src/pl_exec.c      2005-05-30 08:55:09.000000000 
+0200
--- pgsql.new/src/pl/plpgsql/src/pl_exec.c      2005-06-01 10:38:40.000000000 
+0200
***************
*** 2245,2250 ****
--- 2245,2257 ----
        Oid                     restype;
        char       *querystr;
        int                     exec_res;
+       PLpgSQL_rec *rec = NULL;
+       PLpgSQL_row *row = NULL;
+ 
+       if (stmt->rec != NULL)
+               rec = (PLpgSQL_rec *) (estate->datums[stmt->rec->recno]);
+       else if (stmt->row != NULL)
+               row = (PLpgSQL_row *) (estate->datums[stmt->row->rowno]);
  
        /*
         * First we evaluate the string expression after the EXECUTE keyword.
***************
*** 2267,2275 ****
--- 2274,2297 ----
         * results will be discarded.
         */
        exec_res = SPI_execute(querystr, estate->readonly_func, 0);
+       if (exec_res != SPI_OK_SELECT && (rec || row))
+               ereport(ERROR,
+                       (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+                        errmsg("EXECUTE ... INTO is only for SELECT")));
+       
        switch (exec_res)
        {
                case SPI_OK_SELECT:
+               {
+                       if (row || rec) 
+                       {
+                           if (SPI_processed == 0)
+                               exec_move_row(estate, rec, row, NULL, 
SPI_tuptable->tupdesc);
+                           else
+                               exec_move_row(estate, rec, row, 
SPI_tuptable->vals[0], SPI_tuptable->tupdesc);
+                       }
+                       break;
+               }
                case SPI_OK_INSERT:
                case SPI_OK_UPDATE:
                case SPI_OK_DELETE:
***************
*** 2339,2344 ****
--- 2361,2367 ----
        estate->eval_processed = SPI_processed;
        estate->eval_lastoid = SPI_lastoid;
  
+ 
        return PLPGSQL_RC_OK;
  }
  
diff -c -r --new-file pgsql.old/src/pl/plpgsql/src/plpgsql.h 
pgsql.new/src/pl/plpgsql/src/plpgsql.h
*** pgsql.old/src/pl/plpgsql/src/plpgsql.h      2005-05-30 07:39:32.000000000 
+0200
--- pgsql.new/src/pl/plpgsql/src/plpgsql.h      2005-06-01 08:35:20.000000000 
+0200
***************
*** 524,529 ****
--- 524,531 ----
  {                                                             /* Dynamic SQL 
string to execute */
        int                     cmd_type;
        int                     lineno;
+       PLpgSQL_rec *rec;
+       PLpgSQL_row *row;           
        PLpgSQL_expr *query;
  } PLpgSQL_stmt_dynexecute;
  
***************
*** 734,739 ****
--- 736,742 ----
   * ----------
   */
  extern PLpgSQL_expr *plpgsql_read_expression(int until, const char *expected);
+ extern PLpgSQL_expr *plpgsql_read_expression2(int until, int until2, const 
char *expected);
  extern int    plpgsql_yyparse(void);
  extern int    plpgsql_base_yylex(void);
  extern int    plpgsql_yylex(void);
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Reply via email to