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
