Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit347dd6a

Browse files
committed
Make plpgsql support FOR over a query specified by a cursor declaration,
for improved compatibility with Oracle.Pavel Stehule, with some fixes by me.
1 parent2604359 commit347dd6a

File tree

8 files changed

+934
-555
lines changed

8 files changed

+934
-555
lines changed

‎doc/src/sgml/plpgsql.sgml

Lines changed: 163 additions & 110 deletions
Large diffs are not rendered by default.

‎src/pl/plpgsql/src/gram.y

Lines changed: 144 additions & 89 deletions
Original file line numberDiff line numberDiff line change
@@ -9,7 +9,7 @@
99
*
1010
*
1111
* IDENTIFICATION
12-
* $PostgreSQL: pgsql/src/pl/plpgsql/src/gram.y,v 1.109 2008/04/01 03:51:09 tgl Exp $
12+
* $PostgreSQL: pgsql/src/pl/plpgsql/src/gram.y,v 1.110 2008/04/06 23:43:29 tgl Exp $
1313
*
1414
*-------------------------------------------------------------------------
1515
*/
@@ -50,6 +50,8 @@ staticvoid plpgsql_sql_error_callback(void *arg);
5050
staticchar*check_label(constchar *yytxt);
5151
staticvoidcheck_labels(constchar *start_label,
5252
constchar *end_label);
53+
static PLpgSQL_expr *read_cursor_args(PLpgSQL_var *cursor,
54+
int until,constchar *expected);
5355

5456
%}
5557

@@ -861,21 +863,15 @@ stmt_for: opt_block_label K_FOR for_control loop_body
861863
new->body =$4.stmts;
862864
$$ = (PLpgSQL_stmt *)new;
863865
}
864-
elseif ($3->cmd_type == PLPGSQL_STMT_FORS)
865-
{
866-
PLpgSQL_stmt_fors*new;
867-
868-
new = (PLpgSQL_stmt_fors *)$3;
869-
new->label =$1;
870-
new->body =$4.stmts;
871-
$$ = (PLpgSQL_stmt *)new;
872-
}
873866
else
874867
{
875-
PLpgSQL_stmt_dynfors*new;
868+
PLpgSQL_stmt_forq*new;
876869

877-
Assert($3->cmd_type == PLPGSQL_STMT_DYNFORS);
878-
new = (PLpgSQL_stmt_dynfors *)$3;
870+
Assert($3->cmd_type == PLPGSQL_STMT_FORS ||
871+
$3->cmd_type == PLPGSQL_STMT_FORC ||
872+
$3->cmd_type == PLPGSQL_STMT_DYNFORS);
873+
/* forq is the common supertype of all three*/
874+
new = (PLpgSQL_stmt_forq *)$3;
879875
new->label =$1;
880876
new->body =$4.stmts;
881877
$$ = (PLpgSQL_stmt *)new;
@@ -892,9 +888,9 @@ for_control:
892888
{
893889
inttok =yylex();
894890

895-
/* Simple case: EXECUTE is a dynamic FOR loop*/
896891
if (tok == K_EXECUTE)
897892
{
893+
/* EXECUTE means it's a dynamic FOR loop*/
898894
PLpgSQL_stmt_dynfors*new;
899895
PLpgSQL_expr*expr;
900896
intterm;
@@ -942,6 +938,47 @@ for_control:
942938

943939
$$ = (PLpgSQL_stmt *)new;
944940
}
941+
elseif (tok == T_SCALAR &&
942+
yylval.scalar->dtype == PLPGSQL_DTYPE_VAR &&
943+
((PLpgSQL_var *) yylval.scalar)->datatype->typoid == REFCURSOROID)
944+
{
945+
/* It's FOR var IN cursor*/
946+
PLpgSQL_stmt_forc*new;
947+
PLpgSQL_var*cursor = (PLpgSQL_var *) yylval.scalar;
948+
char*varname;
949+
950+
new = (PLpgSQL_stmt_forc *) palloc0(sizeof(PLpgSQL_stmt_forc));
951+
new->cmd_type = PLPGSQL_STMT_FORC;
952+
new->lineno =$1;
953+
954+
new->curvar = cursor->varno;
955+
956+
/* Should have had a single variable name*/
957+
plpgsql_error_lineno =$2.lineno;
958+
if ($2.scalar &&$2.row)
959+
ereport(ERROR,
960+
(errcode(ERRCODE_SYNTAX_ERROR),
961+
errmsg("cursor FOR loop must have just one target variable")));
962+
963+
/* create loop's private RECORD variable*/
964+
plpgsql_convert_ident($2.name, &varname,1);
965+
new->rec = plpgsql_build_record(varname,
966+
$2.lineno,
967+
true);
968+
969+
/* can't use an unbound cursor this way*/
970+
if (cursor->cursor_explicit_expr ==NULL)
971+
ereport(ERROR,
972+
(errcode(ERRCODE_SYNTAX_ERROR),
973+
errmsg("cursor FOR loop must use a bound cursor variable")));
974+
975+
/* collect cursor's parameters if any*/
976+
new->argquery = read_cursor_args(cursor,
977+
K_LOOP,
978+
"LOOP");
979+
980+
$$ = (PLpgSQL_stmt *)new;
981+
}
945982
else
946983
{
947984
PLpgSQL_expr*expr1;
@@ -1412,81 +1449,8 @@ stmt_open: K_OPEN lno cursor_variable
14121449
}
14131450
else
14141451
{
1415-
if ($3->cursor_explicit_argrow >=0)
1416-
{
1417-
char *cp;
1418-
1419-
tok =yylex();
1420-
if (tok !='(')
1421-
{
1422-
plpgsql_error_lineno = plpgsql_scanner_lineno();
1423-
ereport(ERROR,
1424-
(errcode(ERRCODE_SYNTAX_ERROR),
1425-
errmsg("cursor\"%s\" has arguments",
1426-
$3->refname)));
1427-
}
1428-
1429-
/*
1430-
* Push back the '(', else read_sql_stmt
1431-
* will complain about unbalanced parens.
1432-
*/
1433-
plpgsql_push_back_token(tok);
1434-
1435-
new->argquery = read_sql_stmt("SELECT");
1436-
1437-
/*
1438-
* Now remove the leading and trailing parens,
1439-
* because we want "select 1, 2", not
1440-
* "select (1, 2)".
1441-
*/
1442-
cp =new->argquery->query;
1443-
1444-
if (strncmp(cp,"SELECT",6) !=0)
1445-
{
1446-
plpgsql_error_lineno = plpgsql_scanner_lineno();
1447-
/* internal error*/
1448-
elog(ERROR,"expected\"SELECT (\", got\"%s\"",
1449-
new->argquery->query);
1450-
}
1451-
cp +=6;
1452-
while (*cp =='')/* could be more than 1 space here*/
1453-
cp++;
1454-
if (*cp !='(')
1455-
{
1456-
plpgsql_error_lineno = plpgsql_scanner_lineno();
1457-
/* internal error*/
1458-
elog(ERROR,"expected\"SELECT (\", got\"%s\"",
1459-
new->argquery->query);
1460-
}
1461-
*cp ='';
1462-
1463-
cp += strlen(cp) -1;
1464-
1465-
if (*cp !=')')
1466-
yyerror("expected\")\"");
1467-
*cp ='\0';
1468-
}
1469-
else
1470-
{
1471-
tok =yylex();
1472-
if (tok =='(')
1473-
{
1474-
plpgsql_error_lineno = plpgsql_scanner_lineno();
1475-
ereport(ERROR,
1476-
(errcode(ERRCODE_SYNTAX_ERROR),
1477-
errmsg("cursor\"%s\" has no arguments",
1478-
$3->refname)));
1479-
}
1480-
1481-
if (tok !=';')
1482-
{
1483-
plpgsql_error_lineno = plpgsql_scanner_lineno();
1484-
ereport(ERROR,
1485-
(errcode(ERRCODE_SYNTAX_ERROR),
1486-
errmsg("syntax error at\"%s\"",
1487-
yytext)));
1488-
}
1489-
}
1452+
/* predefined cursor query, so read args*/
1453+
new->argquery = read_cursor_args($3,';',";");
14901454
}
14911455

14921456
$$ = (PLpgSQL_stmt *)new;
@@ -2578,6 +2542,97 @@ check_labels(const char *start_label, const char *end_label)
25782542
}
25792543
}
25802544

2545+
/*
2546+
* Read the arguments (if any) for a cursor, followed by the until token
2547+
*
2548+
* If cursor has no args, just swallow the until token and return NULL.
2549+
* If it does have args, we expect to see "( expr [, expr ...] )" followed
2550+
* by the until token. Consume all that and return a SELECT query that
2551+
* evaluates the expression(s) (without the outer parens).
2552+
*/
2553+
static PLpgSQL_expr *
2554+
read_cursor_args(PLpgSQL_var *cursor,int until,constchar *expected)
2555+
{
2556+
PLpgSQL_expr *expr;
2557+
inttok;
2558+
char *cp;
2559+
2560+
tok =yylex();
2561+
if (cursor->cursor_explicit_argrow <0)
2562+
{
2563+
/* No arguments expected*/
2564+
if (tok =='(')
2565+
{
2566+
plpgsql_error_lineno =plpgsql_scanner_lineno();
2567+
ereport(ERROR,
2568+
(errcode(ERRCODE_SYNTAX_ERROR),
2569+
errmsg("cursor\"%s\" has no arguments",
2570+
cursor->refname)));
2571+
}
2572+
2573+
if (tok != until)
2574+
{
2575+
plpgsql_error_lineno =plpgsql_scanner_lineno();
2576+
ereport(ERROR,
2577+
(errcode(ERRCODE_SYNTAX_ERROR),
2578+
errmsg("syntax error at\"%s\"",
2579+
yytext)));
2580+
}
2581+
2582+
returnNULL;
2583+
}
2584+
2585+
/* Else better provide arguments*/
2586+
if (tok !='(')
2587+
{
2588+
plpgsql_error_lineno =plpgsql_scanner_lineno();
2589+
ereport(ERROR,
2590+
(errcode(ERRCODE_SYNTAX_ERROR),
2591+
errmsg("cursor\"%s\" has arguments",
2592+
cursor->refname)));
2593+
}
2594+
2595+
/*
2596+
* Push back the '(', else plpgsql_read_expression
2597+
* will complain about unbalanced parens.
2598+
*/
2599+
plpgsql_push_back_token(tok);
2600+
2601+
expr =plpgsql_read_expression(until, expected);
2602+
2603+
/*
2604+
* Now remove the leading and trailing parens,
2605+
* because we want "SELECT 1, 2", not "SELECT (1, 2)".
2606+
*/
2607+
cp = expr->query;
2608+
2609+
if (strncmp(cp,"SELECT",6) !=0)
2610+
{
2611+
plpgsql_error_lineno =plpgsql_scanner_lineno();
2612+
/* internal error*/
2613+
elog(ERROR,"expected\"SELECT (\", got\"%s\"", expr->query);
2614+
}
2615+
cp +=6;
2616+
while (*cp =='')/* could be more than 1 space here*/
2617+
cp++;
2618+
if (*cp !='(')
2619+
{
2620+
plpgsql_error_lineno =plpgsql_scanner_lineno();
2621+
/* internal error*/
2622+
elog(ERROR,"expected\"SELECT (\", got\"%s\"", expr->query);
2623+
}
2624+
*cp ='';
2625+
2626+
cp +=strlen(cp) -1;
2627+
2628+
if (*cp !=')')
2629+
yyerror("expected\")\"");
2630+
*cp ='\0';
2631+
2632+
return expr;
2633+
}
2634+
2635+
25812636
/* Needed to avoid conflict between different prefix settings:*/
25822637
#undef yylex
25832638

‎src/pl/plpgsql/src/pl_comp.c

Lines changed: 27 additions & 30 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/pl/plpgsql/src/pl_comp.c,v 1.123 2008/03/27 03:57:34 tgl Exp $
11+
* $PostgreSQL: pgsql/src/pl/plpgsql/src/pl_comp.c,v 1.124 2008/04/06 23:43:29 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -575,25 +575,11 @@ do_compile(FunctionCallInfo fcinfo,
575575
errhint("You probably want to use TG_NARGS and TG_ARGV instead.")));
576576

577577
/* Add the record for referencing NEW */
578-
rec=palloc0(sizeof(PLpgSQL_rec));
579-
rec->dtype=PLPGSQL_DTYPE_REC;
580-
rec->refname=pstrdup("new");
581-
rec->tup=NULL;
582-
rec->tupdesc=NULL;
583-
rec->freetup= false;
584-
plpgsql_adddatum((PLpgSQL_datum*)rec);
585-
plpgsql_ns_additem(PLPGSQL_NSTYPE_REC,rec->recno,rec->refname);
578+
rec=plpgsql_build_record("new",0, true);
586579
function->new_varno=rec->recno;
587580

588581
/* Add the record for referencing OLD */
589-
rec=palloc0(sizeof(PLpgSQL_rec));
590-
rec->dtype=PLPGSQL_DTYPE_REC;
591-
rec->refname=pstrdup("old");
592-
rec->tup=NULL;
593-
rec->tupdesc=NULL;
594-
rec->freetup= false;
595-
plpgsql_adddatum((PLpgSQL_datum*)rec);
596-
plpgsql_ns_additem(PLPGSQL_NSTYPE_REC,rec->recno,rec->refname);
582+
rec=plpgsql_build_record("old",0, true);
597583
function->old_varno=rec->recno;
598584

599585
/* Add the variable tg_name */
@@ -1481,21 +1467,10 @@ plpgsql_build_variable(const char *refname, int lineno, PLpgSQL_type *dtype,
14811467
}
14821468
casePLPGSQL_TTYPE_REC:
14831469
{
1484-
/*
1485-
* "record" type -- build a variable-contents record variable
1486-
*/
1470+
/* "record" type -- build a record variable */
14871471
PLpgSQL_rec*rec;
14881472

1489-
rec=palloc0(sizeof(PLpgSQL_rec));
1490-
rec->dtype=PLPGSQL_DTYPE_REC;
1491-
rec->refname=pstrdup(refname);
1492-
rec->lineno=lineno;
1493-
1494-
plpgsql_adddatum((PLpgSQL_datum*)rec);
1495-
if (add2namespace)
1496-
plpgsql_ns_additem(PLPGSQL_NSTYPE_REC,
1497-
rec->recno,
1498-
refname);
1473+
rec=plpgsql_build_record(refname,lineno,add2namespace);
14991474
result= (PLpgSQL_variable*)rec;
15001475
break;
15011476
}
@@ -1515,6 +1490,28 @@ plpgsql_build_variable(const char *refname, int lineno, PLpgSQL_type *dtype,
15151490
returnresult;
15161491
}
15171492

1493+
/*
1494+
* Build empty named record variable, and optionally add it to namespace
1495+
*/
1496+
PLpgSQL_rec*
1497+
plpgsql_build_record(constchar*refname,intlineno,booladd2namespace)
1498+
{
1499+
PLpgSQL_rec*rec;
1500+
1501+
rec=palloc0(sizeof(PLpgSQL_rec));
1502+
rec->dtype=PLPGSQL_DTYPE_REC;
1503+
rec->refname=pstrdup(refname);
1504+
rec->lineno=lineno;
1505+
rec->tup=NULL;
1506+
rec->tupdesc=NULL;
1507+
rec->freetup= false;
1508+
plpgsql_adddatum((PLpgSQL_datum*)rec);
1509+
if (add2namespace)
1510+
plpgsql_ns_additem(PLPGSQL_NSTYPE_REC,rec->recno,rec->refname);
1511+
1512+
returnrec;
1513+
}
1514+
15181515
/*
15191516
* Build a row-variable data structure given the pg_class OID.
15201517
*/

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp