CREATE SEQUENCE test_id_seq START 1 INCREMENT 1;
CREATE TABLE test_tbl (
test_id BIGINT NOT NULL
DEFAULT nextval('test_id_seq'),
test_name VARCHAR(64) NOT NULL,
PRIMARY KEY (test_id));
CREATE OR REPLACE FUNCTION test_func (test_nm VARCHAR)
RETURNS VOID AS $$
DECLARE
current_rec RECORD;
BEGIN
-- Test INSERT RETURNING
INSERT INTO test_tbl (test_name) VALUES (test_nm)
RETURNING * INTO current_rec;
RAISE NOTICE 'test_id is %', current_rec.test_id;
RAISE NOTICE 'test_name is %', current_rec.test_name;
-- Test UPDATE RETURNING
UPDATE test_tbl SET test_name = 'Uncle Bob'
WHERE test_id = current_rec.test_id
RETURNING * INTO current_rec;
RAISE NOTICE 'test_id is %', current_rec.test_id;
RAISE NOTICE 'test_name is %', current_rec.test_name;
-- Test DELETE RETURNING
DELETE FROM test_tbl WHERE test_id = current_rec.test_id
RETURNING * INTO current_rec;
-- This DOES NOT WORK
RAISE NOTICE 'test_id is %', current_rec.test_id;
RAISE NOTICE 'test_name is %', current_rec.test_name;
RETURN;
END;
$$ LANGUAGE plpgsql;
On 3/2/06, Jonah H. Harris <[EMAIL PROTECTED]> wrote:
All,
This is only the current patch updated to apply cleanly on cvs... it's not ready for -patches yet as I still haven't spent much time looking through it and testing it. This is just for anyone to play with and find issues.
My focus for the next couple days is on getting INS/UPD/DEL RETURNING ready for 8.2... all comments, suggestions, issues would be appreciated.
--
Jonah H. Harris, Database Internals Architect
EnterpriseDB Corporation
732.331.1324
--
Jonah H. Harris, Database Internals Architect
EnterpriseDB Corporation
732.331.1324
diff -cr pgsql/src/pl/plpgsql/src/gram.y pgsql-iudret/src/pl/plpgsql/src/gram.y
*** pgsql/src/pl/plpgsql/src/gram.y 2006-02-12 01:37:05.000000000 -0500
--- pgsql-iudret/src/pl/plpgsql/src/gram.y 2006-03-02 17:08:47.000000000 -0500
***************
*** 50,55 ****
--- 50,56 ----
static PLpgSQL_expr *read_sql_stmt(const char *sqlstart);
static PLpgSQL_type *read_datatype(int tok);
static PLpgSQL_stmt *make_select_stmt(void);
+ static PLpgSQL_stmt *make_returning_stmt(char *command);
static PLpgSQL_stmt *make_fetch_stmt(void);
static void check_assignable(PLpgSQL_datum *datum);
static PLpgSQL_row *read_into_scalar_list(const char *initial_name,
***************
*** 141,146 ****
--- 142,148 ----
%type <stmt> stmt_for stmt_select stmt_perform
%type <stmt> stmt_dynexecute stmt_getdiag
%type <stmt> stmt_open stmt_fetch stmt_close stmt_null
+ %type <stmt> stmt_insert stmt_update stmt_delete
%type <list> proc_exceptions
%type <exception_block> exception_sect
***************
*** 170,175 ****
--- 172,178 ----
%token K_DEBUG
%token K_DECLARE
%token K_DEFAULT
+ %token K_DELETE
%token K_DIAGNOSTICS
%token K_DOTDOT
%token K_ELSE
***************
*** 185,190 ****
--- 188,194 ----
%token K_IF
%token K_IN
%token K_INFO
+ %token K_INSERT
%token K_INTO
%token K_IS
%token K_LOG
***************
*** 201,212 ****
--- 205,218 ----
%token K_RENAME
%token K_RESULT_OID
%token K_RETURN
+ %token K_RETURNING
%token K_RETURN_NEXT
%token K_REVERSE
%token K_SELECT
%token K_THEN
%token K_TO
%token K_TYPE
+ %token K_UPDATE
%token K_WARNING
%token K_WHEN
%token K_WHILE
***************
*** 605,610 ****
--- 611,622 ----
{ $$ = $1; }
| stmt_select
{ $$ = $1; }
+ | stmt_insert
+ { $$ = $1; }
+ | stmt_update
+ { $$ = $1; }
+ | stmt_delete
+ { $$ = $1; }
| stmt_exit
{ $$ = $1; }
| stmt_return
***************
*** 1119,1124 ****
--- 1131,1157 ----
}
;
+ stmt_insert : K_INSERT lno
+ {
+ $$ = make_returning_stmt("INSERT");
+ $$->lineno = $2;
+ }
+ ;
+
+ stmt_update : K_UPDATE lno
+ {
+ $$ = make_returning_stmt("UPDATE");
+ $$->lineno = $2;
+ }
+ ;
+
+ stmt_delete : K_DELETE lno
+ {
+ $$ = make_returning_stmt("DELETE");
+ $$->lineno = $2;
+ }
+ ;
+
stmt_exit : exit_type lno opt_label opt_exitcond
{
PLpgSQL_stmt_exit *new;
***************
*** 2077,2087 ****
--- 2110,2260 ----
execsql = palloc(sizeof(PLpgSQL_stmt_execsql));
execsql->cmd_type = PLPGSQL_STMT_EXECSQL;
execsql->sqlstmt = expr;
+ execsql->rec = rec;
+ execsql->row = row;
return (PLpgSQL_stmt *)execsql;
}
}
+ static PLpgSQL_stmt *
+ make_returning_stmt(char *command)
+ {
+ PLpgSQL_dstring ds;
+ int nparams = 0;
+ int params[1024];
+ char buf[32];
+ PLpgSQL_expr *expr;
+ PLpgSQL_row *row = NULL;
+ PLpgSQL_rec *rec = NULL;
+ int tok;
+ bool have_returning = false;
+ bool have_into = false;
+ PLpgSQL_stmt_execsql *execsql;
+
+ plpgsql_dstring_init(&ds);
+ plpgsql_dstring_append(&ds, command);
+ plpgsql_dstring_append(&ds, " ");
+
+ while (1)
+ {
+ tok = yylex();
+
+ if (tok == ';')
+ break;
+ if (tok == 0)
+ {
+ plpgsql_error_lineno = plpgsql_scanner_lineno();
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("unexpected end of function definition")));
+ }
+ if (tok == K_RETURNING)
+ {
+ if (have_returning)
+ {
+ plpgsql_error_lineno = plpgsql_scanner_lineno();
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("RETURNING specified more than once")));
+ }
+ have_returning = true;
+ }
+ if (tok == K_INTO && have_returning)
+ {
+ if (have_into)
+ {
+ plpgsql_error_lineno = plpgsql_scanner_lineno();
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("RETURNING INTO specified more than once")));
+ }
+ tok = yylex();
+ switch (tok)
+ {
+ case T_ROW:
+ row = yylval.row;
+ check_assignable((PLpgSQL_datum *) row);
+ have_into = true;
+ break;
+
+ case T_RECORD:
+ rec = yylval.rec;
+ check_assignable((PLpgSQL_datum *) rec);
+ have_into = true;
+ break;
+
+ case T_SCALAR:
+ row = read_into_scalar_list(yytext, yylval.scalar);
+ have_into = true;
+ break;
+
+ default:
+ /* Treat the INTO as non-special */
+ plpgsql_dstring_append(&ds, " INTO ");
+ plpgsql_push_back_token(tok);
+ break;
+ }
+ continue;
+ }
+
+ if (plpgsql_SpaceScanned)
+ plpgsql_dstring_append(&ds, " ");
+
+ /* Check for array overflow */
+ if (nparams >= 1024)
+ {
+ plpgsql_error_lineno = plpgsql_scanner_lineno();
+ ereport(ERROR,
+ (errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
+ errmsg("too many parameters specified in SQL statement")));
+ }
+
+ switch (tok)
+ {
+ case T_SCALAR:
+ params[nparams] = yylval.scalar->dno;
+ snprintf(buf, sizeof(buf), " $%d ", ++nparams);
+ plpgsql_dstring_append(&ds, buf);
+ break;
+
+ case T_ROW:
+ params[nparams] = yylval.row->rowno;
+ snprintf(buf, sizeof(buf), " $%d ", ++nparams);
+ plpgsql_dstring_append(&ds, buf);
+ break;
+
+ case T_RECORD:
+ params[nparams] = yylval.rec->recno;
+ snprintf(buf, sizeof(buf), " $%d ", ++nparams);
+ plpgsql_dstring_append(&ds, buf);
+ break;
+
+ default:
+ plpgsql_dstring_append(&ds, yytext);
+ break;
+ }
+ }
+
+ expr = palloc(sizeof(PLpgSQL_expr) + sizeof(int) * nparams - sizeof(int));
+ expr->dtype = PLPGSQL_DTYPE_EXPR;
+ expr->query = pstrdup(plpgsql_dstring_get(&ds));
+ expr->plan = NULL;
+ expr->nparams = nparams;
+ while(nparams-- > 0)
+ expr->params[nparams] = params[nparams];
+ plpgsql_dstring_free(&ds);
+
+ check_sql_expr(expr->query);
+
+ execsql = palloc(sizeof(PLpgSQL_stmt_execsql));
+ execsql->cmd_type = PLPGSQL_STMT_EXECSQL;
+ execsql->sqlstmt = expr;
+ execsql->rec = rec;
+ execsql->row = row;
+
+ return (PLpgSQL_stmt *)execsql;
+ }
static PLpgSQL_stmt *
make_fetch_stmt(void)
diff -cr pgsql/src/pl/plpgsql/src/pl_exec.c pgsql-iudret/src/pl/plpgsql/src/pl_exec.c
*** pgsql/src/pl/plpgsql/src/pl_exec.c 2006-03-02 00:34:12.000000000 -0500
--- pgsql-iudret/src/pl/plpgsql/src/pl_exec.c 2006-03-02 16:36:36.000000000 -0500
***************
*** 2266,2273 ****
/* ----------
! * exec_stmt_execsql Execute an SQL statement not
! * returning any data.
* ----------
*/
static int
--- 2266,2273 ----
/* ----------
! * exec_stmt_execsql Execute an SQL statement which
! * may return data.
* ----------
*/
static int
***************
*** 2279,2284 ****
--- 2279,2286 ----
char *nulls;
int rc;
PLpgSQL_expr *expr = stmt->sqlstmt;
+ PLpgSQL_rec *rec = NULL;
+ PLpgSQL_row *row = NULL;
/*
* On the first call for this expression generate the plan
***************
*** 2307,2322 ****
}
/*
* Execute the plan
*/
rc = SPI_execute_plan(expr->plan, values, nulls,
estate->readonly_func, 0);
switch (rc)
{
case SPI_OK_UTILITY:
case SPI_OK_SELINTO:
break;
-
case SPI_OK_INSERT:
case SPI_OK_DELETE:
case SPI_OK_UPDATE:
--- 2309,2343 ----
}
/*
+ * If the user has selected the RETURNING option, we're going to
+ * determine how to return it.
+ */
+ 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]);
+
+ /*
* Execute the plan
*/
rc = SPI_execute_plan(expr->plan, values, nulls,
estate->readonly_func, 0);
+
+ /* Assign to INTO variable */
+ if (rec || row)
+ {
+ 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);
+ }
+
switch (rc)
{
case SPI_OK_UTILITY:
case SPI_OK_SELINTO:
break;
case SPI_OK_INSERT:
case SPI_OK_DELETE:
case SPI_OK_UPDATE:
***************
*** 2340,2357 ****
expr->query, SPI_result_code_string(rc));
}
! /*
! * Release any result tuples from SPI_execute_plan (probably shouldn't be
! * any)
! */
SPI_freetuptable(SPI_tuptable);
/* Save result info for GET DIAGNOSTICS */
estate->eval_processed = SPI_processed;
estate->eval_lastoid = SPI_lastoid;
! pfree(values);
! pfree(nulls);
return PLPGSQL_RC_OK;
}
--- 2361,2375 ----
expr->query, SPI_result_code_string(rc));
}
! /* Release any result tuples from SPI_execute_plan */
SPI_freetuptable(SPI_tuptable);
/* Save result info for GET DIAGNOSTICS */
estate->eval_processed = SPI_processed;
estate->eval_lastoid = SPI_lastoid;
! pfree(values);
! pfree(nulls);
return PLPGSQL_RC_OK;
}
diff -cr pgsql/src/pl/plpgsql/src/plpgsql.h pgsql-iudret/src/pl/plpgsql/src/plpgsql.h
*** pgsql/src/pl/plpgsql/src/plpgsql.h 2006-03-02 00:34:12.000000000 -0500
--- pgsql-iudret/src/pl/plpgsql/src/plpgsql.h 2006-03-02 15:11:56.000000000 -0500
***************
*** 528,533 ****
--- 528,535 ----
{ /* Generic SQL statement to execute */
int cmd_type;
int lineno;
+ PLpgSQL_rec *rec; /* INTO record or row variable */
+ PLpgSQL_row *row;
PLpgSQL_expr *sqlstmt;
} PLpgSQL_stmt_execsql;
diff -cr pgsql/src/pl/plpgsql/src/scan.l pgsql-iudret/src/pl/plpgsql/src/scan.l
*** pgsql/src/pl/plpgsql/src/scan.l 2006-02-27 11:09:50.000000000 -0500
--- pgsql-iudret/src/pl/plpgsql/src/scan.l 2006-03-02 16:24:38.000000000 -0500
***************
*** 144,149 ****
--- 144,150 ----
debug { return K_DEBUG; }
declare { return K_DECLARE; }
default { return K_DEFAULT; }
+ delete { return K_DELETE; }
diagnostics { return K_DIAGNOSTICS; }
else { return K_ELSE; }
elseif { return K_ELSIF; }
***************
*** 159,164 ****
--- 160,166 ----
if { return K_IF; }
in { return K_IN; }
info { return K_INFO; }
+ insert { return K_INSERT; }
into { return K_INTO; }
is { return K_IS; }
log { return K_LOG; }
***************
*** 174,185 ****
--- 176,189 ----
rename { return K_RENAME; }
result_oid { return K_RESULT_OID; }
return { return K_RETURN; }
+ returning { return K_RETURNING; }
reverse { return K_REVERSE; }
row_count { return K_ROW_COUNT; }
select { return K_SELECT; }
then { return K_THEN; }
to { return K_TO; }
type { return K_TYPE; }
+ update { return K_UPDATE; }
warning { return K_WARNING; }
when { return K_WHEN; }
while { return K_WHILE; }
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
