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

Commit7cd9765

Browse files
committed
Re-allow DISTINCT in pl/pgsql expressions.
I'd omitted this from the grammar in commitc9d5298, figuring thatit wasn't worth supporting. However we already have one complaint,so it seems that judgment was wrong. It doesn't require a hugeamount of code, so add it back. (I'm still drawing the line atUNION/INTERSECT/EXCEPT though: those'd require an unreasonableamount of grammar refactoring, and the single-result-row restrictionmakes them near useless anyway.)Also rethink the documentation: this behavior is a property ofall pl/pgsql expressions, not just assignments.Discussion:https://postgr.es/m/20210122134106.e94c5cd7@mail.verfriemelt.org
1 parentab66645 commit7cd9765

File tree

3 files changed

+70
-35
lines changed

3 files changed

+70
-35
lines changed

‎doc/src/sgml/plpgsql.sgml

Lines changed: 18 additions & 14 deletions
Original file line numberDiff line numberDiff line change
@@ -917,6 +917,24 @@ PREPARE <replaceable>statement_name</replaceable>(integer, integer) AS SELECT $1
917917
they are useful to know when trying to diagnose a problem.
918918
More information appears in <xref linkend="plpgsql-plan-caching"/>.
919919
</para>
920+
921+
<para>
922+
Since an <replaceable>expression</replaceable> is converted to a
923+
<literal>SELECT</literal> command, it can contain the same clauses
924+
that an ordinary <literal>SELECT</literal> would, except that it
925+
cannot include a top-level <literal>UNION</literal>,
926+
<literal>INTERSECT</literal>, or <literal>EXCEPT</literal> clause.
927+
Thus for example one could test whether a table is non-empty with
928+
<programlisting>
929+
IF count(*) &gt; 0 FROM my_table THEN ...
930+
</programlisting>
931+
since the <replaceable>expression</replaceable>
932+
between <literal>IF</literal> and <literal>THEN</literal> is parsed as
933+
though it were <literal>SELECT count(*) &gt; 0 FROM my_table</literal>.
934+
The <literal>SELECT</literal> must produce a single column, and not
935+
more than one row. (If it produces no rows, the result is taken as
936+
NULL.)
937+
</para>
920938
</sect1>
921939

922940
<sect1 id="plpgsql-statements">
@@ -973,20 +991,6 @@ my_array[1:3] := array[1,2,3];
973991
complex_array[n].realpart = 12.3;
974992
</programlisting>
975993
</para>
976-
977-
<para>
978-
It's useful to know that what follows the assignment operator is
979-
essentially treated as a <literal>SELECT</literal> command; as long
980-
as it returns a single row and column, it will work. Thus for example
981-
one can write something like
982-
<programlisting>
983-
total_sales := sum(quantity) from sales;
984-
</programlisting>
985-
This provides an effect similar to the single-row <literal>SELECT
986-
... INTO</literal> syntax described in
987-
<xref linkend="plpgsql-statements-sql-onerow"/>. However, that syntax
988-
is more portable.
989-
</para>
990994
</sect2>
991995

992996
<sect2 id="plpgsql-statements-sql-noresult">

‎src/backend/parser/analyze.c

Lines changed: 24 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -2466,7 +2466,7 @@ transformPLAssignStmt(ParseState *pstate, PLAssignStmt *stmt)
24662466

24672467
/*
24682468
* The rest mostly matches transformSelectStmt, except that we needn't
2469-
* consider WITH orDISTINCT, and we build a targetlist our own way.
2469+
* consider WITH orINTO, and we build a targetlist our own way.
24702470
*/
24712471
qry->commandType=CMD_SELECT;
24722472
pstate->p_is_insert= false;
@@ -2590,10 +2590,29 @@ transformPLAssignStmt(ParseState *pstate, PLAssignStmt *stmt)
25902590
EXPR_KIND_GROUP_BY,
25912591
false/* allow SQL92 rules */ );
25922592

2593-
/* No DISTINCT clause */
2594-
Assert(!sstmt->distinctClause);
2595-
qry->distinctClause=NIL;
2596-
qry->hasDistinctOn= false;
2593+
if (sstmt->distinctClause==NIL)
2594+
{
2595+
qry->distinctClause=NIL;
2596+
qry->hasDistinctOn= false;
2597+
}
2598+
elseif (linitial(sstmt->distinctClause)==NULL)
2599+
{
2600+
/* We had SELECT DISTINCT */
2601+
qry->distinctClause=transformDistinctClause(pstate,
2602+
&qry->targetList,
2603+
qry->sortClause,
2604+
false);
2605+
qry->hasDistinctOn= false;
2606+
}
2607+
else
2608+
{
2609+
/* We had SELECT DISTINCT ON */
2610+
qry->distinctClause=transformDistinctOnClause(pstate,
2611+
sstmt->distinctClause,
2612+
&qry->targetList,
2613+
qry->sortClause);
2614+
qry->hasDistinctOn= true;
2615+
}
25972616

25982617
/* transform LIMIT */
25992618
qry->limitOffset=transformLimitClause(pstate,sstmt->limitOffset,

‎src/backend/parser/gram.y

Lines changed: 28 additions & 16 deletions
Original file line numberDiff line numberDiff line change
@@ -389,7 +389,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
389389
OptTableElementListTableElementListOptInheritdefinition
390390
OptTypedTableElementListTypedTableElementList
391391
reloptionsopt_reloptions
392-
OptWithdistinct_clauseopt_definitionfunc_argsfunc_args_list
392+
OptWithopt_definitionfunc_argsfunc_args_list
393393
func_args_with_defaultsfunc_args_with_defaults_list
394394
aggr_argsaggr_args_list
395395
func_ascreatefunc_opt_listalterfunc_opt_list
@@ -401,6 +401,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
401401
name_listrole_listfrom_clausefrom_listopt_array_bounds
402402
qualified_name_listany_nameany_name_listtype_name_list
403403
any_operatorexpr_listattrs
404+
distinct_clauseopt_distinct_clause
404405
target_listopt_target_listinsert_column_listset_target_list
405406
set_clause_listset_clause
406407
def_listoperator_def_listindirectionopt_indirection
@@ -11260,6 +11261,11 @@ select_clause:
1126011261
* As with select_no_parens, simple_select cannot have outer parentheses,
1126111262
* but can have parenthesized subclauses.
1126211263
*
11264+
* It might appear that we could fold the first two alternatives into one
11265+
* by using opt_distinct_clause. However, that causes a shift/reduce conflict
11266+
* against INSERT ... SELECT ... ON CONFLICT. We avoid the ambiguity by
11267+
* requiring SELECT DISTINCT [ON] to be followed by a non-empty target_list.
11268+
*
1126311269
* Note that sort clauses cannot be included at this level --- SQL requires
1126411270
*SELECT foo UNION SELECT bar ORDER BY baz
1126511271
* to be parsed as
@@ -11497,8 +11503,13 @@ opt_all_clause:
1149711503
|/*EMPTY*/
1149811504
;
1149911505

11506+
opt_distinct_clause:
11507+
distinct_clause{$$ =$1; }
11508+
|opt_all_clause{$$ = NIL; }
11509+
;
11510+
1150011511
opt_sort_clause:
11501-
sort_clause{$$ =$1;}
11512+
sort_clause{$$ =$1;}
1150211513
|/*EMPTY*/{$$ = NIL; }
1150311514
;
1150411515

@@ -15065,32 +15076,33 @@ role_list:RoleSpec
1506515076
* Therefore the returned struct is a SelectStmt.
1506615077
*****************************************************************************/
1506715078

15068-
PLpgSQL_Expr: opt_target_list
15079+
PLpgSQL_Expr:opt_distinct_clauseopt_target_list
1506915080
from_clause where_clause
1507015081
group_clause having_clause window_clause
1507115082
opt_sort_clause opt_select_limit opt_for_locking_clause
1507215083
{
1507315084
SelectStmt *n =makeNode(SelectStmt);
1507415085

15075-
n->targetList = $1;
15076-
n->fromClause = $2;
15077-
n->whereClause = $3;
15078-
n->groupClause = $4;
15079-
n->havingClause = $5;
15080-
n->windowClause = $6;
15081-
n->sortClause = $7;
15082-
if ($8)
15086+
n->distinctClause = $1;
15087+
n->targetList = $2;
15088+
n->fromClause = $3;
15089+
n->whereClause = $4;
15090+
n->groupClause = $5;
15091+
n->havingClause = $6;
15092+
n->windowClause = $7;
15093+
n->sortClause = $8;
15094+
if ($9)
1508315095
{
15084-
n->limitOffset = $8->limitOffset;
15085-
n->limitCount = $8->limitCount;
15096+
n->limitOffset = $9->limitOffset;
15097+
n->limitCount = $9->limitCount;
1508615098
if (!n->sortClause &&
15087-
$8->limitOption == LIMIT_OPTION_WITH_TIES)
15099+
$9->limitOption == LIMIT_OPTION_WITH_TIES)
1508815100
ereport(ERROR,
1508915101
(errcode(ERRCODE_SYNTAX_ERROR),
1509015102
errmsg("WITH TIES cannot be specified without ORDER BY clause")));
15091-
n->limitOption = $8->limitOption;
15103+
n->limitOption = $9->limitOption;
1509215104
}
15093-
n->lockingClause = $9;
15105+
n->lockingClause = $10;
1509415106
$$ = (Node *) n;
1509515107
}
1509615108
;

0 commit comments

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