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

Commit9218689

Browse files
committed
Attached are two patches to implement and document anonymous composite
types for Table Functions, as previously proposed on HACKERS. Here is abrief explanation:1. Creates a new pg_type typtype: 'p' for pseudo type (currently either 'b' for base or 'c' for catalog, i.e. a class).2. Creates new builtin type of typtype='p' named RECORD. This is the first of potentially several pseudo types.3. Modify FROM clause grammer to accept: SELECT * FROM my_func() AS m(colname1 type1, colname2 type1, ...) where m is the table alias, colname1, etc are the column names, and type1, etc are the column types.4. When typtype == 'p' and the function return type is RECORD, a list of column defs is required, and when typtype != 'p', it isdisallowed.5. A check was added to ensure that the tupdesc provide via the parser and the actual return tupdesc match in number and type ofattributes.When creating a function you can do: CREATE FUNCTION foo(text) RETURNS setof RECORD ...When using it you can do: SELECT * from foo(sqlstmt) AS (f1 int, f2 text, f3 timestamp) or SELECT * from foo(sqlstmt) AS f(f1 int, f2 text, f3 timestamp) or SELECT * from foo(sqlstmt) f(f1 int, f2 text, f3 timestamp)Included in the patches are adjustments to the regression test sql andexpected files, and documentation.p.s. This potentially solves (or at least improves) the issue of builtin Table Functions. They can be bootstrapped as returning RECORD, and we can wrap system views around them with properly specified column defs. For example: CREATE VIEW pg_settings AS SELECT s.name, s.setting FROM show_all_settings()AS s(name text, setting text); Then we can also add the UPDATE RULE that I previously posted to pg_settings, and have pg_settings act like a virtual table, allowing settings to be queried and set.Joe Conway
1 parent35d39ba commit9218689

File tree

19 files changed

+670
-273
lines changed

19 files changed

+670
-273
lines changed

‎doc/src/sgml/ref/select.sgml

Lines changed: 75 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$Header: /cvsroot/pgsql/doc/src/sgml/ref/select.sgml,v 1.54 2002/04/23 02:07:16 tgl Exp $
2+
$Header: /cvsroot/pgsql/doc/src/sgml/ref/select.sgml,v 1.55 2002/08/04 19:48:09 momjian Exp $
33
PostgreSQL documentation
44
-->
55

@@ -40,6 +40,12 @@ where <replaceable class="PARAMETER">from_item</replaceable> can be:
4040
( <replaceable class="PARAMETER">select</replaceable> )
4141
[ AS ] <replaceable class="PARAMETER">alias</replaceable> [ ( <replaceable class="PARAMETER">column_alias_list</replaceable> ) ]
4242
|
43+
<replaceable class="PARAMETER">table_function_name</replaceable> ( [ <replaceable class="parameter">argtype</replaceable> [, ...] ] )
44+
[ AS ] <replaceable class="PARAMETER">alias</replaceable> [ ( <replaceable class="PARAMETER">column_alias_list</replaceable> | <replaceable class="PARAMETER">column_definition_list</replaceable> ) ]
45+
|
46+
<replaceable class="PARAMETER">table_function_name</replaceable> ( [ <replaceable class="parameter">argtype</replaceable> [, ...] ] )
47+
AS ( <replaceable class="PARAMETER">column_definition_list</replaceable> )
48+
|
4349
<replaceable class="PARAMETER">from_item</replaceable> [ NATURAL ] <replaceable class="PARAMETER">join_type</replaceable> <replaceable class="PARAMETER">from_item</replaceable>
4450
[ ON <replaceable class="PARAMETER">join_condition</replaceable> | USING ( <replaceable class="PARAMETER">join_column_list</replaceable> ) ]
4551
</synopsis>
@@ -82,7 +88,7 @@ where <replaceable class="PARAMETER">from_item</replaceable> can be:
8288
<term><replaceable class="PARAMETER">from_item</replaceable></term>
8389
<listitem>
8490
<para>
85-
A table reference, sub-SELECT, or JOIN clause. See below for details.
91+
A table reference, sub-SELECT,table function,or JOIN clause. See below for details.
8692
</para>
8793
</listitem>
8894
</varlistentry>
@@ -156,6 +162,23 @@ where <replaceable class="PARAMETER">from_item</replaceable> can be:
156162
</para>
157163
</listitem>
158164
</varlistentry>
165+
166+
<varlistentry>
167+
<term><replaceable class="PARAMETER">table function</replaceable></term>
168+
<listitem>
169+
<para>
170+
A table function can appear in the FROM clause. This acts as though
171+
its output were created as a temporary table for the duration of
172+
this single SELECT command. An alias may also be used. If an alias is
173+
written, a column alias list can also be written to providesubstitute names
174+
for one or more columns of the table function. If the table function has been
175+
defined as returning the RECORD data type, an alias, or the keyword AS, must
176+
also be present, followed by a column definition list in the form
177+
( <replaceable class="PARAMETER">column_name</replaceable> <replaceable class="PARAMETER">data_type</replaceable> [, ... ] ).
178+
The column definition list must match the actual number and types returned by the function.
179+
</para>
180+
</listitem>
181+
</varlistentry>
159182

160183
<varlistentry>
161184
<term><replaceable class="PARAMETER">join_type</replaceable></term>
@@ -380,6 +403,19 @@ where <replaceable class="PARAMETER">from_item</replaceable> can be:
380403
grouping, aggregation, or sorting in a single query.
381404
</para>
382405

406+
<para>
407+
A FROM item can be a table function (i.e. a function that returns
408+
multiple rows and columns). When a table function is created, it may
409+
be defined to return a named scalar or composite data type (an existing
410+
scalar data type, or a table or view name), or it may be defined to return
411+
a RECORD data type. When a table function is defined to return RECORD, it
412+
must be followed in the FROM clause by an alias, or the keyword AS alone,
413+
and then by a parenthesized list of column names and types. This provides
414+
a query-time composite type definition. The FROM clause composite type
415+
must match the actual composite type returned from the function or an
416+
ERROR will be generated.
417+
</para>
418+
383419
<para>
384420
Finally, a FROM item can be a JOIN clause, which combines two simpler
385421
FROM items. (Use parentheses if necessary to determine the order
@@ -925,6 +961,43 @@ SELECT actors.name
925961
Warren Beatty
926962
Westward
927963
Woody Allen
964+
</programlisting>
965+
</para>
966+
967+
<para>
968+
This example shows how to use a table function, both with and without
969+
a column definition list.
970+
971+
<programlisting>
972+
distributors:
973+
did | name
974+
-----+--------------
975+
108 | Westward
976+
111 | Walt Disney
977+
112 | Warner Bros.
978+
...
979+
980+
CREATE FUNCTION distributors(int)
981+
RETURNS SETOF distributors AS '
982+
SELECT * FROM distributors WHERE did = $1;
983+
' LANGUAGE SQL;
984+
985+
SELECT * FROM distributors(111);
986+
did | name
987+
-----+-------------
988+
111 | Walt Disney
989+
(1 row)
990+
991+
CREATE FUNCTION distributors_2(int)
992+
RETURNS SETOF RECORD AS '
993+
SELECT * FROM distributors WHERE did = $1;
994+
' LANGUAGE SQL;
995+
996+
SELECT * FROM distributors_2(111) AS (f1 int, f2 text);
997+
f1 | f2
998+
-----+-------------
999+
111 | Walt Disney
1000+
(1 row)
9281001
</programlisting>
9291002
</para>
9301003
</refsect1>

‎src/backend/access/common/tupdesc.c

Lines changed: 40 additions & 27 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $Header: /cvsroot/pgsql/src/backend/access/common/tupdesc.c,v 1.83 2002/08/02 18:15:04 tgl Exp $
11+
* $Header: /cvsroot/pgsql/src/backend/access/common/tupdesc.c,v 1.84 2002/08/04 19:48:09 momjian Exp $
1212
*
1313
* NOTES
1414
* some of the executor utility code such as "ExecTypeFromTL" should be
@@ -24,6 +24,7 @@
2424
#include"catalog/namespace.h"
2525
#include"catalog/pg_type.h"
2626
#include"nodes/parsenodes.h"
27+
#include"parser/parse_relation.h"
2728
#include"parser/parse_type.h"
2829
#include"utils/builtins.h"
2930
#include"utils/syscache.h"
@@ -600,46 +601,53 @@ RelationNameGetTupleDesc(char *relname)
600601
TupleDesc
601602
TypeGetTupleDesc(Oidtypeoid,List*colaliases)
602603
{
603-
Oidrelid=typeidTypeRelid(typeoid);
604-
TupleDesctupdesc;
604+
charfunctyptype=typeid_get_typtype(typeoid);
605+
TupleDesctupdesc=NULL;
605606

606607
/*
607608
* Build a suitable tupledesc representing the output rows
608609
*/
609-
if (OidIsValid(relid))
610+
if (functyptype=='c')
610611
{
611612
/* Composite data type, i.e. a table's row type */
612-
Relationrel;
613-
intnatts;
614-
615-
rel=relation_open(relid,AccessShareLock);
616-
tupdesc=CreateTupleDescCopy(RelationGetDescr(rel));
617-
natts=tupdesc->natts;
618-
relation_close(rel,AccessShareLock);
613+
Oidrelid=typeidTypeRelid(typeoid);
619614

620-
/* check to see if we've given column aliases */
621-
if(colaliases!=NIL)
615+
if (OidIsValid(relid))
622616
{
623-
char*label;
624-
intvarattno;
617+
Relationrel;
618+
intnatts;
625619

626-
/* does the List length match the number of attributes */
627-
if (length(colaliases)!=natts)
628-
elog(ERROR,"TypeGetTupleDesc: number of aliases does not match number of attributes");
620+
rel=relation_open(relid,AccessShareLock);
621+
tupdesc=CreateTupleDescCopy(RelationGetDescr(rel));
622+
natts=tupdesc->natts;
623+
relation_close(rel,AccessShareLock);
629624

630-
/*OK, use the aliases instead */
631-
for (varattno=0;varattno<natts;varattno++)
625+
/*check to see if we've given column aliases */
626+
if(colaliases!=NIL)
632627
{
633-
label=strVal(nth(varattno,colaliases));
634-
635-
if (label!=NULL)
636-
namestrcpy(&(tupdesc->attrs[varattno]->attname),label);
637-
else
638-
MemSet(NameStr(tupdesc->attrs[varattno]->attname),0,NAMEDATALEN);
628+
char*label;
629+
intvarattno;
630+
631+
/* does the List length match the number of attributes */
632+
if (length(colaliases)!=natts)
633+
elog(ERROR,"TypeGetTupleDesc: number of aliases does not match number of attributes");
634+
635+
/* OK, use the aliases instead */
636+
for (varattno=0;varattno<natts;varattno++)
637+
{
638+
label=strVal(nth(varattno,colaliases));
639+
640+
if (label!=NULL)
641+
namestrcpy(&(tupdesc->attrs[varattno]->attname),label);
642+
else
643+
MemSet(NameStr(tupdesc->attrs[varattno]->attname),0,NAMEDATALEN);
644+
}
639645
}
640646
}
647+
else
648+
elog(ERROR,"Invalid return relation specified for function");
641649
}
642-
else
650+
elseif (functyptype=='b')
643651
{
644652
/* Must be a base data type, i.e. scalar */
645653
char*attname;
@@ -664,6 +672,11 @@ TypeGetTupleDesc(Oid typeoid, List *colaliases)
664672
0,
665673
false);
666674
}
675+
elseif (functyptype=='p'&&typeoid==RECORDOID)
676+
elog(ERROR,"Unable to determine tuple description for function"
677+
" returning \"record\"");
678+
else
679+
elog(ERROR,"Unknown kind of return type specified for function");
667680

668681
returntupdesc;
669682
}

0 commit comments

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