From 978129f28e0cba0b6364df672fbd1ae88e3397c4 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Mon, 30 May 2005 23:09:07 +0000 Subject: [PATCH] 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. --- contrib/dblink/dblink.c | 76 ++++++------- contrib/pgstattuple/pgstattuple.c | 22 ++-- contrib/tablefunc/README.tablefunc | 110 ++++++++++++------- contrib/tablefunc/expected/tablefunc.out | 53 ++++++++- contrib/tablefunc/sql/tablefunc.sql | 45 +++++++- contrib/tablefunc/tablefunc.c | 132 +++++++---------------- contrib/tablefunc/tablefunc.sql.in | 8 ++ contrib/tsearch2/ts_stat.c | 11 +- contrib/tsearch2/wparser.c | 25 +++-- doc/src/sgml/xfunc.sgml | 100 +++++++++++++---- src/backend/utils/fmgr/funcapi.c | 11 +- src/include/funcapi.h | 11 +- 12 files changed, 373 insertions(+), 231 deletions(-) diff --git a/contrib/dblink/dblink.c b/contrib/dblink/dblink.c index b216dbeea9..61d992ed13 100644 --- a/contrib/dblink/dblink.c +++ b/contrib/dblink/dblink.c @@ -454,14 +454,10 @@ dblink_fetch(PG_FUNCTION_ARGS) /* stuff done only on the first call of the function */ if (SRF_IS_FIRSTCALL()) { - Oid functypeid; - char functyptype; - Oid funcid = fcinfo->flinfo->fn_oid; PGconn *conn = NULL; StringInfo str = makeStringInfo(); char *curname = NULL; int howmany = 0; - ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo; bool fail = true; /* default to backward compatible */ if (PG_NARGS() == 4) @@ -554,27 +550,27 @@ dblink_fetch(PG_FUNCTION_ARGS) SRF_RETURN_DONE(funcctx); } - /* check typtype to see if we have a predetermined return type */ - functypeid = get_func_rettype(funcid); - functyptype = get_typtype(functypeid); - - if (functyptype == 'c') - tupdesc = TypeGetTupleDesc(functypeid, NIL); - else if (functypeid == RECORDOID) + /* get a tuple descriptor for our result type */ + switch (get_call_result_type(fcinfo, NULL, &tupdesc)) { - if (!rsinfo || !IsA(rsinfo, ReturnSetInfo) || - rsinfo->expectedDesc == NULL) + case TYPEFUNC_COMPOSITE: + /* success */ + break; + case TYPEFUNC_RECORD: + /* failed to determine actual type of RECORD */ ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), - errmsg("function returning record called in context " - "that cannot accept type record"))); - - /* get the requested return tuple description */ - tupdesc = CreateTupleDescCopy(rsinfo->expectedDesc); + errmsg("function returning record called in context " + "that cannot accept type record"))); + break; + default: + /* result type isn't composite */ + elog(ERROR, "return type must be a row type"); + break; } - else - /* shouldn't happen */ - elog(ERROR, "return type must be a row type"); + + /* make sure we have a persistent copy of the tupdesc */ + tupdesc = CreateTupleDescCopy(tupdesc); /* store needed metadata for subsequent calls */ attinmeta = TupleDescGetAttInMetadata(tupdesc); @@ -651,15 +647,11 @@ dblink_record(PG_FUNCTION_ARGS) /* stuff done only on the first call of the function */ if (SRF_IS_FIRSTCALL()) { - Oid functypeid; - char functyptype; - Oid funcid = fcinfo->flinfo->fn_oid; PGconn *conn = NULL; char *connstr = NULL; char *sql = NULL; char *conname = NULL; remoteConn *rcon = NULL; - ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo; bool fail = true; /* default to backward compatible */ /* create a function context for cross-call persistence */ @@ -756,29 +748,29 @@ dblink_record(PG_FUNCTION_ARGS) SRF_RETURN_DONE(funcctx); } - /* check typtype to see if we have a predetermined return type */ - functypeid = get_func_rettype(funcid); - functyptype = get_typtype(functypeid); - if (!is_sql_cmd) { - if (functyptype == 'c') - tupdesc = TypeGetTupleDesc(functypeid, NIL); - else if (functypeid == RECORDOID) + /* get a tuple descriptor for our result type */ + switch (get_call_result_type(fcinfo, NULL, &tupdesc)) { - if (!rsinfo || !IsA(rsinfo, ReturnSetInfo) || - rsinfo->expectedDesc == NULL) + case TYPEFUNC_COMPOSITE: + /* success */ + break; + case TYPEFUNC_RECORD: + /* failed to determine actual type of RECORD */ ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), - errmsg("function returning record called in context " - "that cannot accept type record"))); - - /* get the requested return tuple description */ - tupdesc = CreateTupleDescCopy(rsinfo->expectedDesc); + errmsg("function returning record called in context " + "that cannot accept type record"))); + break; + default: + /* result type isn't composite */ + elog(ERROR, "return type must be a row type"); + break; } - else - /* shouldn't happen */ - elog(ERROR, "return type must be a row type"); + + /* make sure we have a persistent copy of the tupdesc */ + tupdesc = CreateTupleDescCopy(tupdesc); } /* store needed metadata for subsequent calls */ diff --git a/contrib/pgstattuple/pgstattuple.c b/contrib/pgstattuple/pgstattuple.c index db4b37def0..3056bd8978 100644 --- a/contrib/pgstattuple/pgstattuple.c +++ b/contrib/pgstattuple/pgstattuple.c @@ -1,5 +1,5 @@ /* - * $PostgreSQL: pgsql/contrib/pgstattuple/pgstattuple.c,v 1.18 2005/05/27 00:57:49 neilc Exp $ + * $PostgreSQL: pgsql/contrib/pgstattuple/pgstattuple.c,v 1.19 2005/05/30 23:09:06 tgl Exp $ * * Copyright (c) 2001,2002 Tatsuo Ishii * @@ -38,7 +38,8 @@ PG_FUNCTION_INFO_V1(pgstattuplebyid); extern Datum pgstattuple(PG_FUNCTION_ARGS); extern Datum pgstattuplebyid(PG_FUNCTION_ARGS); -static Datum pgstattuple_real(Relation rel); +static Datum pgstattuple_real(Relation rel, FunctionCallInfo fcinfo); + /* ---------- * pgstattuple: @@ -50,7 +51,6 @@ static Datum pgstattuple_real(Relation rel); * ---------- */ -#define DUMMY_TUPLE "public.pgstattuple_type" #define NCOLUMNS 9 #define NCHARS 32 @@ -66,7 +66,7 @@ pgstattuple(PG_FUNCTION_ARGS) relrv = makeRangeVarFromNameList(textToQualifiedNameList(relname)); rel = heap_openrv(relrv, AccessShareLock); - result = pgstattuple_real(rel); + result = pgstattuple_real(rel, fcinfo); PG_RETURN_DATUM(result); } @@ -81,7 +81,7 @@ pgstattuplebyid(PG_FUNCTION_ARGS) /* open relation */ rel = heap_open(relid, AccessShareLock); - result = pgstattuple_real(rel); + result = pgstattuple_real(rel, fcinfo); PG_RETURN_DATUM(result); } @@ -92,7 +92,7 @@ pgstattuplebyid(PG_FUNCTION_ARGS) * The real work occurs here */ static Datum -pgstattuple_real(Relation rel) +pgstattuple_real(Relation rel, FunctionCallInfo fcinfo) { HeapScanDesc scan; HeapTuple tuple; @@ -115,10 +115,12 @@ pgstattuple_real(Relation rel) int i; Datum result; - /* - * Build a tuple description for a pgstattupe_type tuple - */ - tupdesc = RelationNameGetTupleDesc(DUMMY_TUPLE); + /* Build a tuple descriptor for our result type */ + if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE) + elog(ERROR, "return type must be a row type"); + + /* make sure we have a persistent copy of the tupdesc */ + tupdesc = CreateTupleDescCopy(tupdesc); /* * Generate attribute metadata needed later to produce tuples from raw diff --git a/contrib/tablefunc/README.tablefunc b/contrib/tablefunc/README.tablefunc index 7097ef0095..63b1c17d8e 100644 --- a/contrib/tablefunc/README.tablefunc +++ b/contrib/tablefunc/README.tablefunc @@ -57,11 +57,16 @@ Installation: but you can create additional crosstab functions per the instructions in the documentation below. - crosstab(text sql, N int) + crosstab(text sql) - returns a set of row_name plus N category value columns - requires anonymous composite type syntax in the FROM clause. See the instructions in the documentation below. + crosstab(text sql, N int) + - obsolete version of crosstab() + - the argument N is now ignored, since the number of value columns + is always determined by the calling query + connectby(text relname, text keyid_fld, text parent_keyid_fld [, text orderby_fld], text start_with, int max_depth [, text branch_delim]) @@ -133,7 +138,7 @@ Inputs A SQL statement which produces the source set of data. The SQL statement must return one row_name column, one category column, and one value - column. + column. row_name and value must be of type text. e.g. provided sql must produce a set something like: @@ -152,15 +157,15 @@ Outputs Returns setof tablefunc_crosstab_N, which is defined by: - CREATE VIEW tablefunc_crosstab_N AS - SELECT - ''::TEXT AS row_name, - ''::TEXT AS category_1, - ''::TEXT AS category_2, + CREATE TYPE tablefunc_crosstab_N AS ( + row_name TEXT, + category_1 TEXT, + category_2 TEXT, . . . - ''::TEXT AS category_N; + category_N TEXT + ); for the default installed functions, where N is 2, 3, or 4. @@ -188,31 +193,9 @@ Notes 6. The installed defaults are for illustration purposes. You can create your own return types and functions based on the - crosstab() function of the installed library. - - The return type must have a first column that matches the data - type of the sql set used as its source. The subsequent category - columns must have the same data type as the value column of the - sql result set. - - Create a VIEW to define your return type, similar to the VIEWS - in the provided installation script. Then define a unique function - name accepting one text parameter and returning setof your_view_name. - For example, if your source data produces row_names that are TEXT, - and values that are FLOAT8, and you want 5 category columns: + crosstab() function of the installed library. See below for + details. - CREATE VIEW my_crosstab_float8_5_cols AS - SELECT - ''::TEXT AS row_name, - 0::FLOAT8 AS category_1, - 0::FLOAT8 AS category_2, - 0::FLOAT8 AS category_3, - 0::FLOAT8 AS category_4, - 0::FLOAT8 AS category_5; - - CREATE OR REPLACE FUNCTION crosstab_float8_5_cols(text) - RETURNS setof my_crosstab_float8_5_cols - AS '$libdir/tablefunc','crosstab' LANGUAGE 'c' STABLE STRICT; Example usage @@ -241,11 +224,12 @@ select * from crosstab3( ================================================================== Name -crosstab(text, int) - returns a set of row_name - plus N category value columns +crosstab(text) - returns a set of row_names plus category value columns Synopsis +crosstab(text sql) + crosstab(text sql, int N) Inputs @@ -271,15 +255,16 @@ Inputs N - number of category value columns + Obsolete argument; ignored if supplied (formerly this had to match + the number of category columns determined by the calling query) Outputs - Returns setof record, which must defined with a column definition + Returns setof record, which must be defined with a column definition in the FROM clause of the SELECT statement, e.g.: SELECT * - FROM crosstab(sql, 2) AS ct(row_name text, category_1 text, category_2 text); + FROM crosstab(sql) AS ct(row_name text, category_1 text, category_2 text); the example crosstab function produces a set something like: <== values columns ==> @@ -292,9 +277,12 @@ Notes 1. The sql result must be ordered by 1,2. - 2. The number of values columns is determined at run-time. The - column definition provided in the FROM clause must provide for - N + 1 columns of the proper data types. + 2. The number of values columns is determined by the column definition + provided in the FROM clause. The FROM clause must define one + row_name column (of the same datatype as the first result column + of the sql query) followed by N category columns (of the same + datatype as the third result column of the sql query). You can + set up as many category columns as you wish. 3. Missing values (i.e. not enough adjacent rows of same row_name to fill the number of result values columns) are filled in with nulls. @@ -304,6 +292,44 @@ Notes 5. Rows with all nulls in the values columns are skipped. + 6. You can avoid always having to write out a FROM clause that defines the + output columns by setting up a custom crosstab function that has + the desired output row type wired into its definition. + + There are two ways you can set up a custom crosstab function: + + A. Create a composite type to define your return type, similar to the + examples in the installation script. Then define a unique function + name accepting one text parameter and returning setof your_type_name. + For example, if your source data produces row_names that are TEXT, + and values that are FLOAT8, and you want 5 category columns: + + CREATE TYPE my_crosstab_float8_5_cols AS ( + row_name TEXT, + category_1 FLOAT8, + category_2 FLOAT8, + category_3 FLOAT8, + category_4 FLOAT8, + category_5 FLOAT8 + ); + + CREATE OR REPLACE FUNCTION crosstab_float8_5_cols(text) + RETURNS setof my_crosstab_float8_5_cols + AS '$libdir/tablefunc','crosstab' LANGUAGE 'c' STABLE STRICT; + + B. Use OUT parameters to define the return type implicitly. + The same example could also be done this way: + + CREATE OR REPLACE FUNCTION crosstab_float8_5_cols(IN text, + OUT row_name TEXT, + OUT category_1 FLOAT8, + OUT category_2 FLOAT8, + OUT category_3 FLOAT8, + OUT category_4 FLOAT8, + OUT category_5 FLOAT8) + RETURNS setof record + AS '$libdir/tablefunc','crosstab' LANGUAGE 'c' STABLE STRICT; + Example usage @@ -418,6 +444,10 @@ Notes 5. Rows with a null row_name column are skipped. + 6. You can create predefined functions to avoid having to write out + the result column names/types in each query. See the examples + for crosstab(text). + Example usage diff --git a/contrib/tablefunc/expected/tablefunc.out b/contrib/tablefunc/expected/tablefunc.out index d8f4c30a5d..50faf4390f 100644 --- a/contrib/tablefunc/expected/tablefunc.out +++ b/contrib/tablefunc/expected/tablefunc.out @@ -102,27 +102,40 @@ SELECT * FROM crosstab4('SELECT rowid, attribute, val FROM ct where rowclass = ' test4 | val4 | val5 | val6 | (2 rows) -SELECT * FROM crosstab('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' ORDER BY 1,2;', 2) AS c(rowid text, att1 text, att2 text); +SELECT * FROM crosstab('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' ORDER BY 1,2;') AS c(rowid text, att1 text, att2 text); rowid | att1 | att2 -------+------+------ test1 | val1 | val2 test2 | val5 | val6 (2 rows) -SELECT * FROM crosstab('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' ORDER BY 1,2;', 3) AS c(rowid text, att1 text, att2 text, att3 text); +SELECT * FROM crosstab('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' ORDER BY 1,2;') AS c(rowid text, att1 text, att2 text, att3 text); rowid | att1 | att2 | att3 -------+------+------+------ test1 | val1 | val2 | val3 test2 | val5 | val6 | val7 (2 rows) -SELECT * FROM crosstab('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' ORDER BY 1,2;', 4) AS c(rowid text, att1 text, att2 text, att3 text, att4 text); +SELECT * FROM crosstab('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' ORDER BY 1,2;') AS c(rowid text, att1 text, att2 text, att3 text, att4 text); rowid | att1 | att2 | att3 | att4 -------+------+------+------+------ test1 | val1 | val2 | val3 | val4 test2 | val5 | val6 | val7 | val8 (2 rows) +-- check it works with OUT parameters, too +CREATE FUNCTION crosstab_out(text, + OUT rowid text, OUT att1 text, OUT att2 text, OUT att3 text) +RETURNS setof record +AS '$libdir/tablefunc','crosstab' +LANGUAGE 'C' STABLE STRICT; +SELECT * FROM crosstab_out('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' ORDER BY 1,2;'); + rowid | att1 | att2 | att3 +-------+------+------+------ + test1 | val1 | val2 | val3 + test2 | val5 | val6 | val7 +(2 rows) + -- -- hash based crosstab -- @@ -211,6 +224,40 @@ AS c(rowid text, rowdt timestamp, temperature text, test_result text, test_start -------+-------+-------------+-------------+----------------+------- (0 rows) +-- check it works with a named result rowtype +create type my_crosstab_result as ( + rowid text, rowdt timestamp, + temperature int4, test_result text, test_startdate timestamp, volts float8); +CREATE FUNCTION crosstab_named(text, text) +RETURNS setof my_crosstab_result +AS '$libdir/tablefunc','crosstab_hash' +LANGUAGE 'C' STABLE STRICT; +SELECT * FROM crosstab_named( + 'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1', + 'SELECT DISTINCT attribute FROM cth ORDER BY 1'); + rowid | rowdt | temperature | test_result | test_startdate | volts +-------+--------------------------+-------------+-------------+--------------------------+-------- + test1 | Sat Mar 01 00:00:00 2003 | 42 | PASS | | 2.6987 + test2 | Sun Mar 02 00:00:00 2003 | 53 | FAIL | Sat Mar 01 00:00:00 2003 | 3.1234 +(2 rows) + +-- check it works with OUT parameters +CREATE FUNCTION crosstab_out(text, text, + OUT rowid text, OUT rowdt timestamp, + OUT temperature int4, OUT test_result text, + OUT test_startdate timestamp, OUT volts float8) +RETURNS setof record +AS '$libdir/tablefunc','crosstab_hash' +LANGUAGE 'C' STABLE STRICT; +SELECT * FROM crosstab_out( + 'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1', + 'SELECT DISTINCT attribute FROM cth ORDER BY 1'); + rowid | rowdt | temperature | test_result | test_startdate | volts +-------+--------------------------+-------------+-------------+--------------------------+-------- + test1 | Sat Mar 01 00:00:00 2003 | 42 | PASS | | 2.6987 + test2 | Sun Mar 02 00:00:00 2003 | 53 | FAIL | Sat Mar 01 00:00:00 2003 | 3.1234 +(2 rows) + -- -- connectby -- diff --git a/contrib/tablefunc/sql/tablefunc.sql b/contrib/tablefunc/sql/tablefunc.sql index c464acbd3b..3229086133 100644 --- a/contrib/tablefunc/sql/tablefunc.sql +++ b/contrib/tablefunc/sql/tablefunc.sql @@ -34,9 +34,19 @@ SELECT * FROM crosstab2('SELECT rowid, attribute, val FROM ct where rowclass = ' SELECT * FROM crosstab3('SELECT rowid, attribute, val FROM ct where rowclass = ''group2'' ORDER BY 1,2;'); SELECT * FROM crosstab4('SELECT rowid, attribute, val FROM ct where rowclass = ''group2'' ORDER BY 1,2;'); -SELECT * FROM crosstab('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' ORDER BY 1,2;', 2) AS c(rowid text, att1 text, att2 text); -SELECT * FROM crosstab('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' ORDER BY 1,2;', 3) AS c(rowid text, att1 text, att2 text, att3 text); -SELECT * FROM crosstab('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' ORDER BY 1,2;', 4) AS c(rowid text, att1 text, att2 text, att3 text, att4 text); +SELECT * FROM crosstab('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' ORDER BY 1,2;') AS c(rowid text, att1 text, att2 text); +SELECT * FROM crosstab('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' ORDER BY 1,2;') AS c(rowid text, att1 text, att2 text, att3 text); +SELECT * FROM crosstab('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' ORDER BY 1,2;') AS c(rowid text, att1 text, att2 text, att3 text, att4 text); + +-- check it works with OUT parameters, too + +CREATE FUNCTION crosstab_out(text, + OUT rowid text, OUT att1 text, OUT att2 text, OUT att3 text) +RETURNS setof record +AS '$libdir/tablefunc','crosstab' +LANGUAGE 'C' STABLE STRICT; + +SELECT * FROM crosstab_out('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' ORDER BY 1,2;'); -- -- hash based crosstab @@ -100,6 +110,35 @@ SELECT * FROM crosstab( 'SELECT DISTINCT attribute FROM cth WHERE false ORDER BY 1') AS c(rowid text, rowdt timestamp, temperature text, test_result text, test_startdate text, volts text); +-- check it works with a named result rowtype + +create type my_crosstab_result as ( + rowid text, rowdt timestamp, + temperature int4, test_result text, test_startdate timestamp, volts float8); + +CREATE FUNCTION crosstab_named(text, text) +RETURNS setof my_crosstab_result +AS '$libdir/tablefunc','crosstab_hash' +LANGUAGE 'C' STABLE STRICT; + +SELECT * FROM crosstab_named( + 'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1', + 'SELECT DISTINCT attribute FROM cth ORDER BY 1'); + +-- check it works with OUT parameters + +CREATE FUNCTION crosstab_out(text, text, + OUT rowid text, OUT rowdt timestamp, + OUT temperature int4, OUT test_result text, + OUT test_startdate timestamp, OUT volts float8) +RETURNS setof record +AS '$libdir/tablefunc','crosstab_hash' +LANGUAGE 'C' STABLE STRICT; + +SELECT * FROM crosstab_out( + 'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1', + 'SELECT DISTINCT attribute FROM cth ORDER BY 1'); + -- -- connectby -- diff --git a/contrib/tablefunc/tablefunc.c b/contrib/tablefunc/tablefunc.c index 0aa52b5281..6ab79ebeff 100644 --- a/contrib/tablefunc/tablefunc.c +++ b/contrib/tablefunc/tablefunc.c @@ -51,8 +51,6 @@ static void validateConnectbyTupleDesc(TupleDesc tupdesc, bool show_branch, bool static bool compatCrosstabTupleDescs(TupleDesc tupdesc1, TupleDesc tupdesc2); static bool compatConnectbyTupleDescs(TupleDesc tupdesc1, TupleDesc tupdesc2); static void get_normal_pair(float8 *x1, float8 *x2); -static TupleDesc make_crosstab_tupledesc(TupleDesc spi_tupdesc, - int num_categories); static Tuplestorestate *connectby(char *relname, char *key_fld, char *parent_key_fld, @@ -332,12 +330,14 @@ get_normal_pair(float8 *x1, float8 *x2) * NOTES: * 1. SQL result must be ordered by 1,2. * 2. The number of values columns depends on the tuple description - * of the function's declared return type. - * 2. Missing values (i.e. not enough adjacent rows of same rowid to + * of the function's declared return type. The return type's columns + * must match the datatypes of the SQL query's result. The datatype + * of the category column can be anything, however. + * 3. Missing values (i.e. not enough adjacent rows of same rowid to * fill the number of result values columns) are filled in with nulls. - * 3. Extra values (i.e. too many adjacent rows of same rowid to fill + * 4. Extra values (i.e. too many adjacent rows of same rowid to fill * the number of result values columns) are skipped. - * 4. Rows with all nulls in the values columns are skipped. + * 5. Rows with all nulls in the values columns are skipped. */ PG_FUNCTION_INFO_V1(crosstab); Datum @@ -360,10 +360,7 @@ crosstab(PG_FUNCTION_ARGS) if (SRF_IS_FIRSTCALL()) { char *sql = GET_STR(PG_GETARG_TEXT_P(0)); - Oid funcid = fcinfo->flinfo->fn_oid; - Oid functypeid; - char functyptype; - TupleDesc tupdesc = NULL; + TupleDesc tupdesc; int ret; int proc; @@ -391,20 +388,23 @@ crosstab(PG_FUNCTION_ARGS) spi_tuptable = SPI_tuptable; spi_tupdesc = spi_tuptable->tupdesc; - /* + /*---------- * The provided SQL query must always return three columns. * - * 1. rowname the label or identifier for each row in the final - * result 2. category the label or identifier for each column - * in the final result 3. values the value for each column - * in the final result + * 1. rowname + * the label or identifier for each row in the final result + * 2. category + * the label or identifier for each column in the final result + * 3. values + * the value for each column in the final result + *---------- */ if (spi_tupdesc->natts != 3) ereport(ERROR, (errcode(ERRCODE_INVALID_PARAMETER_VALUE), errmsg("invalid source data SQL statement"), - errdetail("The provided SQL must return 3 " \ - " columns; rowid, category, and values."))); + errdetail("The provided SQL must return 3 " + "columns: rowid, category, and values."))); } else { @@ -416,39 +416,31 @@ crosstab(PG_FUNCTION_ARGS) /* SPI switches context on us, so reset it */ MemoryContextSwitchTo(funcctx->multi_call_memory_ctx); - /* get the typeid that represents our return type */ - functypeid = get_func_rettype(funcid); - - /* check typtype to see if we have a predetermined return type */ - functyptype = get_typtype(functypeid); - - if (functyptype == 'c') + /* get a tuple descriptor for our result type */ + switch (get_call_result_type(fcinfo, NULL, &tupdesc)) { - /* Build a tuple description for a named composite type */ - tupdesc = TypeGetTupleDesc(functypeid, NIL); - } - else if (functypeid == RECORDOID) - { - if (fcinfo->nargs != 2) + case TYPEFUNC_COMPOSITE: + /* success */ + break; + case TYPEFUNC_RECORD: + /* failed to determine actual type of RECORD */ ereport(ERROR, - (errcode(ERRCODE_SYNTAX_ERROR), - errmsg("wrong number of arguments"))); - else - { - int num_categories = PG_GETARG_INT32(1); - - tupdesc = make_crosstab_tupledesc(spi_tupdesc, num_categories); - } + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("function returning record called in context " + "that cannot accept type record"))); + break; + default: + /* result type isn't composite */ + elog(ERROR, "return type must be a row type"); + break; } - else - ereport(ERROR, - (errcode(ERRCODE_SYNTAX_ERROR), - errmsg("return type must be a row type"))); + + /* make sure we have a persistent copy of the tupdesc */ + tupdesc = CreateTupleDescCopy(tupdesc); /* - * Check that return tupdesc is compatible with the one we got - * from ret_relname, at least based on number and type of - * attributes + * Check that return tupdesc is compatible with the data we got + * from SPI, at least based on number and type of attributes */ if (!compatCrosstabTupleDescs(tupdesc, spi_tupdesc)) ereport(ERROR, @@ -679,8 +671,8 @@ crosstab(PG_FUNCTION_ARGS) * 1. SQL result must be ordered by 1. * 2. The number of values columns depends on the tuple description * of the function's declared return type. - * 2. Missing values (i.e. missing category) are filled in with nulls. - * 3. Extra values (i.e. not in category results) are skipped. + * 3. Missing values (i.e. missing category) are filled in with nulls. + * 4. Extra values (i.e. not in category results) are skipped. */ PG_FUNCTION_INFO_V1(crosstab_hash); Datum @@ -1628,52 +1620,6 @@ compatCrosstabTupleDescs(TupleDesc ret_tupdesc, TupleDesc sql_tupdesc) return true; } -static TupleDesc -make_crosstab_tupledesc(TupleDesc spi_tupdesc, int num_categories) -{ - Form_pg_attribute sql_attr; - Oid sql_atttypid; - TupleDesc tupdesc; - int natts; - AttrNumber attnum; - char attname[NAMEDATALEN]; - int i; - - /* - * We need to build a tuple description with one column for the - * rowname, and num_categories columns for the values. Each must be of - * the same type as the corresponding spi result input column. - */ - natts = num_categories + 1; - tupdesc = CreateTemplateTupleDesc(natts, false); - - /* first the rowname column */ - attnum = 1; - - sql_attr = spi_tupdesc->attrs[0]; - sql_atttypid = sql_attr->atttypid; - - strcpy(attname, "rowname"); - - TupleDescInitEntry(tupdesc, attnum, attname, sql_atttypid, - -1, 0); - - /* now the category values columns */ - sql_attr = spi_tupdesc->attrs[2]; - sql_atttypid = sql_attr->atttypid; - - for (i = 0; i < num_categories; i++) - { - attnum++; - - sprintf(attname, "category_%d", i + 1); - TupleDescInitEntry(tupdesc, attnum, attname, sql_atttypid, - -1, 0); - } - - return tupdesc; -} - /* * Return a properly quoted literal value. * Uses quote_literal in quote.c diff --git a/contrib/tablefunc/tablefunc.sql.in b/contrib/tablefunc/tablefunc.sql.in index f3d5d3d8a1..fee9cb7e17 100644 --- a/contrib/tablefunc/tablefunc.sql.in +++ b/contrib/tablefunc/tablefunc.sql.in @@ -6,6 +6,13 @@ RETURNS setof float8 AS 'MODULE_PATHNAME','normal_rand' LANGUAGE 'C' VOLATILE STRICT; +-- the generic crosstab function: +CREATE OR REPLACE FUNCTION crosstab(text) +RETURNS setof record +AS 'MODULE_PATHNAME','crosstab' +LANGUAGE 'C' STABLE STRICT; + +-- examples of building custom type-specific crosstab functions: CREATE TYPE tablefunc_crosstab_2 AS ( row_name TEXT, @@ -45,6 +52,7 @@ RETURNS setof tablefunc_crosstab_4 AS 'MODULE_PATHNAME','crosstab' LANGUAGE 'C' STABLE STRICT; +-- obsolete: CREATE OR REPLACE FUNCTION crosstab(text,int) RETURNS setof record AS 'MODULE_PATHNAME','crosstab' diff --git a/contrib/tsearch2/ts_stat.c b/contrib/tsearch2/ts_stat.c index bb54c37a70..aaf43f8d74 100644 --- a/contrib/tsearch2/ts_stat.c +++ b/contrib/tsearch2/ts_stat.c @@ -330,7 +330,8 @@ typedef struct } StatStorage; static void -ts_setup_firstcall(FuncCallContext *funcctx, tsstat * stat) +ts_setup_firstcall(FunctionCallInfo fcinfo, FuncCallContext *funcctx, + tsstat * stat) { TupleDesc tupdesc; MemoryContext oldcontext; @@ -342,7 +343,9 @@ ts_setup_firstcall(FuncCallContext *funcctx, tsstat * stat) st->stat = palloc(stat->len); memcpy(st->stat, stat, stat->len); funcctx->user_fctx = (void *) st; - tupdesc = RelationNameGetTupleDesc("statinfo"); + if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE) + elog(ERROR, "return type must be a row type"); + tupdesc = CreateTupleDescCopy(tupdesc); funcctx->attinmeta = TupleDescGetAttInMetadata(tupdesc); MemoryContextSwitchTo(oldcontext); } @@ -399,7 +402,7 @@ ts_accum_finish(PG_FUNCTION_ARGS) if (SRF_IS_FIRSTCALL()) { funcctx = SRF_FIRSTCALL_INIT(); - ts_setup_firstcall(funcctx, (tsstat *) PG_GETARG_POINTER(0)); + ts_setup_firstcall(fcinfo, funcctx, (tsstat *) PG_GETARG_POINTER(0)); } funcctx = SRF_PERCALL_SETUP(); @@ -544,7 +547,7 @@ ts_stat(PG_FUNCTION_ARGS) PG_FREE_IF_COPY(txt, 0); if (PG_NARGS() > 1) PG_FREE_IF_COPY(ws, 1); - ts_setup_firstcall(funcctx, stat); + ts_setup_firstcall(fcinfo, funcctx, stat); SPI_finish(); } diff --git a/contrib/tsearch2/wparser.c b/contrib/tsearch2/wparser.c index 39cd673bb0..0f3ead832e 100644 --- a/contrib/tsearch2/wparser.c +++ b/contrib/tsearch2/wparser.c @@ -182,7 +182,7 @@ typedef struct } TypeStorage; static void -setup_firstcall(FuncCallContext *funcctx, Oid prsid) +setup_firstcall(FunctionCallInfo fcinfo, FuncCallContext *funcctx, Oid prsid) { TupleDesc tupdesc; MemoryContext oldcontext; @@ -197,7 +197,9 @@ setup_firstcall(FuncCallContext *funcctx, Oid prsid) OidFunctionCall1(prs->lextype, PointerGetDatum(prs->prs)) ); funcctx->user_fctx = (void *) st; - tupdesc = RelationNameGetTupleDesc("tokentype"); + if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE) + elog(ERROR, "return type must be a row type"); + tupdesc = CreateTupleDescCopy(tupdesc); funcctx->attinmeta = TupleDescGetAttInMetadata(tupdesc); MemoryContextSwitchTo(oldcontext); } @@ -250,7 +252,7 @@ token_type(PG_FUNCTION_ARGS) if (SRF_IS_FIRSTCALL()) { funcctx = SRF_FIRSTCALL_INIT(); - setup_firstcall(funcctx, PG_GETARG_OID(0)); + setup_firstcall(fcinfo, funcctx, PG_GETARG_OID(0)); } funcctx = SRF_PERCALL_SETUP(); @@ -274,7 +276,7 @@ token_type_byname(PG_FUNCTION_ARGS) text *name = PG_GETARG_TEXT_P(0); funcctx = SRF_FIRSTCALL_INIT(); - setup_firstcall(funcctx, name2id_prs(name)); + setup_firstcall(fcinfo, funcctx, name2id_prs(name)); PG_FREE_IF_COPY(name, 0); } @@ -299,7 +301,7 @@ token_type_current(PG_FUNCTION_ARGS) funcctx = SRF_FIRSTCALL_INIT(); if (current_parser_id == InvalidOid) current_parser_id = name2id_prs(char2text("default")); - setup_firstcall(funcctx, current_parser_id); + setup_firstcall(fcinfo, funcctx, current_parser_id); } funcctx = SRF_PERCALL_SETUP(); @@ -352,7 +354,8 @@ typedef struct static void -prs_setup_firstcall(FuncCallContext *funcctx, int prsid, text *txt) +prs_setup_firstcall(FunctionCallInfo fcinfo, FuncCallContext *funcctx, + int prsid, text *txt) { TupleDesc tupdesc; MemoryContext oldcontext; @@ -405,7 +408,9 @@ prs_setup_firstcall(FuncCallContext *funcctx, int prsid, text *txt) st->cur = 0; funcctx->user_fctx = (void *) st; - tupdesc = RelationNameGetTupleDesc("tokenout"); + if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE) + elog(ERROR, "return type must be a row type"); + tupdesc = CreateTupleDescCopy(tupdesc); funcctx->attinmeta = TupleDescGetAttInMetadata(tupdesc); MemoryContextSwitchTo(oldcontext); } @@ -458,7 +463,7 @@ parse(PG_FUNCTION_ARGS) text *txt = PG_GETARG_TEXT_P(1); funcctx = SRF_FIRSTCALL_INIT(); - prs_setup_firstcall(funcctx, PG_GETARG_OID(0), txt); + prs_setup_firstcall(fcinfo, funcctx, PG_GETARG_OID(0), txt); PG_FREE_IF_COPY(txt, 1); } @@ -484,7 +489,7 @@ parse_byname(PG_FUNCTION_ARGS) text *txt = PG_GETARG_TEXT_P(1); funcctx = SRF_FIRSTCALL_INIT(); - prs_setup_firstcall(funcctx, name2id_prs(name), txt); + prs_setup_firstcall(fcinfo, funcctx, name2id_prs(name), txt); PG_FREE_IF_COPY(name, 0); PG_FREE_IF_COPY(txt, 1); } @@ -513,7 +518,7 @@ parse_current(PG_FUNCTION_ARGS) funcctx = SRF_FIRSTCALL_INIT(); if (current_parser_id == InvalidOid) current_parser_id = name2id_prs(char2text("default")); - prs_setup_firstcall(funcctx, current_parser_id, txt); + prs_setup_firstcall(fcinfo, funcctx, current_parser_id, txt); PG_FREE_IF_COPY(txt, 0); } diff --git a/doc/src/sgml/xfunc.sgml b/doc/src/sgml/xfunc.sgml index 079773d0d4..0781bf4dba 100644 --- a/doc/src/sgml/xfunc.sgml +++ b/doc/src/sgml/xfunc.sgml @@ -1,5 +1,5 @@ @@ -2324,21 +2324,68 @@ CREATE FUNCTION c_overpaid(emp, integer) RETURNS boolean - Several helper functions are available for setting up the initial - TupleDesc. If you want to use a named composite type, - you can fetch the information from the system catalogs. Use + Several helper functions are available for setting up the needed + TupleDesc. The recommended way to do this in most + functions returning composite values is to call + +TypeFuncClass get_call_result_type(FunctionCallInfo fcinfo, + Oid *resultTypeId, + TupleDesc *resultTupleDesc) + + passing the same fcinfo struct passed to the calling function + itself. (This of course requires that you use the version-1 + calling conventions.) resultTypeId can be specified + as NULL or as the address of a local variable to receive the + function's result type OID. resultTupleDesc should be the + address of a local TupleDesc variable. Check that the + result is TYPEFUNC_COMPOSITE; if so, + resultTupleDesc has been filled with the needed + TupleDesc. (If it is not, you can report an error along + the lines of function returning record called in context that + cannot accept type record.) + + + + + get_call_result_type can resolve the actual type of a + polymorphic function result; so it is useful in functions that return + scalar polymorphic results, not only functions that return composites. + The resultTypeId output is primarily useful for functions + returning polymorphic scalars. + + + + + + get_call_result_type has a sibling + get_expr_result_type, which can be used to resolve the + expected output type for a function call represented by an expression + tree. This can be used when trying to determine the result type from + outside the function itself. There is also + get_func_result_type, which can be used when only the + function's OID is available. However these functions are not able + to deal with functions declared to return record, and + get_func_result_type cannot resolve polymorphic types, + so you should preferentially use get_call_result_type. + + + + + Older, now-deprecated functions for obtaining + TupleDescs are TupleDesc RelationNameGetTupleDesc(const char *relname) - to get a TupleDesc for a named relation, or + to get a TupleDesc for the row type of a named relation, + and TupleDesc TypeGetTupleDesc(Oid typeoid, List *colaliases) to get a TupleDesc based on a type OID. This can be used to get a TupleDesc for a base or - composite type. When writing a function that returns - record, the expected TupleDesc - must be passed in by the caller. + composite type. It will not work for a function that returns + record, however, and it cannot resolve polymorphic + types. @@ -2587,12 +2634,13 @@ my_set_returning_function(PG_FUNCTION_ARGS) - A complete example of a simple SRF returning a composite type looks like: + A complete example of a simple SRF returning a composite type + looks like: -PG_FUNCTION_INFO_V1(testpassbyval); +PG_FUNCTION_INFO_V1(retcomposite); Datum -testpassbyval(PG_FUNCTION_ARGS) +retcomposite(PG_FUNCTION_ARGS) { FuncCallContext *funcctx; int call_cntr; @@ -2614,8 +2662,12 @@ testpassbyval(PG_FUNCTION_ARGS) /* total number of tuples to be returned */ funcctx->max_calls = PG_GETARG_UINT32(0); - /* Build a tuple description for a __testpassbyval tuple */ - tupdesc = RelationNameGetTupleDesc("__testpassbyval"); + /* Build a tuple descriptor for our result type */ + if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("function returning record called in context " + "that cannot accept type record"))); /* * generate attribute metadata needed later to produce tuples from raw @@ -2675,14 +2727,25 @@ testpassbyval(PG_FUNCTION_ARGS) } - The SQL code to declare this function is: + One way to declare this function in SQL is: -CREATE TYPE __testpassbyval AS (f1 integer, f2 integer, f3 integer); +CREATE TYPE __retcomposite AS (f1 integer, f2 integer, f3 integer); -CREATE OR REPLACE FUNCTION testpassbyval(integer, integer) RETURNS SETOF __testpassbyval - AS 'filename', 'testpassbyval' +CREATE OR REPLACE FUNCTION retcomposite(integer, integer) + RETURNS SETOF __retcomposite + AS 'filename', 'retcomposite' + LANGUAGE C IMMUTABLE STRICT; + + A different way is to use OUT parameters: + +CREATE OR REPLACE FUNCTION retcomposite(IN integer, IN integer, + OUT f1 integer, OUT f2 integer, OUT f3 integer) + RETURNS SETOF record + AS 'filename', 'retcomposite' LANGUAGE C IMMUTABLE STRICT; + Notice that in this method the output type of the function is formally + an anonymous record type. @@ -2711,7 +2774,8 @@ CREATE OR REPLACE FUNCTION testpassbyval(integer, integer) RETURNS SETOF __testp information is not available. The structure flinfo is normally accessed as fcinfo->flinfo. The parameter argnum - is zero based. + is zero based. get_call_result_type can also be used + as an alternative to get_fn_expr_rettype. diff --git a/src/backend/utils/fmgr/funcapi.c b/src/backend/utils/fmgr/funcapi.c index 3ecb59eec8..43afc69322 100644 --- a/src/backend/utils/fmgr/funcapi.c +++ b/src/backend/utils/fmgr/funcapi.c @@ -7,7 +7,7 @@ * Copyright (c) 2002-2005, PostgreSQL Global Development Group * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/utils/fmgr/funcapi.c,v 1.22 2005/05/28 05:10:47 tgl Exp $ + * $PostgreSQL: pgsql/src/backend/utils/fmgr/funcapi.c,v 1.23 2005/05/30 23:09:07 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -822,6 +822,10 @@ build_function_result_tupdesc_d(Datum proallargtypes, * RelationNameGetTupleDesc * * Given a (possibly qualified) relation name, build a TupleDesc. + * + * Note: while this works as advertised, it's seldom the best way to + * build a tupdesc for a function's result type. It's kept around + * only for backwards compatibility with existing user-written code. */ TupleDesc RelationNameGetTupleDesc(const char *relname) @@ -844,7 +848,10 @@ RelationNameGetTupleDesc(const char *relname) /* * TypeGetTupleDesc * - * Given a type Oid, build a TupleDesc. + * Given a type Oid, build a TupleDesc. (In most cases you should be + * using get_call_result_type or one of its siblings instead of this + * routine, so that you can handle OUT parameters, RECORD result type, + * and polymorphic results.) * * If the type is composite, *and* a colaliases List is provided, *and* * the List is of natts length, use the aliases instead of the relation diff --git a/src/include/funcapi.h b/src/include/funcapi.h index 2b4bfa28c4..8acbf3aa3b 100644 --- a/src/include/funcapi.h +++ b/src/include/funcapi.h @@ -9,7 +9,7 @@ * * Copyright (c) 2002-2005, PostgreSQL Global Development Group * - * $PostgreSQL: pgsql/src/include/funcapi.h,v 1.17 2005/04/05 06:22:15 tgl Exp $ + * $PostgreSQL: pgsql/src/include/funcapi.h,v 1.18 2005/05/30 23:09:07 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -181,11 +181,6 @@ extern TupleDesc build_function_result_tupdesc_t(HeapTuple procTuple); * Support to ease writing functions returning composite types * * External declarations: - * TupleDesc RelationNameGetTupleDesc(const char *relname) - Use to get a - * TupleDesc based on a specified relation. - * TupleDesc TypeGetTupleDesc(Oid typeoid, List *colaliases) - Use to get a - * TupleDesc based on a type OID. This can be used to get - * a TupleDesc for a base (scalar) or composite (relation) type. * TupleDesc BlessTupleDesc(TupleDesc tupdesc) - "Bless" a completed tuple * descriptor so that it can be used to return properly labeled tuples. * You need to call this if you are going to use heap_formtuple directly. @@ -203,6 +198,10 @@ extern TupleDesc build_function_result_tupdesc_t(HeapTuple procTuple); * HeapTupleGetDatum(HeapTuple tuple) - convert a HeapTuple to a Datum. * * Obsolete routines and macros: + * TupleDesc RelationNameGetTupleDesc(const char *relname) - Use to get a + * TupleDesc based on a named relation. + * TupleDesc TypeGetTupleDesc(Oid typeoid, List *colaliases) - Use to get a + * TupleDesc based on a type OID. * TupleTableSlot *TupleDescGetSlot(TupleDesc tupdesc) - Builds a * TupleTableSlot, which is not needed anymore. * TupleGetDatum(TupleTableSlot *slot, HeapTuple tuple) - get a Datum -- 2.40.0