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

Commit978129f

Browse files
committed
Document get_call_result_type() and friends; mark TypeGetTupleDesc()
and RelationNameGetTupleDesc() as deprecated; remove uses of thelatter in the contrib library. Along the way, clean up crosstab()code and documentation a little.
1 parentb215fae commit978129f

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-
Oidfunctypeid;
458-
charfunctyptype;
459-
Oidfuncid=fcinfo->flinfo->fn_oid;
460457
PGconn*conn=NULL;
461458
StringInfostr=makeStringInfo();
462459
char*curname=NULL;
463460
inthowmany=0;
464-
ReturnSetInfo*rsinfo= (ReturnSetInfo*)fcinfo->resultinfo;
465461
boolfail= 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-
elseif (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+
caseTYPEFUNC_COMPOSITE:
557+
/* success */
558+
break;
559+
caseTYPEFUNC_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-
Oidfunctypeid;
655-
charfunctyptype;
656-
Oidfuncid=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
boolfail= 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-
elseif (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+
caseTYPEFUNC_COMPOSITE:
757+
/* success */
758+
break;
759+
caseTYPEFUNC_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,2002Tatsuo Ishii
55
*
@@ -38,7 +38,8 @@ PG_FUNCTION_INFO_V1(pgstattuplebyid);
3838
externDatumpgstattuple(PG_FUNCTION_ARGS);
3939
externDatumpgstattuplebyid(PG_FUNCTION_ARGS);
4040

41-
staticDatumpgstattuple_real(Relationrel);
41+
staticDatumpgstattuple_real(Relationrel,FunctionCallInfofcinfo);
42+
4243

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

53-
#defineDUMMY_TUPLE "public.pgstattuple_type"
5454
#defineNCOLUMNS 9
5555
#defineNCHARS 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
staticDatum
95-
pgstattuple_real(Relationrel)
95+
pgstattuple_real(Relationrel,FunctionCallInfofcinfo)
9696
{
9797
HeapScanDescscan;
9898
HeapTupletuple;
@@ -115,10 +115,12 @@ pgstattuple_real(Relation rel)
115115
inti;
116116
Datumresult;
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 mustbedefined 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)
[8]ページ先頭
©2009-2025 Movatter.jp