🌐 AI搜索 & 代理 主页
Skip to content

Commit 978129f

Browse files
tglsfdctglsfdc
committed
Document get_call_result_type() and friends; mark TypeGetTupleDesc()
and RelationNameGetTupleDesc() as deprecated; remove uses of the latter in the contrib library. Along the way, clean up crosstab() code and documentation a little.
1 parent b215fae commit 978129f

File tree

12 files changed

+373
-231
lines changed

12 files changed

+373
-231
lines changed

‎contrib/dblink/dblink.c‎

Lines changed: 34 additions & 42 deletions
Original file line numberDiff line numberDiff line change
@@ -454,14 +454,10 @@ dblink_fetch(PG_FUNCTION_ARGS)
454454
/* stuff done only on the first call of the function */
455455
if (SRF_IS_FIRSTCALL())
456456
{
457-
Oid functypeid;
458-
char functyptype;
459-
Oid funcid = fcinfo->flinfo->fn_oid;
460457
PGconn *conn = NULL;
461458
StringInfo str = makeStringInfo();
462459
char *curname = NULL;
463460
int howmany = 0;
464-
ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
465461
bool fail = true; /* default to backward compatible */
466462

467463
if (PG_NARGS() == 4)
@@ -554,27 +550,27 @@ dblink_fetch(PG_FUNCTION_ARGS)
554550
SRF_RETURN_DONE(funcctx);
555551
}
556552

557-
/* check typtype to see if we have a predetermined return type */
558-
functypeid = get_func_rettype(funcid);
559-
functyptype = get_typtype(functypeid);
560-
561-
if (functyptype == 'c')
562-
tupdesc = TypeGetTupleDesc(functypeid, NIL);
563-
else if (functypeid == RECORDOID)
553+
/* get a tuple descriptor for our result type */
554+
switch (get_call_result_type(fcinfo, NULL, &tupdesc))
564555
{
565-
if (!rsinfo || !IsA(rsinfo, ReturnSetInfo) ||
566-
rsinfo->expectedDesc == NULL)
556+
case TYPEFUNC_COMPOSITE:
557+
/* success */
558+
break;
559+
case TYPEFUNC_RECORD:
560+
/* failed to determine actual type of RECORD */
567561
ereport(ERROR,
568562
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
569-
errmsg("function returning record called in context "
570-
"that cannot accept type record")));
571-
572-
/* get the requested return tuple description */
573-
tupdesc = CreateTupleDescCopy(rsinfo->expectedDesc);
563+
errmsg("function returning record called in context "
564+
"that cannot accept type record")));
565+
break;
566+
default:
567+
/* result type isn't composite */
568+
elog(ERROR, "return type must be a row type");
569+
break;
574570
}
575-
else
576-
/* shouldn't happen */
577-
elog(ERROR, "return type must be a row type");
571+
572+
/* make sure we have a persistent copy of the tupdesc */
573+
tupdesc = CreateTupleDescCopy(tupdesc);
578574

579575
/* store needed metadata for subsequent calls */
580576
attinmeta = TupleDescGetAttInMetadata(tupdesc);
@@ -651,15 +647,11 @@ dblink_record(PG_FUNCTION_ARGS)
651647
/* stuff done only on the first call of the function */
652648
if (SRF_IS_FIRSTCALL())
653649
{
654-
Oid functypeid;
655-
char functyptype;
656-
Oid funcid = fcinfo->flinfo->fn_oid;
657650
PGconn *conn = NULL;
658651
char *connstr = NULL;
659652
char *sql = NULL;
660653
char *conname = NULL;
661654
remoteConn *rcon = NULL;
662-
ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
663655
bool fail = true; /* default to backward compatible */
664656

665657
/* create a function context for cross-call persistence */
@@ -756,29 +748,29 @@ dblink_record(PG_FUNCTION_ARGS)
756748
SRF_RETURN_DONE(funcctx);
757749
}
758750

759-
/* check typtype to see if we have a predetermined return type */
760-
functypeid = get_func_rettype(funcid);
761-
functyptype = get_typtype(functypeid);
762-
763751
if (!is_sql_cmd)
764752
{
765-
if (functyptype == 'c')
766-
tupdesc = TypeGetTupleDesc(functypeid, NIL);
767-
else if (functypeid == RECORDOID)
753+
/* get a tuple descriptor for our result type */
754+
switch (get_call_result_type(fcinfo, NULL, &tupdesc))
768755
{
769-
if (!rsinfo || !IsA(rsinfo, ReturnSetInfo) ||
770-
rsinfo->expectedDesc == NULL)
756+
case TYPEFUNC_COMPOSITE:
757+
/* success */
758+
break;
759+
case TYPEFUNC_RECORD:
760+
/* failed to determine actual type of RECORD */
771761
ereport(ERROR,
772762
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
773-
errmsg("function returning record called in context "
774-
"that cannot accept type record")));
775-
776-
/* get the requested return tuple description */
777-
tupdesc = CreateTupleDescCopy(rsinfo->expectedDesc);
763+
errmsg("function returning record called in context "
764+
"that cannot accept type record")));
765+
break;
766+
default:
767+
/* result type isn't composite */
768+
elog(ERROR, "return type must be a row type");
769+
break;
778770
}
779-
else
780-
/* shouldn't happen */
781-
elog(ERROR, "return type must be a row type");
771+
772+
/* make sure we have a persistent copy of the tupdesc */
773+
tupdesc = CreateTupleDescCopy(tupdesc);
782774
}
783775

784776
/* store needed metadata for subsequent calls */

‎contrib/pgstattuple/pgstattuple.c‎

Lines changed: 12 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
/*
2-
* $PostgreSQL: pgsql/contrib/pgstattuple/pgstattuple.c,v 1.18 2005/05/27 00:57:49 neilc Exp $
2+
* $PostgreSQL: pgsql/contrib/pgstattuple/pgstattuple.c,v 1.19 2005/05/30 23:09:06 tgl Exp $
33
*
44
* Copyright (c) 2001,2002 Tatsuo Ishii
55
*
@@ -38,7 +38,8 @@ PG_FUNCTION_INFO_V1(pgstattuplebyid);
3838
extern Datum pgstattuple(PG_FUNCTION_ARGS);
3939
extern Datum pgstattuplebyid(PG_FUNCTION_ARGS);
4040

41-
static Datum pgstattuple_real(Relation rel);
41+
static Datum pgstattuple_real(Relation rel, FunctionCallInfo fcinfo);
42+
4243

4344
/* ----------
4445
* pgstattuple:
@@ -50,7 +51,6 @@ static Datum pgstattuple_real(Relation rel);
5051
* ----------
5152
*/
5253

53-
#define DUMMY_TUPLE "public.pgstattuple_type"
5454
#define NCOLUMNS 9
5555
#define NCHARS 32
5656

@@ -66,7 +66,7 @@ pgstattuple(PG_FUNCTION_ARGS)
6666
relrv = makeRangeVarFromNameList(textToQualifiedNameList(relname));
6767
rel = heap_openrv(relrv, AccessShareLock);
6868

69-
result = pgstattuple_real(rel);
69+
result = pgstattuple_real(rel, fcinfo);
7070

7171
PG_RETURN_DATUM(result);
7272
}
@@ -81,7 +81,7 @@ pgstattuplebyid(PG_FUNCTION_ARGS)
8181
/* open relation */
8282
rel = heap_open(relid, AccessShareLock);
8383

84-
result = pgstattuple_real(rel);
84+
result = pgstattuple_real(rel, fcinfo);
8585

8686
PG_RETURN_DATUM(result);
8787
}
@@ -92,7 +92,7 @@ pgstattuplebyid(PG_FUNCTION_ARGS)
9292
* The real work occurs here
9393
*/
9494
static Datum
95-
pgstattuple_real(Relation rel)
95+
pgstattuple_real(Relation rel, FunctionCallInfo fcinfo)
9696
{
9797
HeapScanDesc scan;
9898
HeapTuple tuple;
@@ -115,10 +115,12 @@ pgstattuple_real(Relation rel)
115115
int i;
116116
Datum result;
117117

118-
/*
119-
* Build a tuple description for a pgstattupe_type tuple
120-
*/
121-
tupdesc = RelationNameGetTupleDesc(DUMMY_TUPLE);
118+
/* Build a tuple descriptor for our result type */
119+
if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
120+
elog(ERROR, "return type must be a row type");
121+
122+
/* make sure we have a persistent copy of the tupdesc */
123+
tupdesc = CreateTupleDescCopy(tupdesc);
122124

123125
/*
124126
* Generate attribute metadata needed later to produce tuples from raw

‎contrib/tablefunc/README.tablefunc‎

Lines changed: 70 additions & 40 deletions
Original file line numberDiff line numberDiff line change
@@ -57,11 +57,16 @@ Installation:
5757
but you can create additional crosstab functions per the instructions
5858
in the documentation below.
5959

60-
crosstab(text sql, N int)
60+
crosstab(text sql)
6161
- returns a set of row_name plus N category value columns
6262
- requires anonymous composite type syntax in the FROM clause. See
6363
the instructions in the documentation below.
6464

65+
crosstab(text sql, N int)
66+
- obsolete version of crosstab()
67+
- the argument N is now ignored, since the number of value columns
68+
is always determined by the calling query
69+
6570
connectby(text relname, text keyid_fld, text parent_keyid_fld
6671
[, text orderby_fld], text start_with, int max_depth
6772
[, text branch_delim])
@@ -133,7 +138,7 @@ Inputs
133138

134139
A SQL statement which produces the source set of data. The SQL statement
135140
must return one row_name column, one category column, and one value
136-
column.
141+
column. row_name and value must be of type text.
137142

138143
e.g. provided sql must produce a set something like:
139144

@@ -152,15 +157,15 @@ Outputs
152157

153158
Returns setof tablefunc_crosstab_N, which is defined by:
154159

155-
CREATE VIEW tablefunc_crosstab_N AS
156-
SELECT
157-
''::TEXT AS row_name,
158-
''::TEXT AS category_1,
159-
''::TEXT AS category_2,
160+
CREATE TYPE tablefunc_crosstab_N AS (
161+
row_name TEXT,
162+
category_1 TEXT,
163+
category_2 TEXT,
160164
.
161165
.
162166
.
163-
''::TEXT AS category_N;
167+
category_N TEXT
168+
);
164169

165170
for the default installed functions, where N is 2, 3, or 4.
166171

@@ -188,31 +193,9 @@ Notes
188193

189194
6. The installed defaults are for illustration purposes. You
190195
can create your own return types and functions based on the
191-
crosstab() function of the installed library.
192-
193-
The return type must have a first column that matches the data
194-
type of the sql set used as its source. The subsequent category
195-
columns must have the same data type as the value column of the
196-
sql result set.
197-
198-
Create a VIEW to define your return type, similar to the VIEWS
199-
in the provided installation script. Then define a unique function
200-
name accepting one text parameter and returning setof your_view_name.
201-
For example, if your source data produces row_names that are TEXT,
202-
and values that are FLOAT8, and you want 5 category columns:
196+
crosstab() function of the installed library. See below for
197+
details.
203198

204-
CREATE VIEW my_crosstab_float8_5_cols AS
205-
SELECT
206-
''::TEXT AS row_name,
207-
0::FLOAT8 AS category_1,
208-
0::FLOAT8 AS category_2,
209-
0::FLOAT8 AS category_3,
210-
0::FLOAT8 AS category_4,
211-
0::FLOAT8 AS category_5;
212-
213-
CREATE OR REPLACE FUNCTION crosstab_float8_5_cols(text)
214-
RETURNS setof my_crosstab_float8_5_cols
215-
AS '$libdir/tablefunc','crosstab' LANGUAGE 'c' STABLE STRICT;
216199

217200
Example usage
218201

@@ -241,11 +224,12 @@ select * from crosstab3(
241224
==================================================================
242225
Name
243226

244-
crosstab(text, int) - returns a set of row_name
245-
plus N category value columns
227+
crosstab(text) - returns a set of row_names plus category value columns
246228

247229
Synopsis
248230

231+
crosstab(text sql)
232+
249233
crosstab(text sql, int N)
250234

251235
Inputs
@@ -271,15 +255,16 @@ Inputs
271255

272256
N
273257

274-
number of category value columns
258+
Obsolete argument; ignored if supplied (formerly this had to match
259+
the number of category columns determined by the calling query)
275260

276261
Outputs
277262

278-
Returns setof record, which must defined with a column definition
263+
Returns setof record, which must be defined with a column definition
279264
in the FROM clause of the SELECT statement, e.g.:
280265

281266
SELECT *
282-
FROM crosstab(sql, 2) AS ct(row_name text, category_1 text, category_2 text);
267+
FROM crosstab(sql) AS ct(row_name text, category_1 text, category_2 text);
283268

284269
the example crosstab function produces a set something like:
285270
<== values columns ==>
@@ -292,9 +277,12 @@ Notes
292277

293278
1. The sql result must be ordered by 1,2.
294279

295-
2. The number of values columns is determined at run-time. The
296-
column definition provided in the FROM clause must provide for
297-
N + 1 columns of the proper data types.
280+
2. The number of values columns is determined by the column definition
281+
provided in the FROM clause. The FROM clause must define one
282+
row_name column (of the same datatype as the first result column
283+
of the sql query) followed by N category columns (of the same
284+
datatype as the third result column of the sql query). You can
285+
set up as many category columns as you wish.
298286

299287
3. Missing values (i.e. not enough adjacent rows of same row_name to
300288
fill the number of result values columns) are filled in with nulls.
@@ -304,6 +292,44 @@ Notes
304292

305293
5. Rows with all nulls in the values columns are skipped.
306294

295+
6. You can avoid always having to write out a FROM clause that defines the
296+
output columns by setting up a custom crosstab function that has
297+
the desired output row type wired into its definition.
298+
299+
There are two ways you can set up a custom crosstab function:
300+
301+
A. Create a composite type to define your return type, similar to the
302+
examples in the installation script. Then define a unique function
303+
name accepting one text parameter and returning setof your_type_name.
304+
For example, if your source data produces row_names that are TEXT,
305+
and values that are FLOAT8, and you want 5 category columns:
306+
307+
CREATE TYPE my_crosstab_float8_5_cols AS (
308+
row_name TEXT,
309+
category_1 FLOAT8,
310+
category_2 FLOAT8,
311+
category_3 FLOAT8,
312+
category_4 FLOAT8,
313+
category_5 FLOAT8
314+
);
315+
316+
CREATE OR REPLACE FUNCTION crosstab_float8_5_cols(text)
317+
RETURNS setof my_crosstab_float8_5_cols
318+
AS '$libdir/tablefunc','crosstab' LANGUAGE 'c' STABLE STRICT;
319+
320+
B. Use OUT parameters to define the return type implicitly.
321+
The same example could also be done this way:
322+
323+
CREATE OR REPLACE FUNCTION crosstab_float8_5_cols(IN text,
324+
OUT row_name TEXT,
325+
OUT category_1 FLOAT8,
326+
OUT category_2 FLOAT8,
327+
OUT category_3 FLOAT8,
328+
OUT category_4 FLOAT8,
329+
OUT category_5 FLOAT8)
330+
RETURNS setof record
331+
AS '$libdir/tablefunc','crosstab' LANGUAGE 'c' STABLE STRICT;
332+
307333

308334
Example usage
309335

@@ -418,6 +444,10 @@ Notes
418444

419445
5. Rows with a null row_name column are skipped.
420446

447+
6. You can create predefined functions to avoid having to write out
448+
the result column names/types in each query. See the examples
449+
for crosstab(text).
450+
421451

422452
Example usage
423453

0 commit comments

Comments
 (0)