Hello

  This patch is second release with changes wia 

http://archives.postgresql.org/pgsql-patches/2005-06/msg00024.php

It's trivial, but I houpe usefull.

Regards

Pavel Stehule
diff -c -r --new-file pgsql.01/doc/src/sgml/plpgsql.sgml 
pgsql/doc/src/sgml/plpgsql.sgml
*** pgsql.01/doc/src/sgml/plpgsql.sgml  2005-06-06 20:46:00.000000000 +0200
--- pgsql/doc/src/sgml/plpgsql.sgml     2005-06-06 22:18:53.000000000 +0200
***************
*** 1251,1263 ****
       <command>EXECUTE</command> statement is provided:
  
  <synopsis>
! EXECUTE <replaceable class="command">command-string</replaceable>;
  </synopsis>
  
       where <replaceable>command-string</replaceable> is an expression
       yielding a string (of type
       <type>text</type>) containing the command
       to be executed.  This string is fed literally to the SQL engine.
      </para>
  
      <para>
--- 1251,1265 ----
       <command>EXECUTE</command> statement is provided:
  
  <synopsis>
! EXECUTE <replaceable class="command">command-string</replaceable> [INTO 
record_or_row];
  </synopsis>
  
       where <replaceable>command-string</replaceable> is an expression
       yielding a string (of type
       <type>text</type>) containing the command
       to be executed.  This string is fed literally to the SQL engine.
+      <replaceable>record_or_row</replaceable> is any record or row variable.
+      INTO record_or_row clause is optional.
      </para>
  
      <para>
***************
*** 1277,1291 ****
  
      <para>
       The results from <command>SELECT</command> commands are discarded
!      by <command>EXECUTE</command>, and <command>SELECT INTO</command>
       is not currently supported within <command>EXECUTE</command>.
-      So there is no way to extract a result from a dynamically-created
-      <command>SELECT</command> using the plain <command>EXECUTE</command>
-      command.  There are two other ways to do it, however: one is to use the
-      <command>FOR-IN-EXECUTE</>
-      loop form described in <xref linkend="plpgsql-records-iterating">,
-      and the other is to use a cursor with <command>OPEN-FOR-EXECUTE</>, as
-      described in <xref linkend="plpgsql-cursor-opening">.
      </para>
  
      <para>
--- 1279,1287 ----
  
      <para>
       The results from <command>SELECT</command> commands are discarded
!      by <command>EXECUTE</command> if don't use clause INTO. 
!      <command>SELECT INTO</command>
       is not currently supported within <command>EXECUTE</command>.
      </para>
  
      <para>
diff -c -r --new-file pgsql.01/src/pl/plpgsql/src/gram.y 
pgsql/src/pl/plpgsql/src/gram.y
*** pgsql.01/src/pl/plpgsql/src/gram.y  2005-06-06 20:30:30.000000000 +0200
--- pgsql/src/pl/plpgsql/src/gram.y     2005-06-06 21:53:20.000000000 +0200
***************
*** 1260,1277 ****
                                        }
                                ;
  
! 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
                                        {
--- 1260,1320 ----
                                        }
                                ;
  
! stmt_dynexecute : K_EXECUTE lno 
                                        {
  
!                                               PLpgSQL_stmt_dynexecute *new;
!                                               PLpgSQL_expr *expr;
!                                               int endtoken;
!                                               
!                                               expr = 
read_sql_construct(K_INTO, ';', "INTO|;", "SELECT ", true, true, &endtoken);
!   
!                                               new = 
palloc(sizeof(PLpgSQL_stmt_dynexecute));
!                                               new->cmd_type = 
PLPGSQL_STMT_DYNEXECUTE;
!                                               new->lineno   = $2;
!                                               new->query    = expr;
!  
!                                               new->rec = NULL;
!                                               new->row = NULL;
!                                               
!                                               if (endtoken == K_INTO) 
!                                               {
!                                                       switch (yylex())
!                                                       {
!                                                               case T_ROW:
!                                                                       
check_assignable((PLpgSQL_datum *) yylval.row);
!                                                                       
new->row = yylval.row;
!                                                                       break;
!  
!                                                               case T_RECORD:
!                                                                       
check_assignable((PLpgSQL_datum *) yylval.row);
!                                                                       
new->rec = yylval.rec;
!                                                                       break;
!  
!                                                               default:
!                                                                       
plpgsql_error_lineno = $2;
!                                                                       
ereport(ERROR,
!                                                                               
(errcode(ERRCODE_SYNTAX_ERROR),
!                                                                               
 errmsg("syntax error at \"%s\"",
!                                                                               
                yytext),
!                                                                               
 errdetail("Expected record or row variable.")));
!                                                       }
!                                                       if (yylex() != ';')
!                                                       {
!                                                               
plpgsql_error_lineno = $2;
!                                                               ereport(ERROR,
!                                                                               
(errcode(ERRCODE_SYNTAX_ERROR),
!                                                                               
 errmsg("syntax error at \"%s\"",
!                                                                               
                yytext),
!                                                                               
 errdetail("Expected record or row variable.")));
!                                                       }
!                                               }
!                                       
!                                               $$ = (PLpgSQL_stmt *)new;
!   
!                                       }
!                               ;
  
  
  stmt_open             : K_OPEN lno cursor_varptr
                                        {
diff -c -r --new-file pgsql.01/src/pl/plpgsql/src/pl_exec.c 
pgsql/src/pl/plpgsql/src/pl_exec.c
*** pgsql.01/src/pl/plpgsql/src/pl_exec.c       2005-06-06 20:35:33.000000000 
+0200
--- pgsql/src/pl/plpgsql/src/pl_exec.c  2005-06-06 22:08:58.000000000 +0200
***************
*** 2236,2241 ****
--- 2236,2249 ----
        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.
         * It's result is the querystring we have to execute.
***************
*** 2257,2265 ****
--- 2265,2288 ----
         * 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:
diff -c -r --new-file pgsql.01/src/pl/plpgsql/src/plpgsql.h 
pgsql/src/pl/plpgsql/src/plpgsql.h
*** pgsql.01/src/pl/plpgsql/src/plpgsql.h       2005-06-06 20:20:43.000000000 
+0200
--- pgsql/src/pl/plpgsql/src/plpgsql.h  2005-06-06 22:03:44.000000000 +0200
***************
*** 526,531 ****
--- 526,533 ----
  {                                                             /* Dynamic SQL 
string to execute */
        int                     cmd_type;
        int                     lineno;
+       PLpgSQL_rec *rec;                                       /* INTO record 
or row variable */
+       PLpgSQL_row *row;
        PLpgSQL_expr *query;
  } PLpgSQL_stmt_dynexecute;
  
diff -c -r --new-file pgsql.01/src/test/regress/expected/plpgsql.out 
pgsql/src/test/regress/expected/plpgsql.out
*** pgsql.01/src/test/regress/expected/plpgsql.out      2005-06-06 
20:48:57.000000000 +0200
--- pgsql/src/test/regress/expected/plpgsql.out 2005-06-06 22:15:34.000000000 
+0200
***************
*** 2415,2417 ****
--- 2415,2449 ----
  
  drop function excpt_test();
  DROP FUNCTION
+       
+ --
+ -- EXECUTE ... INTO test
+ --
+ create table eifoo (i integer, y integer);
+ CREATE TABLE
+ create type eitype as (i integer, y integer);
+ CREATE TYPE
+ create or replace function execute_into_test(varchar) returns record as $$
+ declare _r record; _rt eifoo%rowtype; _v eitype;
+ begin
+   execute 'insert into '||$1||' values(10,15)';
+   execute 'select (row).* from (select row(10,1)::eifoo) s' INTO _r;
+   raise notice '% %', _r.i, _r.y;
+   execute 'select * from '||$1||' limit 1' into _rt;
+   raise notice '% %', _rt.i, _rt.y;
+   execute 'select 1,2' into _v;
+   return _v;
+ end; $$ language plpgsql;
+ CREATE FUNCTION
+ select execute_into_test('eifoo');
+ NOTICE:  10 1
+ NOTICE:  10 15
+  execute_into_test 
+ -------------------
+  (1,2)
+ (1 row)
+ 
+ drop table eifoo cascade;
+ DROP TABLE
+ drop type eitype cascade;
+ DROP TYPE
diff -c -r --new-file pgsql.01/src/test/regress/sql/plpgsql.sql 
pgsql/src/test/regress/sql/plpgsql.sql
*** pgsql.01/src/test/regress/sql/plpgsql.sql   2005-06-06 20:41:53.000000000 
+0200
--- pgsql/src/test/regress/sql/plpgsql.sql      2005-06-06 22:14:15.000000000 
+0200
***************
*** 2043,2045 ****
--- 2043,2069 ----
  select excpt_test();
  
  drop function excpt_test();
+ 
+ --
+ -- EXECUTE ... INTO test
+ --
+ 
+ create table eifoo (i integer, y integer);
+ create type eitype as (i integer, y integer);
+ 
+ create or replace function execute_into_test(varchar) returns record as $$
+ declare _r record; _rt eifoo%rowtype; _v eitype;
+ begin
+   execute 'insert into '||$1||' values(10,15)';
+   execute 'select (row).* from (select row(10,1)::eifoo) s' INTO _r;
+   raise notice '% %', _r.i, _r.y;
+   execute 'select * from '||$1||' limit 1' into _rt;
+   raise notice '% %', _rt.i, _rt.y;
+   execute 'select 1,2' into _v;
+   return _v;
+ end; $$ language plpgsql;
+ 
+ select execute_into_test('eifoo');
+ 
+ drop table eifoo cascade;
+ drop type eitype cascade;
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Reply via email to