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

Commit1e4b038

Browse files
committed
Improve behavior of WITH RECURSIVE with an untyped literal in the
non-recursive term. Per an example from Dickson S. Guedes.
1 parent0814250 commit1e4b038

File tree

3 files changed

+56
-5
lines changed

3 files changed

+56
-5
lines changed

‎src/backend/parser/parse_cte.c

Lines changed: 21 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -8,12 +8,13 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/parser/parse_cte.c,v 2.1 2008/10/04 21:56:54 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/parser/parse_cte.c,v 2.2 2008/10/05 22:50:55 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
1515
#include"postgres.h"
1616

17+
#include"catalog/pg_type.h"
1718
#include"nodes/nodeFuncs.h"
1819
#include"parser/analyze.h"
1920
#include"parser/parse_cte.h"
@@ -339,6 +340,8 @@ analyzeCTETargetList(ParseState *pstate, CommonTableExpr *cte, List *tlist)
339340
foreach(tlistitem,tlist)
340341
{
341342
TargetEntry*te= (TargetEntry*)lfirst(tlistitem);
343+
Oidcoltype;
344+
int32coltypmod;
342345

343346
if (te->resjunk)
344347
continue;
@@ -351,10 +354,23 @@ analyzeCTETargetList(ParseState *pstate, CommonTableExpr *cte, List *tlist)
351354
attrname=pstrdup(te->resname);
352355
cte->ctecolnames=lappend(cte->ctecolnames,makeString(attrname));
353356
}
354-
cte->ctecoltypes=lappend_oid(cte->ctecoltypes,
355-
exprType((Node*)te->expr));
356-
cte->ctecoltypmods=lappend_int(cte->ctecoltypmods,
357-
exprTypmod((Node*)te->expr));
357+
coltype=exprType((Node*)te->expr);
358+
coltypmod=exprTypmod((Node*)te->expr);
359+
/*
360+
* If the CTE is recursive, force the exposed column type of any
361+
* "unknown" column to "text". This corresponds to the fact that
362+
* SELECT 'foo' UNION SELECT 'bar' will ultimately produce text.
363+
* We might see "unknown" as a result of an untyped literal in
364+
* the non-recursive term's select list, and if we don't convert
365+
* to text then we'll have a mismatch against the UNION result.
366+
*/
367+
if (cte->cterecursive&&coltype==UNKNOWNOID)
368+
{
369+
coltype=TEXTOID;
370+
coltypmod=-1;/* should be -1 already, but be sure */
371+
}
372+
cte->ctecoltypes=lappend_oid(cte->ctecoltypes,coltype);
373+
cte->ctecoltypmods=lappend_int(cte->ctecoltypmods,coltypmod);
358374
}
359375
if (varattno<numaliases)
360376
ereport(ERROR,

‎src/test/regress/expected/with.out

Lines changed: 24 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -69,6 +69,30 @@ SELECT * FROM t LIMIT 10;
6969
10
7070
(10 rows)
7171

72+
-- Test behavior with an unknown-type literal in the WITH
73+
WITH q AS (SELECT 'foo' AS x)
74+
SELECT x, x IS OF (unknown) as is_unknown FROM q;
75+
x | is_unknown
76+
-----+------------
77+
foo | t
78+
(1 row)
79+
80+
WITH RECURSIVE t(n) AS (
81+
SELECT 'foo'
82+
UNION ALL
83+
SELECT n || ' bar' FROM t WHERE length(n) < 20
84+
)
85+
SELECT n, n IS OF (text) as is_text FROM t;
86+
n | is_text
87+
-------------------------+---------
88+
foo | t
89+
foo bar | t
90+
foo bar bar | t
91+
foo bar bar bar | t
92+
foo bar bar bar bar | t
93+
foo bar bar bar bar bar | t
94+
(6 rows)
95+
7296
--
7397
-- Some examples with a tree
7498
--

‎src/test/regress/sql/with.sql

Lines changed: 11 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -38,6 +38,17 @@ UNION ALL
3838
SELECT n+1FROM t)
3939
SELECT*FROM tLIMIT10;
4040

41+
-- Test behavior with an unknown-type literal in the WITH
42+
WITH qAS (SELECT'foo'AS x)
43+
SELECT x, x IS OF (unknown)as is_unknownFROM q;
44+
45+
WITH RECURSIVE t(n)AS (
46+
SELECT'foo'
47+
UNION ALL
48+
SELECT n||' bar'FROM tWHERE length(n)<20
49+
)
50+
SELECT n, n IS OF (text)as is_textFROM t;
51+
4152
--
4253
-- Some examples with a tree
4354
--

0 commit comments

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