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

Commit470a104

Browse files
committed
plpgsql functions can return RECORD, per Neil Conway.
1 parent1903221 commit470a104

File tree

7 files changed

+123
-17
lines changed

7 files changed

+123
-17
lines changed

‎doc/src/sgml/plpgsql.sgml

Lines changed: 1 addition & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$Header: /cvsroot/pgsql/doc/src/sgml/plpgsql.sgml,v 1.5 2002/08/30 00:28:40 tgl Exp $
2+
$Header: /cvsroot/pgsql/doc/src/sgml/plpgsql.sgml,v 1.6 2002/09/01 16:28:05 tgl Exp $
33
-->
44

55
<chapter id="plpgsql">
@@ -538,8 +538,6 @@ END;
538538

539539
<para>
540540
Note that <literal>RECORD</> is not a true data type, only a placeholder.
541-
Thus, for example, one cannot declare a function returning
542-
<literal>RECORD</>.
543541
</para>
544542
</sect2>
545543

‎doc/src/sgml/xfunc.sgml

Lines changed: 22 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$Header: /cvsroot/pgsql/doc/src/sgml/xfunc.sgml,v 1.59 2002/08/30 00:28:40 tgl Exp $
2+
$Header: /cvsroot/pgsql/doc/src/sgml/xfunc.sgml,v 1.60 2002/09/01 16:28:05 tgl Exp $
33
-->
44

55
<chapter id="xfunc">
@@ -2119,6 +2119,27 @@ SELECT * FROM vw_getfoo;
21192119
</programlisting>
21202120
are all valid statements.
21212121
</para>
2122+
2123+
<para>
2124+
In some cases it is useful to define table functions that can return
2125+
different column sets depending on how they are invoked. To support this,
2126+
the table function can be declared as returning the pseudo-type
2127+
<type>record</>. When such a function is used in a query, the expected
2128+
row structure must be specified in the query itself, so that the system
2129+
can know how to parse and plan the query. Consider this example:
2130+
<programlisting>
2131+
SELECT *
2132+
FROM dblink('dbname=template1', 'select proname, prosrc from pg_proc')
2133+
AS t1(proname name, prosrc text)
2134+
WHERE proname LIKE 'bytea%';
2135+
</programlisting>
2136+
The <literal>dblink</> function executes a remote query (see
2137+
<literal>contrib/dblink</>). It is declared to return <type>record</>
2138+
since it might be used for any kind of query. The actual column set
2139+
must be specified in the calling query so that the parser knows, for
2140+
example, what <literal>*</> should expand to.
2141+
</para>
2142+
21222143
</sect1>
21232144

21242145
<sect1 id="xfunc-plhandler">

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

Lines changed: 1 addition & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -4,7 +4,7 @@
44
* procedural language
55
*
66
* IDENTIFICATION
7-
* $Header: /cvsroot/pgsql/src/pl/plpgsql/src/gram.y,v 1.36 2002/08/30 00:28:41 tgl Exp $
7+
* $Header: /cvsroot/pgsql/src/pl/plpgsql/src/gram.y,v 1.37 2002/09/01 16:28:06 tgl Exp $
88
*
99
* This software is copyrighted by Jan Wieck - Hamburg.
1010
*
@@ -1159,7 +1159,6 @@ stmt_return: K_RETURN lno
11591159
}
11601160
;
11611161

1162-
/* FIXME: this syntax needs work, RETURN NEXT breaks stmt_return*/
11631162
stmt_return_next:K_RETURN_NEXTlno
11641163
{
11651164
PLpgSQL_stmt_return_next *new;

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

Lines changed: 7 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -3,7 +3,7 @@
33
* procedural language
44
*
55
* IDENTIFICATION
6-
* $Header: /cvsroot/pgsql/src/pl/plpgsql/src/pl_comp.c,v 1.49 2002/08/30 00:28:41 tgl Exp $
6+
* $Header: /cvsroot/pgsql/src/pl/plpgsql/src/pl_comp.c,v 1.50 2002/09/01 16:28:06 tgl Exp $
77
*
88
* This software is copyrighted by Jan Wieck - Hamburg.
99
*
@@ -211,11 +211,11 @@ plpgsql_compile(Oid fn_oid, int functype)
211211
procStruct->prorettype);
212212
typeStruct= (Form_pg_type)GETSTRUCT(typeTup);
213213

214-
/* Disallow pseudotype result, except VOID */
215-
/* XXX someday allow RECORD? */
214+
/* Disallow pseudotype result, except VOID or RECORD */
216215
if (typeStruct->typtype=='p')
217216
{
218-
if (procStruct->prorettype==VOIDOID)
217+
if (procStruct->prorettype==VOIDOID||
218+
procStruct->prorettype==RECORDOID)
219219
/* okay */;
220220
elseif (procStruct->prorettype==TRIGGEROID||
221221
procStruct->prorettype==OPAQUEOID)
@@ -227,7 +227,8 @@ plpgsql_compile(Oid fn_oid, int functype)
227227
format_type_be(procStruct->prorettype));
228228
}
229229

230-
if (typeStruct->typrelid!=InvalidOid)
230+
if (typeStruct->typrelid!=InvalidOid||
231+
procStruct->prorettype==RECORDOID)
231232
function->fn_retistuple= true;
232233
else
233234
{
@@ -486,8 +487,7 @@ plpgsql_compile(Oid fn_oid, int functype)
486487
}
487488

488489
/*
489-
* Create the magic found variable indicating if the last FOR or
490-
* SELECT statement returned data
490+
* Create the magic FOUND variable.
491491
*/
492492
var=malloc(sizeof(PLpgSQL_var));
493493
memset(var,0,sizeof(PLpgSQL_var));

‎src/pl/plpgsql/src/pl_exec.c

Lines changed: 4 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -3,7 +3,7 @@
33
* procedural language
44
*
55
* IDENTIFICATION
6-
* $Header: /cvsroot/pgsql/src/pl/plpgsql/src/pl_exec.c,v 1.61 2002/08/30 23:59:46 tgl Exp $
6+
* $Header: /cvsroot/pgsql/src/pl/plpgsql/src/pl_exec.c,v 1.62 2002/09/01 16:28:06 tgl Exp $
77
*
88
* This software is copyrighted by Jan Wieck - Hamburg.
99
*
@@ -1588,9 +1588,9 @@ static int
15881588
exec_stmt_return_next(PLpgSQL_execstate*estate,
15891589
PLpgSQL_stmt_return_next*stmt)
15901590
{
1591-
TupleDesctupdesc;
1592-
intnatts;
1593-
HeapTupletuple;
1591+
TupleDesctupdesc;
1592+
intnatts;
1593+
HeapTupletuple;
15941594
boolfree_tuple= false;
15951595

15961596
if (!estate->retisset)

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

Lines changed: 53 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1680,3 +1680,56 @@ select * from test_ret_set_scalar(1,10);
16801680
11
16811681
(10 rows)
16821682

1683+
create function test_ret_set_rec_dyn(int) returns setof record as '
1684+
DECLARE
1685+
retval RECORD;
1686+
BEGIN
1687+
IF $1 > 10 THEN
1688+
SELECT INTO retval 5, 10, 15;
1689+
RETURN NEXT retval;
1690+
RETURN NEXT retval;
1691+
ELSE
1692+
SELECT INTO retval 50, 5::numeric, ''xxx''::text;
1693+
RETURN NEXT retval;
1694+
RETURN NEXT retval;
1695+
END IF;
1696+
RETURN;
1697+
END;' language 'plpgsql';
1698+
SELECT * FROM test_ret_set_rec_dyn(1500) AS (a int, b int, c int);
1699+
a | b | c
1700+
---+----+----
1701+
5 | 10 | 15
1702+
5 | 10 | 15
1703+
(2 rows)
1704+
1705+
SELECT * FROM test_ret_set_rec_dyn(5) AS (a int, b numeric, c text);
1706+
a | b | c
1707+
----+---+-----
1708+
50 | 5 | xxx
1709+
50 | 5 | xxx
1710+
(2 rows)
1711+
1712+
create function test_ret_rec_dyn(int) returns record as '
1713+
DECLARE
1714+
retval RECORD;
1715+
BEGIN
1716+
IF $1 > 10 THEN
1717+
SELECT INTO retval 5, 10, 15;
1718+
RETURN retval;
1719+
ELSE
1720+
SELECT INTO retval 50, 5::numeric, ''xxx''::text;
1721+
RETURN retval;
1722+
END IF;
1723+
END;' language 'plpgsql';
1724+
SELECT * FROM test_ret_rec_dyn(1500) AS (a int, b int, c int);
1725+
a | b | c
1726+
---+----+----
1727+
5 | 10 | 15
1728+
(1 row)
1729+
1730+
SELECT * FROM test_ret_rec_dyn(5) AS (a int, b numeric, c text);
1731+
a | b | c
1732+
----+---+-----
1733+
50 | 5 | xxx
1734+
(1 row)
1735+

‎src/test/regress/sql/plpgsql.sql

Lines changed: 35 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1524,3 +1524,38 @@ BEGIN
15241524
END;' language'plpgsql';
15251525

15261526
select*from test_ret_set_scalar(1,10);
1527+
1528+
createfunctiontest_ret_set_rec_dyn(int) returns setof recordas'
1529+
DECLARE
1530+
retval RECORD;
1531+
BEGIN
1532+
IF $1 > 10 THEN
1533+
SELECT INTO retval 5, 10, 15;
1534+
RETURN NEXT retval;
1535+
RETURN NEXT retval;
1536+
ELSE
1537+
SELECT INTO retval 50, 5::numeric,''xxx''::text;
1538+
RETURN NEXT retval;
1539+
RETURN NEXT retval;
1540+
END IF;
1541+
RETURN;
1542+
END;' language'plpgsql';
1543+
1544+
SELECT*FROM test_ret_set_rec_dyn(1500)AS (aint, bint, cint);
1545+
SELECT*FROM test_ret_set_rec_dyn(5)AS (aint, bnumeric, ctext);
1546+
1547+
createfunctiontest_ret_rec_dyn(int) returns recordas'
1548+
DECLARE
1549+
retval RECORD;
1550+
BEGIN
1551+
IF $1 > 10 THEN
1552+
SELECT INTO retval 5, 10, 15;
1553+
RETURN retval;
1554+
ELSE
1555+
SELECT INTO retval 50, 5::numeric,''xxx''::text;
1556+
RETURN retval;
1557+
END IF;
1558+
END;' language'plpgsql';
1559+
1560+
SELECT*FROM test_ret_rec_dyn(1500)AS (aint, bint, cint);
1561+
SELECT*FROM test_ret_rec_dyn(5)AS (aint, bnumeric, ctext);

0 commit comments

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