guc GetConfigOptionByNum and tablefunc API - minor changes

CVSROOT: /cvsroot
Module name: pgsql
Changes by: tgl(at)postgresql(dot)org 02/07/20 11:12:56

Modified files:
doc/src/sgml : release.sgml
src/backend/commands: explain.c
src/backend/utils/misc: guc.c
src/include/executor: executor.h
src/include/utils: guc.h
src/test/regress/expected: horology-no-DST-before-1970.out
horology-solaris-1947.out
horology.out

Log message:
Code review for SHOW output changes; fix horology expected files for
new SHOW output format.

Tom Lane wrote:
> CVSROOT: /cvsroot
> Module name: pgsql
> Changes by: tgl(at)postgresql(dot)org 02/07/20 11:12:56
>
> Modified files:
> doc/src/sgml : release.sgml
> src/backend/commands: explain.c
> src/backend/utils/misc: guc.c
> src/include/executor: executor.h
> src/include/utils: guc.h
> src/test/regress/expected: horology-no-DST-before-1970.out
> horology-solaris-1947.out
> horology.out
>
> Log message:
> Code review for SHOW output changes; fix horology expected files for
> new SHOW output format.

Thanks for reviewing and cleaning this up, Tom. I think I understand
most of your changes, but I wasn't sure why you changed
PointerGetDatum(PG_GETARG_TEXT_P(0))
to
PG_GETARG_DATUM(0)

Somewhere I got the impression that the former was preferred, although
the two are equivalent and the latter is more compact.

Joe

Joe Conway <mail(at)joeconway(dot)com> writes:
> Thanks for reviewing and cleaning this up, Tom. I think I understand
> most of your changes, but I wasn't sure why you changed
> PointerGetDatum(PG_GETARG_TEXT_P(0))
> to
> PG_GETARG_DATUM(0)

The former was okay, but seemed a little redundant; you have a Datum
already, why convert to Text pointer and back to Datum? There is
a runtime cost here, it's not only a cast: GETARG_TEXT_P implies a
detoasting check. Since text_out will do that anyway, it doesn't
seem necessary to do it here.

It's a minor judgment call, really; I would not have bothered to change
it if I hadn't been making nearby edits for other reasons.

One thing that possibly needs discussion is the handling of
GUC_NO_SHOW_ALL. I moved that test into the SHOW ALL code because
the intended behavior is for the marked variable to not be in the
SHOW ALL output at all, rather than be there with a null value as
your patch originally behaved. Now that was fine for SHOW ALL because
it can examine the config record directly anyway, but what of external
callers of GetConfigOptionByNum? There aren't any right now so I'm
kind of speculating in a vacuum about what they'll want. But it seems
possible that they will want to be able to discover whether the var is
marked NO_SHOW_ALL or not. Maybe that should be an additional return
variable from GetConfigOptionByNum, along the lines of

GetConfigOptionByNum(..., bool *noshow)
{
if (noshow)
*noshow = (conf->flags & GUC_NO_SHOW_ALL) ? true : false;
}

Any thoughts?

Oh btw: an Assert() verifying that the arg of GetConfigOptionByNum is
in range wouldn't be out of place, I think.

regards, tom lane

Tom Lane wrote:
> Joe Conway <mail(at)joeconway(dot)com> writes:
>
>>Thanks for reviewing and cleaning this up, Tom. I think I understand
>>most of your changes, but I wasn't sure why you changed
>> PointerGetDatum(PG_GETARG_TEXT_P(0))
>>to
>> PG_GETARG_DATUM(0)
>
>
> The former was okay, but seemed a little redundant; you have a Datum
> already, why convert to Text pointer and back to Datum? There is
> a runtime cost here, it's not only a cast: GETARG_TEXT_P implies a
> detoasting check. Since text_out will do that anyway, it doesn't
> seem necessary to do it here.

OK. Makes sense.

>
> One thing that possibly needs discussion is the handling of
> GUC_NO_SHOW_ALL. I moved that test into the SHOW ALL code because
> the intended behavior is for the marked variable to not be in the
> SHOW ALL output at all, rather than be there with a null value as
> your patch originally behaved. Now that was fine for SHOW ALL because
> it can examine the config record directly anyway, but what of external
> callers of GetConfigOptionByNum? There aren't any right now so I'm
> kind of speculating in a vacuum about what they'll want.

But there will be as soon as I submit contrib/tablefunc

> But it seems possible that they will want to be able to discover
> whether the var is marked NO_SHOW_ALL or not. Maybe that should be
> an additional return variable from GetConfigOptionByNum, along the
> lines of
>
> GetConfigOptionByNum(..., bool *noshow)
> {
> if (noshow)
> *noshow = (conf->flags & GUC_NO_SHOW_ALL) ? true : false;
> }
>
> Any thoughts?

I see seed and session_authorization as the only two (at least
currently) settings marked GUC_NO_SHOW_ALL. I tried searching the
archives, but I can't find an explanation anywhere as to why there are
some settings we don't want to see in SHOW ALL. You can still do:

test=# SHOW session_authorization;
session_authorization
-----------------------
postgres
(1 row)

and see the setting, so why leave it out of SHOW ALL results? Or is this
behavior an artifact of my patch?

in 7.2.1 i get:
test=# SHOW seed;
NOTICE: Seed for random number generator is unavailable
SHOW VARIABLE

and cvs:
test=# SHOW seed;
seed
-------------
unavailable
(1 row)

>
> Oh btw: an Assert() verifying that the arg of GetConfigOptionByNum is
> in range wouldn't be out of place, I think.
>

OK -- I'll add one if I end up modifying this again.

Thanks,

Joe

Joe Conway <mail(at)joeconway(dot)com> writes:
>> There aren't any right now so I'm
>> kind of speculating in a vacuum about what they'll want.

> But there will be as soon as I submit contrib/tablefunc

Okay, so the question is what tablefunc wants to do. I'd guess it
wants to duplicate the set of info returned by SHOW ALL.

> I see seed and session_authorization as the only two (at least
> currently) settings marked GUC_NO_SHOW_ALL. I tried searching the
> archives, but I can't find an explanation anywhere as to why there are
> some settings we don't want to see in SHOW ALL.

I put in GUC_NO_SHOW_ALL when I was hacking on GUC a few months ago
to make it able to implement the last few SET variables that had
one-of-a-kind behavior. One of those one-of-a-kind behaviors was that
some of them didn't show up in SHOW ALL. I suppose this is arguably
a bug and not really behavior we want to preserve --- although SHOW SEED
will *never* return anything useful and so it's not clear why SHOW ALL
should bother to show it.

If NO_SHOW_ALL bothers you, feel free to put its removal up to a
pghackers vote. I'm not wedded to it.

regards, tom lane

Tom Lane wrote:
> Joe Conway <mail(at)joeconway(dot)com> writes:
> Okay, so the question is what tablefunc wants to do. I'd guess it
> wants to duplicate the set of info returned by SHOW ALL.

Yes, I think that makes sense.

> I put in GUC_NO_SHOW_ALL when I was hacking on GUC a few months ago
> to make it able to implement the last few SET variables that had
> one-of-a-kind behavior. One of those one-of-a-kind behaviors was that
> some of them didn't show up in SHOW ALL. I suppose this is arguably
> a bug and not really behavior we want to preserve --- although SHOW SEED
> will *never* return anything useful and so it's not clear why SHOW ALL
> should bother to show it.
>
> If NO_SHOW_ALL bothers you, feel free to put its removal up to a
> pghackers vote. I'm not wedded to it.

It doesn't really bother me ;). I just didn't understand why it existed.

I do think that it is conceivable that we want to be able to suppress
the examination of some settings, but I would think that would apply to
SHOW just the same as SHOW ALL. It seems that anything I can SHOW should
be there when I do SHOW ALL. Maybe it should be GUC_NO_SHOW and apply to
both?

Joe

Joe Conway <mail(at)joeconway(dot)com> writes:
> I do think that it is conceivable that we want to be able to suppress
> the examination of some settings, but I would think that would apply to
> SHOW just the same as SHOW ALL. It seems that anything I can SHOW should
> be there when I do SHOW ALL. Maybe it should be GUC_NO_SHOW and apply to
> both?

Nah, that can be implemented by the show_hook subroutine refusing to
disclose anything. NO_SHOW_ALL is only useful for making SHOW ALL
vary from what SHOW does.

regards, tom lane

Tom Lane wrote:
> One thing that possibly needs discussion is the handling of
> GUC_NO_SHOW_ALL. I moved that test into the SHOW ALL code because
> the intended behavior is for the marked variable to not be in the
> SHOW ALL output at all, rather than be there with a null value as
> your patch originally behaved. Now that was fine for SHOW ALL because
> it can examine the config record directly anyway, but what of external
> callers of GetConfigOptionByNum? There aren't any right now so I'm
> kind of speculating in a vacuum about what they'll want. But it seems
> possible that they will want to be able to discover whether the var is
> marked NO_SHOW_ALL or not. Maybe that should be an additional return
> variable from GetConfigOptionByNum, along the lines of
>
> GetConfigOptionByNum(..., bool *noshow)
> {
> if (noshow)
> *noshow = (conf->flags & GUC_NO_SHOW_ALL) ? true : false;
> }
>
> Any thoughts?
>
> Oh btw: an Assert() verifying that the arg of GetConfigOptionByNum is
> in range wouldn't be out of place, I think.

Here are two patches. The guc_and_tablefunc patch addresses the two
changes mentioned above, and also adds a new function to the tablefunc
API. The tablefunc API change adds the following function:

* Oid foidGetTypeId(Oid foid) - Get a function's typeid given the
* function Oid. Use this together with TypeGetTupleDesc() to get a
* TupleDesc which is derived from the function's declared return type.

In the next post I'll send the contrib/tablefunc patch, which
illustrates the usage of this new function. Also attached is a doc patch
for this change. The doc patch also adds a function that I failed to
document previously.

If there are no objections, please apply.

Thanks,

Joe

Joe Conway <mail(at)joeconway(dot)com> writes:
> The tablefunc API change adds the following function:

> * Oid foidGetTypeId(Oid foid) - Get a function's typeid given the
> * function Oid.

Doesn't this duplicate get_func_rettype()? (Which is more clearly
named anyway; a function's type is a second-order concept IMHO...)

regards, tom lane

Tom Lane wrote:
> Doesn't this duplicate get_func_rettype()? (Which is more clearly
> named anyway; a function's type is a second-order concept IMHO...)

Yep -- I thought I had seen something that would do this, and I looked
for such an animal for a while, but didn't guess close enough to the
name to find it :(

OK -- three new patches coming.

Joe

Tom Lane wrote:
> Doesn't this duplicate get_func_rettype()? (Which is more clearly
> named anyway; a function's type is a second-order concept IMHO...)

Here's a second try at all three patches. I removed the foidGetTypeId()
function from funcapi.c, and replaced references to it in
contrib/tablefunc with get_func_rettype().

The only change to funcapi now is a minor addition to the comments in
funcapi.h and the funcapi doc for the previously mentioned undocumented
function.

As always, thanks for the review.

Joe

Joe Conway <mail(at)joeconway(dot)com> writes:
>> Doesn't this duplicate get_func_rettype()?

> Yep -- I thought I had seen something that would do this, and I looked
> for such an animal for a while, but didn't guess close enough to the
> name to find it :(

You didn't know where to look. Simple system-catalog lookup utilities
of this ilk live in lsyscache.c.

regards, tom lane

Your patch has been added to the PostgreSQL unapplied patches list at:

http://candle.pha.pa.us/cgi-bin/pgpatches

I will try to apply it within the next 48 hours.

---------------------------------------------------------------------------

Joe Conway wrote:
> Tom Lane wrote:
> > Doesn't this duplicate get_func_rettype()? (Which is more clearly
> > named anyway; a function's type is a second-order concept IMHO...)
>
> Here's a second try at all three patches. I removed the foidGetTypeId()
> function from funcapi.c, and replaced references to it in
> contrib/tablefunc with get_func_rettype().
>
> The only change to funcapi now is a minor addition to the comments in
> funcapi.h and the funcapi doc for the previously mentioned undocumented
> function.
>
> As always, thanks for the review.
>
> Joe
>
>

> Index: contrib/tablefunc/Makefile
> ===================================================================
> RCS file: contrib/tablefunc/Makefile
> diff -N contrib/tablefunc/Makefile
> *** /dev/null 1 Jan 1970 00:00:00 -0000
> --- contrib/tablefunc/Makefile 9 Jul 2002 03:34:24 -0000
> ***************
> *** 0 ****
> --- 1,9 ----
> + subdir = contrib/tablefunc
> + top_builddir = ../..
> + include $(top_builddir)/src/Makefile.global
> +
> + MODULES = tablefunc
> + DATA_built = tablefunc.sql
> + DOCS = README.tablefunc
> +
> + include $(top_srcdir)/contrib/contrib-global.mk
> Index: contrib/tablefunc/README.tablefunc
> ===================================================================
> RCS file: contrib/tablefunc/README.tablefunc
> diff -N contrib/tablefunc/README.tablefunc
> *** /dev/null 1 Jan 1970 00:00:00 -0000
> --- contrib/tablefunc/README.tablefunc 21 Jul 2002 02:50:53 -0000
> ***************
> *** 0 ****
> --- 1,272 ----
> + /*
> + * tablefunc
> + *
> + * Sample to demonstrate C functions which return setof scalar
> + * and setof composite.
> + * Joe Conway <mail(at)joeconway(dot)com>
> + *
> + * Copyright 2002 by PostgreSQL Global Development Group
> + *
> + * Permission to use, copy, modify, and distribute this software and its
> + * documentation for any purpose, without fee, and without a written agreement
> + * is hereby granted, provided that the above copyright notice and this
> + * paragraph and the following two paragraphs appear in all copies.
> + *
> + * IN NO EVENT SHALL THE AUTHORS OR DISTRIBUTORS BE LIABLE TO ANY PARTY FOR
> + * DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING
> + * LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS
> + * DOCUMENTATION, EVEN IF THE AUTHOR OR DISTRIBUTORS HAVE BEEN ADVISED OF THE
> + * POSSIBILITY OF SUCH DAMAGE.
> + *
> + * THE AUTHORS AND DISTRIBUTORS SPECIFICALLY DISCLAIM ANY WARRANTIES,
> + * INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY
> + * AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS
> + * ON AN "AS IS" BASIS, AND THE AUTHOR AND DISTRIBUTORS HAS NO OBLIGATIONS TO
> + * PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.
> + *
> + */
> + Version 0.1 (20 July, 2002):
> + First release
> +
> + Release Notes:
> +
> + Version 0.1
> + - initial release
> +
> + Installation:
> + Place these files in a directory called 'tablefunc' under 'contrib' in the
> + PostgreSQL source tree. Then run:
> +
> + make
> + make install
> +
> + You can use tablefunc.sql to create the functions in your database of choice, e.g.
> +
> + psql -U postgres template1 < tablefunc.sql
> +
> + installs following functions into database template1:
> +
> + show_all_settings()
> + - returns the same information as SHOW ALL, but as a query result
> +
> + normal_rand(int numvals, float8 mean, float8 stddev, int seed)
> + - returns a set of normally distributed float8 values
> +
> + crosstabN(text sql)
> + - returns a set of row_name plus N category value columns
> + - crosstab2(), crosstab3(), and crosstab4() are defined for you,
> + but you can create additional crosstab functions per the instructions
> + in the documentation below.
> +
> + Documentation
> + ==================================================================
> + Name
> +
> + show_all_settings() - returns the same information as SHOW ALL,
> + but as a query result.
> +
> + Synopsis
> +
> + show_all_settings()
> +
> + Inputs
> +
> + none
> +
> + Outputs
> +
> + Returns setof tablefunc_config_settings which is defined by:
> + CREATE VIEW tablefunc_config_settings AS
> + SELECT
> + ''::TEXT AS name,
> + ''::TEXT AS setting;
> +
> + Example usage
> +
> + test=# select * from show_all_settings();
> + name | setting
> + -------------------------------+---------------------------------------
> + australian_timezones | off
> + authentication_timeout | 60
> + checkpoint_segments | 3
> + .
> + .
> + .
> + wal_debug | 0
> + wal_files | 0
> + wal_sync_method | fdatasync
> + (94 rows)
> +
> + ==================================================================
> + Name
> +
> + normal_rand(int, float8, float8, int) - returns a set of normally
> + distributed float8 values
> +
> + Synopsis
> +
> + normal_rand(int numvals, float8 mean, float8 stddev, int seed)
> +
> + Inputs
> +
> + numvals
> + the number of random values to be returned from the function
> +
> + mean
> + the mean of the normal distribution of values
> +
> + stddev
> + the standard deviation of the normal distribution of values
> +
> + seed
> + a seed value for the pseudo-random number generator
> +
> + Outputs
> +
> + Returns setof float8, where the returned set of random values are normally
> + distributed (Gaussian distribution)
> +
> + Example usage
> +
> + test=# SELECT * FROM
> + test=# normal_rand(1000, 5, 3, EXTRACT(SECONDS FROM CURRENT_TIME(0))::int);
> + normal_rand
> + ----------------------
> + 1.56556322244898
> + 9.10040991424657
> + 5.36957140345079
> + -0.369151492880995
> + 0.283600703686639
> + .
> + .
> + .
> + 4.82992125404908
> + 9.71308014517282
> + 2.49639286969028
> + (1000 rows)
> +
> + Returns 1000 values with a mean of 5 and a standard deviation of 3.
> +
> + ==================================================================
> + Name
> +
> + crosstabN(text) - returns a set of row_name plus N category value columns
> +
> + Synopsis
> +
> + crosstabN(text sql)
> +
> + Inputs
> +
> + sql
> +
> + 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.
> +
> + e.g. provided sql must produce a set something like:
> +
> + row_name cat value
> + ----------+-------+-------
> + row1 cat1 val1
> + row1 cat2 val2
> + row1 cat3 val3
> + row1 cat4 val4
> + row2 cat1 val5
> + row2 cat2 val6
> + row2 cat3 val7
> + row2 cat4 val8
> +
> + 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,
> + .
> + .
> + .
> + ''::TEXT AS category_N;
> +
> + for the default installed functions, where N is 2, 3, or 4.
> +
> + e.g. the provided crosstab2 function produces a set something like:
> + <== values columns ==>
> + row_name category_1 category_2
> + ---------+------------+------------
> + row1 val1 val2
> + row2 val5 val6
> +
> + Notes
> +
> + 1. The 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.
> +
> + 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.
> +
> + 4. Extra values (i.e. too many adjacent rows of same row_name to fill
> + the number of result values columns) are skipped.
> +
> + 5. Rows with all nulls in the values columns are skipped.
> +
> + 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:
> +
> + 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
> +
> + create table ct(id serial, rowclass text, rowid text, attribute text, value text);
> + insert into ct(rowclass, rowid, attribute, value) values('group1','test1','att1','val1');
> + insert into ct(rowclass, rowid, attribute, value) values('group1','test1','att2','val2');
> + insert into ct(rowclass, rowid, attribute, value) values('group1','test1','att3','val3');
> + insert into ct(rowclass, rowid, attribute, value) values('group1','test1','att4','val4');
> + insert into ct(rowclass, rowid, attribute, value) values('group1','test2','att1','val5');
> + insert into ct(rowclass, rowid, attribute, value) values('group1','test2','att2','val6');
> + insert into ct(rowclass, rowid, attribute, value) values('group1','test2','att3','val7');
> + insert into ct(rowclass, rowid, attribute, value) values('group1','test2','att4','val8');
> +
> + select * from crosstab3(
> + 'select rowid, attribute, value
> + from ct
> + where rowclass = ''group1''
> + and (attribute = ''att2'' or attribute = ''att3'') order by 1,2;');
> +
> + row_name | category_1 | category_2 | category_3
> + ----------+------------+------------+------------
> + test1 | val2 | val3 |
> + test2 | val6 | val7 |
> + (2 rows)
> +
> + ==================================================================
> + -- Joe Conway
> +
> Index: contrib/tablefunc/tablefunc-test.sql
> ===================================================================
> RCS file: contrib/tablefunc/tablefunc-test.sql
> diff -N contrib/tablefunc/tablefunc-test.sql
> *** /dev/null 1 Jan 1970 00:00:00 -0000
> --- contrib/tablefunc/tablefunc-test.sql 21 Jul 2002 03:01:13 -0000
> ***************
> *** 0 ****
> --- 1,47 ----
> + --
> + -- show_all_settings()
> + --
> + SELECT * FROM show_all_settings();
> +
> + --
> + -- normal_rand()
> + --
> + SELECT * FROM normal_rand(100, 250, 5, EXTRACT(SECONDS FROM CURRENT_TIME(0))::int);
> +
> + --
> + -- crosstab()
> + --
> + create table ct(id serial, rowclass text, rowid text, attribute text, value text);
> +
> + insert into ct(rowclass, rowid, attribute, value) values('group1','test1','att1','val1');
> + insert into ct(rowclass, rowid, attribute, value) values('group1','test1','att2','val2');
> + insert into ct(rowclass, rowid, attribute, value) values('group1','test1','att3','val3');
> + insert into ct(rowclass, rowid, attribute, value) values('group1','test1','att4','val4');
> + insert into ct(rowclass, rowid, attribute, value) values('group1','test2','att1','val5');
> + insert into ct(rowclass, rowid, attribute, value) values('group1','test2','att2','val6');
> + insert into ct(rowclass, rowid, attribute, value) values('group1','test2','att3','val7');
> + insert into ct(rowclass, rowid, attribute, value) values('group1','test2','att4','val8');
> + insert into ct(rowclass, rowid, attribute, value) values('group2','test3','att1','val1');
> + insert into ct(rowclass, rowid, attribute, value) values('group2','test3','att2','val2');
> + insert into ct(rowclass, rowid, attribute, value) values('group2','test3','att3','val3');
> + insert into ct(rowclass, rowid, attribute, value) values('group2','test4','att1','val4');
> + insert into ct(rowclass, rowid, attribute, value) values('group2','test4','att2','val5');
> + insert into ct(rowclass, rowid, attribute, value) values('group2','test4','att3','val6');
> +
> + select * from crosstab2('select rowid, attribute, value from ct where rowclass = ''group1'' and (attribute = ''att2'' or attribute = ''att3'') order by 1,2;');
> + select * from crosstab3('select rowid, attribute, value from ct where rowclass = ''group1'' and (attribute = ''att2'' or attribute = ''att3'') order by 1,2;');
> + select * from crosstab4('select rowid, attribute, value from ct where rowclass = ''group1'' and (attribute = ''att2'' or attribute = ''att3'') order by 1,2;');
> +
> + select * from crosstab2('select rowid, attribute, value from ct where rowclass = ''group1'' order by 1,2;');
> + select * from crosstab3('select rowid, attribute, value from ct where rowclass = ''group1'' order by 1,2;');
> + select * from crosstab4('select rowid, attribute, value from ct where rowclass = ''group1'' order by 1,2;');
> +
> + select * from crosstab2('select rowid, attribute, value from ct where rowclass = ''group2'' and (attribute = ''att1'' or attribute = ''att2'') order by 1,2;');
> + select * from crosstab3('select rowid, attribute, value from ct where rowclass = ''group2'' and (attribute = ''att1'' or attribute = ''att2'') order by 1,2;');
> + select * from crosstab4('select rowid, attribute, value from ct where rowclass = ''group2'' and (attribute = ''att1'' or attribute = ''att2'') order by 1,2;');
> +
> + select * from crosstab2('select rowid, attribute, value from ct where rowclass = ''group2'' order by 1,2;');
> + select * from crosstab3('select rowid, attribute, value from ct where rowclass = ''group2'' order by 1,2;');
> + select * from crosstab4('select rowid, attribute, value from ct where rowclass = ''group2'' order by 1,2;');
> +
> +
> Index: contrib/tablefunc/tablefunc.c
> ===================================================================
> RCS file: contrib/tablefunc/tablefunc.c
> diff -N contrib/tablefunc/tablefunc.c
> *** /dev/null 1 Jan 1970 00:00:00 -0000
> --- contrib/tablefunc/tablefunc.c 21 Jul 2002 05:36:00 -0000
> ***************
> *** 0 ****
> --- 1,665 ----
> + /*
> + * tablefunc
> + *
> + * Sample to demonstrate C functions which return setof scalar
> + * and setof composite.
> + * Joe Conway <mail(at)joeconway(dot)com>
> + *
> + * Copyright 2002 by PostgreSQL Global Development Group
> + *
> + * Permission to use, copy, modify, and distribute this software and its
> + * documentation for any purpose, without fee, and without a written agreement
> + * is hereby granted, provided that the above copyright notice and this
> + * paragraph and the following two paragraphs appear in all copies.
> + *
> + * IN NO EVENT SHALL THE AUTHORS OR DISTRIBUTORS BE LIABLE TO ANY PARTY FOR
> + * DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING
> + * LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS
> + * DOCUMENTATION, EVEN IF THE AUTHOR OR DISTRIBUTORS HAVE BEEN ADVISED OF THE
> + * POSSIBILITY OF SUCH DAMAGE.
> + *
> + * THE AUTHORS AND DISTRIBUTORS SPECIFICALLY DISCLAIM ANY WARRANTIES,
> + * INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY
> + * AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS
> + * ON AN "AS IS" BASIS, AND THE AUTHOR AND DISTRIBUTORS HAS NO OBLIGATIONS TO
> + * PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.
> + *
> + */
> + #include <stdlib.h>
> + #include <math.h>
> +
> + #include "postgres.h"
> +
> + #include "fmgr.h"
> + #include "funcapi.h"
> + #include "executor/spi.h"
> + #include "utils/builtins.h"
> + #include "utils/guc.h"
> + #include "utils/lsyscache.h"
> +
> + #include "tablefunc.h"
> +
> + static bool compatTupleDescs(TupleDesc tupdesc1, TupleDesc tupdesc2);
> + static void get_normal_pair(float8 *x1, float8 *x2);
> +
> + typedef struct
> + {
> + float8 mean; /* mean of the distribution */
> + float8 stddev; /* stddev of the distribution */
> + float8 carry_val; /* hold second generated value */
> + bool use_carry; /* use second generated value */
> + } normal_rand_fctx;
> +
> + typedef struct
> + {
> + SPITupleTable *spi_tuptable; /* sql results from user query */
> + char *lastrowid; /* rowid of the last tuple sent */
> + } crosstab_fctx;
> +
> + #define GET_TEXT(cstrp) DatumGetTextP(DirectFunctionCall1(textin, CStringGetDatum(cstrp)))
> + #define GET_STR(textp) DatumGetCString(DirectFunctionCall1(textout, PointerGetDatum(textp)))
> + #define xpfree(var_) \
> + do { \
> + if (var_ != NULL) \
> + { \
> + pfree(var_); \
> + var_ = NULL; \
> + } \
> + } while (0)
> +
> + /*
> + * show_all_settings - equiv to SHOW ALL command but implemented as
> + * a Table Function.
> + */
> + PG_FUNCTION_INFO_V1(show_all_settings);
> + Datum
> + show_all_settings(PG_FUNCTION_ARGS)
> + {
> + FuncCallContext *funcctx;
> + TupleDesc tupdesc;
> + int call_cntr;
> + int max_calls;
> + TupleTableSlot *slot;
> + AttInMetadata *attinmeta;
> +
> + /* stuff done only on the first call of the function */
> + if(SRF_IS_FIRSTCALL())
> + {
> + Oid funcid = fcinfo->flinfo->fn_oid;
> + Oid functypeid;
> +
> + /* create a function context for cross-call persistence */
> + funcctx = SRF_FIRSTCALL_INIT();
> +
> + /* get the typeid that represents our return type */
> + functypeid = get_func_rettype(funcid);
> +
> + /* Build a tuple description for a funcrelid tuple */
> + tupdesc = TypeGetTupleDesc(functypeid, NIL);
> +
> + /* allocate a slot for a tuple with this tupdesc */
> + slot = TupleDescGetSlot(tupdesc);
> +
> + /* assign slot to function context */
> + funcctx->slot = slot;
> +
> + /*
> + * Generate attribute metadata needed later to produce tuples from raw
> + * C strings
> + */
> + attinmeta = TupleDescGetAttInMetadata(tupdesc);
> + funcctx->attinmeta = attinmeta;
> +
> + /* total number of tuples to be returned */
> + funcctx->max_calls = GetNumConfigOptions();
> + }
> +
> + /* stuff done on every call of the function */
> + funcctx = SRF_PERCALL_SETUP();
> +
> + call_cntr = funcctx->call_cntr;
> + max_calls = funcctx->max_calls;
> + slot = funcctx->slot;
> + attinmeta = funcctx->attinmeta;
> +
> + if (call_cntr < max_calls) /* do when there is more left to send */
> + {
> + char **values;
> + char *varname;
> + char *varval;
> + bool noshow;
> + HeapTuple tuple;
> + Datum result;
> +
> + /*
> + * Get the next visible GUC variable name and value
> + */
> + do
> + {
> + varval = GetConfigOptionByNum(call_cntr, (const char **) &varname, &noshow);
> + if (noshow)
> + {
> + /* varval is a palloc'd copy, so free it */
> + xpfree(varval);
> +
> + /* bump the counter and get the next config setting */
> + call_cntr = ++funcctx->call_cntr;
> +
> + /* make sure we haven't gone too far now */
> + if (call_cntr >= max_calls)
> + SRF_RETURN_DONE(funcctx);
> + }
> + } while (noshow);
> +
> + /*
> + * Prepare a values array for storage in our slot.
> + * This should be an array of C strings which will
> + * be processed later by the appropriate "in" functions.
> + */
> + values = (char **) palloc(2 * sizeof(char *));
> + values[0] = pstrdup(varname);
> + values[1] = varval; /* varval is already a palloc'd copy */
> +
> + /* build a tuple */
> + tuple = BuildTupleFromCStrings(attinmeta, values);
> +
> + /* make the tuple into a datum */
> + result = TupleGetDatum(slot, tuple);
> +
> + /* Clean up */
> + xpfree(values[0]);
> + xpfree(values[1]);
> + xpfree(values);
> +
> + SRF_RETURN_NEXT(funcctx, result);
> + }
> + else /* do when there is no more left */
> + {
> + SRF_RETURN_DONE(funcctx);
> + }
> + }
> +
> + /*
> + * normal_rand - return requested number of random values
> + * with a Gaussian (Normal) distribution.
> + *
> + * inputs are int numvals, float8 lower_bound, and float8 upper_bound
> + * returns float8
> + */
> + PG_FUNCTION_INFO_V1(normal_rand);
> + Datum
> + normal_rand(PG_FUNCTION_ARGS)
> + {
> + FuncCallContext *funcctx;
> + int call_cntr;
> + int max_calls;
> + normal_rand_fctx *fctx;
> + float8 mean;
> + float8 stddev;
> + float8 carry_val;
> + bool use_carry;
> +
> + /* stuff done only on the first call of the function */
> + if(SRF_IS_FIRSTCALL())
> + {
> + /* create a function context for cross-call persistence */
> + funcctx = SRF_FIRSTCALL_INIT();
> +
> + /* total number of tuples to be returned */
> + funcctx->max_calls = PG_GETARG_UINT32(0);
> +
> + /* allocate memory for user context */
> + fctx = (normal_rand_fctx *) palloc(sizeof(normal_rand_fctx));
> +
> + /*
> + * Use fctx to keep track of upper and lower bounds
> + * from call to call. It will also be used to carry over
> + * the spare value we get from the Box-Muller algorithm
> + * so that we only actually calculate a new value every
> + * other call.
> + */
> + fctx->mean = PG_GETARG_FLOAT8(1);
> + fctx->stddev = PG_GETARG_FLOAT8(2);
> + fctx->carry_val = 0;
> + fctx->use_carry = false;
> +
> + funcctx->user_fctx = fctx;
> +
> + /*
> + * we might actually get passed a negative number, but for this
> + * purpose it doesn't matter, just cast it as an unsigned value
> + */
> + srandom(PG_GETARG_UINT32(3));
> + }
> +
> + /* stuff done on every call of the function */
> + funcctx = SRF_PERCALL_SETUP();
> +
> + call_cntr = funcctx->call_cntr;
> + max_calls = funcctx->max_calls;
> + fctx = funcctx->user_fctx;
> + mean = fctx->mean;
> + stddev = fctx->stddev;
> + carry_val = fctx->carry_val;
> + use_carry = fctx->use_carry;
> +
> + if (call_cntr < max_calls) /* do when there is more left to send */
> + {
> + float8 result;
> +
> + if(use_carry)
> + {
> + /*
> + * reset use_carry and use second value obtained on last pass
> + */
> + fctx->use_carry = false;
> + result = carry_val;
> + }
> + else
> + {
> + float8 normval_1;
> + float8 normval_2;
> +
> + /* Get the next two normal values */
> + get_normal_pair(&normval_1, &normval_2);
> +
> + /* use the first */
> + result = mean + (stddev * normval_1);
> +
> + /* and save the second */
> + fctx->carry_val = mean + (stddev * normval_2);
> + fctx->use_carry = true;
> + }
> +
> + /* send the result */
> + SRF_RETURN_NEXT(funcctx, Float8GetDatum(result));
> + }
> + else /* do when there is no more left */
> + {
> + SRF_RETURN_DONE(funcctx);
> + }
> + }
> +
> + /*
> + * get_normal_pair()
> + * Assigns normally distributed (Gaussian) values to a pair of provided
> + * parameters, with mean 0, standard deviation 1.
> + *
> + * This routine implements Algorithm P (Polar method for normal deviates)
> + * from Knuth's _The_Art_of_Computer_Programming_, Volume 2, 3rd ed., pages
> + * 122-126. Knuth cites his source as "The polar method", G. E. P. Box, M. E.
> + * Muller, and G. Marsaglia, _Annals_Math,_Stat._ 29 (1958), 610-611.
> + *
> + */
> + static void
> + get_normal_pair(float8 *x1, float8 *x2)
> + {
> + float8 u1, u2, v1, v2, s;
> +
> + for(;;)
> + {
> + u1 = (float8) random() / (float8) RAND_MAX;
> + u2 = (float8) random() / (float8) RAND_MAX;
> +
> + v1 = (2.0 * u1) - 1.0;
> + v2 = (2.0 * u2) - 1.0;
> +
> + s = pow(v1, 2) + pow(v2, 2);
> +
> + if (s >= 1.0)
> + continue;
> +
> + if (s == 0)
> + {
> + *x1 = 0;
> + *x2 = 0;
> + }
> + else
> + {
> + *x1 = v1 * sqrt((-2.0 * log(s)) / s);
> + *x2 = v2 * sqrt((-2.0 * log(s)) / s);
> + }
> +
> + return;
> + }
> + }
> +
> + /*
> + * crosstab - create a crosstab of rowids and values columns from a
> + * SQL statement returning one rowid column, one category column,
> + * and one value column.
> + *
> + * e.g. given sql which produces:
> + *
> + * rowid cat value
> + * ------+-------+-------
> + * row1 cat1 val1
> + * row1 cat2 val2
> + * row1 cat3 val3
> + * row1 cat4 val4
> + * row2 cat1 val5
> + * row2 cat2 val6
> + * row2 cat3 val7
> + * row2 cat4 val8
> + *
> + * crosstab returns:
> + * <===== values columns =====>
> + * rowid cat1 cat2 cat3 cat4
> + * ------+-------+-------+-------+-------
> + * row1 val1 val2 val3 val4
> + * row2 val5 val6 val7 val8
> + *
> + * 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
> + * 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
> + * the number of result values columns) are skipped.
> + * 4. Rows with all nulls in the values columns are skipped.
> + */
> + PG_FUNCTION_INFO_V1(crosstab);
> + Datum
> + crosstab(PG_FUNCTION_ARGS)
> + {
> + FuncCallContext *funcctx;
> + TupleDesc ret_tupdesc;
> + int call_cntr;
> + int max_calls;
> + TupleTableSlot *slot;
> + AttInMetadata *attinmeta;
> + SPITupleTable *spi_tuptable;
> + TupleDesc spi_tupdesc;
> + char *lastrowid;
> + crosstab_fctx *fctx;
> + int i;
> + int num_categories;
> +
> + /* stuff done only on the first call of the function */
> + if(SRF_IS_FIRSTCALL())
> + {
> + char *sql = GET_STR(PG_GETARG_TEXT_P(0));
> + Oid funcid = fcinfo->flinfo->fn_oid;
> + Oid functypeid;
> + TupleDesc tupdesc;
> + int ret;
> + int proc;
> +
> + /* create a function context for cross-call persistence */
> + funcctx = SRF_FIRSTCALL_INIT();
> +
> + /* get the typeid that represents our return type */
> + functypeid = get_func_rettype(funcid);
> +
> + /* Build a tuple description for a funcrelid tuple */
> + tupdesc = TypeGetTupleDesc(functypeid, NIL);
> +
> + /* allocate a slot for a tuple with this tupdesc */
> + slot = TupleDescGetSlot(tupdesc);
> +
> + /* assign slot to function context */
> + funcctx->slot = slot;
> +
> + /*
> + * Generate attribute metadata needed later to produce tuples from raw
> + * C strings
> + */
> + attinmeta = TupleDescGetAttInMetadata(tupdesc);
> + funcctx->attinmeta = attinmeta;
> +
> + /* Connect to SPI manager */
> + if ((ret = SPI_connect()) < 0)
> + elog(ERROR, "crosstab: SPI_connect returned %d", ret);
> +
> + /* Retrieve the desired rows */
> + ret = SPI_exec(sql, 0);
> + proc = SPI_processed;
> +
> + /* Check for qualifying tuples */
> + if ((ret == SPI_OK_SELECT) && (proc > 0))
> + {
> + spi_tuptable = SPI_tuptable;
> + spi_tupdesc = spi_tuptable->tupdesc;
> +
> + /*
> + * The provided SQL query must always return three columns.
> + *
> + * 1. rowid 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)
> + elog(ERROR, "crosstab: provided SQL must return 3 columns;"
> + " a rowid, a category, and a values column");
> +
> + /*
> + * Check that return tupdesc is compatible with the one we got
> + * from ret_relname, at least based on number and type of
> + * attributes
> + */
> + if (!compatTupleDescs(tupdesc, spi_tupdesc))
> + elog(ERROR, "crosstab: return and sql tuple descriptions are"
> + " incompatible");
> +
> + /* allocate memory for user context */
> + fctx = (crosstab_fctx *) palloc(sizeof(crosstab_fctx));
> +
> + /*
> + * OK, we have data, and it seems to be valid, so save it
> + * for use across calls
> + */
> + fctx->spi_tuptable = spi_tuptable;
> + fctx->lastrowid = NULL;
> + funcctx->user_fctx = fctx;
> +
> + /* total number of tuples to be returned */
> + funcctx->max_calls = proc;
> + }
> + else
> + {
> + /* no qualifying tuples */
> + funcctx->max_calls = 0;
> + }
> + }
> +
> + /* stuff done on every call of the function */
> + funcctx = SRF_PERCALL_SETUP();
> +
> + /*
> + * initialize per-call variables
> + */
> + call_cntr = funcctx->call_cntr;
> + max_calls = funcctx->max_calls;
> +
> + /* return slot for our tuple */
> + slot = funcctx->slot;
> +
> + /* user context info */
> + fctx = (crosstab_fctx *) funcctx->user_fctx;
> + lastrowid = fctx->lastrowid;
> + spi_tuptable = fctx->spi_tuptable;
> +
> + /* the sql tuple */
> + spi_tupdesc = spi_tuptable->tupdesc;
> +
> + /* attribute return type and return tuple description */
> + attinmeta = funcctx->attinmeta;
> + ret_tupdesc = attinmeta->tupdesc;
> +
> + /* the return tuple always must have 1 rowid + num_categories columns */
> + num_categories = ret_tupdesc->natts - 1;
> +
> + if (call_cntr < max_calls) /* do when there is more left to send */
> + {
> + HeapTuple tuple;
> + Datum result;
> + char **values;
> + bool allnulls = true;
> +
> + while (true)
> + {
> + /* allocate space */
> + values = (char **) palloc((1 + num_categories) * sizeof(char *));
> +
> + /* and make sure it's clear */
> + memset(values, '\0', (1 + num_categories) * sizeof(char *));
> +
> + /*
> + * now loop through the sql results and assign each value
> + * in sequence to the next category
> + */
> + for (i = 0; i < num_categories; i++)
> + {
> + HeapTuple spi_tuple;
> + char *rowid;
> +
> + /* see if we've gone too far already */
> + if (call_cntr >= max_calls)
> + break;
> +
> + /* get the next sql result tuple */
> + spi_tuple = spi_tuptable->vals[call_cntr];
> +
> + /* get the rowid from the current sql result tuple */
> + rowid = SPI_getvalue(spi_tuple, spi_tupdesc, 1);
> +
> + /*
> + * If this is the first pass through the values for this rowid
> + * set it, otherwise make sure it hasn't changed on us. Also
> + * check to see if the rowid is the same as that of the last
> + * tuple sent -- if so, skip this tuple entirely
> + */
> + if (i == 0)
> + values[0] = pstrdup(rowid);
> +
> + if ((rowid != NULL) && (strcmp(rowid, values[0]) == 0))
> + {
> + if ((lastrowid != NULL) && (strcmp(rowid, lastrowid) == 0))
> + break;
> + else if (allnulls == true)
> + allnulls = false;
> +
> + /*
> + * Get the next category item value, which is alway attribute
> + * number three.
> + *
> + * Be careful to sssign the value to the array index based
> + * on which category we are presently processing.
> + */
> + values[1 + i] = SPI_getvalue(spi_tuple, spi_tupdesc, 3);
> +
> + /*
> + * increment the counter since we consume a row
> + * for each category, but not for last pass
> + * because the API will do that for us
> + */
> + if (i < (num_categories - 1))
> + call_cntr = ++funcctx->call_cntr;
> + }
> + else
> + {
> + /*
> + * We'll fill in NULLs for the missing values,
> + * but we need to decrement the counter since
> + * this sql result row doesn't belong to the current
> + * output tuple.
> + */
> + call_cntr = --funcctx->call_cntr;
> + break;
> + }
> +
> + if (rowid != NULL)
> + xpfree(rowid);
> + }
> +
> + xpfree(fctx->lastrowid);
> +
> + if (values[0] != NULL)
> + lastrowid = fctx->lastrowid = pstrdup(values[0]);
> +
> + if (!allnulls)
> + {
> + /* build the tuple */
> + tuple = BuildTupleFromCStrings(attinmeta, values);
> +
> + /* make the tuple into a datum */
> + result = TupleGetDatum(slot, tuple);
> +
> + /* Clean up */
> + for (i = 0; i < num_categories + 1; i++)
> + if (values[i] != NULL)
> + xpfree(values[i]);
> + xpfree(values);
> +
> + SRF_RETURN_NEXT(funcctx, result);
> + }
> + else
> + {
> + /*
> + * Skipping this tuple entirely, but we need to advance
> + * the counter like the API would if we had returned
> + * one.
> + */
> + call_cntr = ++funcctx->call_cntr;
> +
> + /* we'll start over at the top */
> + xpfree(values);
> +
> + /* see if we've gone too far already */
> + if (call_cntr >= max_calls)
> + {
> + /* release SPI related resources */
> + SPI_finish();
> + SRF_RETURN_DONE(funcctx);
> + }
> + }
> + }
> + }
> + else /* do when there is no more left */
> + {
> + /* release SPI related resources */
> + SPI_finish();
> + SRF_RETURN_DONE(funcctx);
> + }
> + }
> +
> + /*
> + * Check if two tupdescs match in type of attributes
> + */
> + static bool
> + compatTupleDescs(TupleDesc ret_tupdesc, TupleDesc sql_tupdesc)
> + {
> + int i;
> + Form_pg_attribute ret_attr;
> + Oid ret_atttypid;
> + Form_pg_attribute sql_attr;
> + Oid sql_atttypid;
> +
> + /* check the rowid types match */
> + ret_atttypid = ret_tupdesc->attrs[0]->atttypid;
> + sql_atttypid = sql_tupdesc->attrs[0]->atttypid;
> + if (ret_atttypid != sql_atttypid)
> + elog(ERROR, "compatTupleDescs: SQL rowid datatype does not match"
> + " return rowid datatype");
> +
> + /*
> + * - attribute [1] of the sql tuple is the category;
> + * no need to check it
> + * - attribute [2] of the sql tuple should match
> + * attributes [1] to [natts] of the return tuple
> + */
> + sql_attr = sql_tupdesc->attrs[2];
> + for (i = 1; i < ret_tupdesc->natts; i++)
> + {
> + ret_attr = ret_tupdesc->attrs[i];
> +
> + if (ret_attr->atttypid != sql_attr->atttypid)
> + return false;
> + }
> +
> + /* OK, the two tupdescs are compatible for our purposes */
> + return true;
> + }
> Index: contrib/tablefunc/tablefunc.h
> ===================================================================
> RCS file: contrib/tablefunc/tablefunc.h
> diff -N contrib/tablefunc/tablefunc.h
> *** /dev/null 1 Jan 1970 00:00:00 -0000
> --- contrib/tablefunc/tablefunc.h 19 Jul 2002 04:46:28 -0000
> ***************
> *** 0 ****
> --- 1,39 ----
> + /*
> + * tablefunc
> + *
> + * Sample to demonstrate C functions which return setof scalar
> + * and setof composite.
> + * Joe Conway <mail(at)joeconway(dot)com>
> + *
> + * Copyright 2002 by PostgreSQL Global Development Group
> + *
> + * Permission to use, copy, modify, and distribute this software and its
> + * documentation for any purpose, without fee, and without a written agreement
> + * is hereby granted, provided that the above copyright notice and this
> + * paragraph and the following two paragraphs appear in all copies.
> + *
> + * IN NO EVENT SHALL THE AUTHORS OR DISTRIBUTORS BE LIABLE TO ANY PARTY FOR
> + * DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING
> + * LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS
> + * DOCUMENTATION, EVEN IF THE AUTHOR OR DISTRIBUTORS HAVE BEEN ADVISED OF THE
> + * POSSIBILITY OF SUCH DAMAGE.
> + *
> + * THE AUTHORS AND DISTRIBUTORS SPECIFICALLY DISCLAIM ANY WARRANTIES,
> + * INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY
> + * AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS
> + * ON AN "AS IS" BASIS, AND THE AUTHOR AND DISTRIBUTORS HAS NO OBLIGATIONS TO
> + * PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.
> + *
> + */
> +
> + #ifndef TABLEFUNC_H
> + #define TABLEFUNC_H
> +
> + /*
> + * External declarations
> + */
> + extern Datum show_all_settings(PG_FUNCTION_ARGS);
> + extern Datum normal_rand(PG_FUNCTION_ARGS);
> + extern Datum crosstab(PG_FUNCTION_ARGS);
> +
> + #endif /* TABLEFUNC_H */
> Index: contrib/tablefunc/tablefunc.sql.in
> ===================================================================
> RCS file: contrib/tablefunc/tablefunc.sql.in
> diff -N contrib/tablefunc/tablefunc.sql.in
> *** /dev/null 1 Jan 1970 00:00:00 -0000
> --- contrib/tablefunc/tablefunc.sql.in 21 Jul 2002 01:19:53 -0000
> ***************
> *** 0 ****
> --- 1,46 ----
> + CREATE VIEW tablefunc_config_settings AS
> + SELECT
> + ''::TEXT AS name,
> + ''::TEXT AS setting;
> +
> + CREATE OR REPLACE FUNCTION show_all_settings()
> + RETURNS setof tablefunc_config_settings
> + AS 'MODULE_PATHNAME','show_all_settings' LANGUAGE 'c' STABLE STRICT;
> +
> + CREATE OR REPLACE FUNCTION normal_rand(int4, float8, float8, int4)
> + RETURNS setof float8
> + AS 'MODULE_PATHNAME','normal_rand' LANGUAGE 'c' VOLATILE STRICT;
> +
> + CREATE VIEW tablefunc_crosstab_2 AS
> + SELECT
> + ''::TEXT AS row_name,
> + ''::TEXT AS category_1,
> + ''::TEXT AS category_2;
> +
> + CREATE VIEW tablefunc_crosstab_3 AS
> + SELECT
> + ''::TEXT AS row_name,
> + ''::TEXT AS category_1,
> + ''::TEXT AS category_2,
> + ''::TEXT AS category_3;
> +
> + CREATE VIEW tablefunc_crosstab_4 AS
> + SELECT
> + ''::TEXT AS row_name,
> + ''::TEXT AS category_1,
> + ''::TEXT AS category_2,
> + ''::TEXT AS category_3,
> + ''::TEXT AS category_4;
> +
> + CREATE OR REPLACE FUNCTION crosstab2(text)
> + RETURNS setof tablefunc_crosstab_2
> + AS 'MODULE_PATHNAME','crosstab' LANGUAGE 'c' STABLE STRICT;
> +
> + CREATE OR REPLACE FUNCTION crosstab3(text)
> + RETURNS setof tablefunc_crosstab_3
> + AS 'MODULE_PATHNAME','crosstab' LANGUAGE 'c' STABLE STRICT;
> +
> + CREATE OR REPLACE FUNCTION crosstab4(text)
> + RETURNS setof tablefunc_crosstab_4
> + AS 'MODULE_PATHNAME','crosstab' LANGUAGE 'c' STABLE STRICT;
> +

> Index: src/backend/utils/misc/guc.c
> ===================================================================
> RCS file: /opt/src/cvs/pgsql/src/backend/utils/misc/guc.c,v
> retrieving revision 1.75
> diff -c -r1.75 guc.c
> *** src/backend/utils/misc/guc.c 20 Jul 2002 15:12:55 -0000 1.75
> --- src/backend/utils/misc/guc.c 21 Jul 2002 01:36:12 -0000
> ***************
> *** 2347,2358 ****
> * form of name. Return value is palloc'd.
> */
> char *
> ! GetConfigOptionByNum(int varnum, const char **varname)
> {
> ! struct config_generic *conf = guc_variables[varnum];
>
> if (varname)
> *varname = conf->name;
>
> return _ShowOption(conf);
> }
> --- 2347,2366 ----
> * form of name. Return value is palloc'd.
> */
> char *
> ! GetConfigOptionByNum(int varnum, const char **varname, bool *noshow)
> {
> ! struct config_generic *conf;
> !
> ! /* check requested variable number valid */
> ! Assert((varnum >= 0) && (varnum < num_guc_variables));
> !
> ! conf = guc_variables[varnum];
>
> if (varname)
> *varname = conf->name;
> +
> + if (noshow)
> + *noshow = (conf->flags & GUC_NO_SHOW_ALL) ? true : false;
>
> return _ShowOption(conf);
> }
> Index: src/include/funcapi.h
> ===================================================================
> RCS file: /opt/src/cvs/pgsql/src/include/funcapi.h,v
> retrieving revision 1.3
> diff -c -r1.3 funcapi.h
> *** src/include/funcapi.h 18 Jul 2002 04:40:30 -0000 1.3
> --- src/include/funcapi.h 21 Jul 2002 05:28:36 -0000
> ***************
> *** 139,144 ****
> --- 139,146 ----
> * HeapTuple BuildTupleFromCStrings(AttInMetadata *attinmeta, char **values) -
> * build a HeapTuple given user data in C string form. values is an array
> * of C strings, one for each attribute of the return tuple.
> + * void get_type_metadata(Oid typeid, Oid *attinfuncid, Oid *attelem) - Get
> + * an attribute "in" function and typelem value given the typeid.
> *
> * Macro declarations:
> * TupleGetDatum(TupleTableSlot *slot, HeapTuple tuple) - get a Datum
> Index: src/include/utils/guc.h
> ===================================================================
> RCS file: /opt/src/cvs/pgsql/src/include/utils/guc.h,v
> retrieving revision 1.19
> diff -c -r1.19 guc.h
> *** src/include/utils/guc.h 20 Jul 2002 15:12:56 -0000 1.19
> --- src/include/utils/guc.h 20 Jul 2002 23:44:52 -0000
> ***************
> *** 87,93 ****
> extern void ShowGUCConfigOption(const char *name);
> extern void ShowAllGUCConfig(void);
> extern char *GetConfigOptionByName(const char *name, const char **varname);
> ! extern char *GetConfigOptionByNum(int varnum, const char **varname);
> extern int GetNumConfigOptions(void);
>
> extern void SetPGVariable(const char *name, List *args, bool is_local);
> --- 87,93 ----
> extern void ShowGUCConfigOption(const char *name);
> extern void ShowAllGUCConfig(void);
> extern char *GetConfigOptionByName(const char *name, const char **varname);
> ! extern char *GetConfigOptionByNum(int varnum, const char **varname, bool *noshow);
> extern int GetNumConfigOptions(void);
>
> extern void SetPGVariable(const char *name, List *args, bool is_local);

> Index: doc/src/sgml/xfunc.sgml
> ===================================================================
> RCS file: /opt/src/cvs/pgsql/doc/src/sgml/xfunc.sgml,v
> retrieving revision 1.53
> diff -c -r1.53 xfunc.sgml
> *** doc/src/sgml/xfunc.sgml 18 Jul 2002 04:47:17 -0000 1.53
> --- doc/src/sgml/xfunc.sgml 21 Jul 2002 05:29:09 -0000
> ***************
> *** 1557,1562 ****
> --- 1557,1570 ----
> </para>
>
> <para>
> + In order to get an attribute "in" function and typelem value given the
> + typeid, use
> + <programlisting>
> + void get_type_metadata(Oid typeid, Oid *attinfuncid, Oid *attelem)
> + </programlisting>
> + </para>
> +
> + <para>
> Finally, in order to return a tuple using the SRF portion of the API
> (described below), the tuple must be converted into a Datum. Use
> <programlisting>

>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026

Patch applied. Thanks.

---------------------------------------------------------------------------

Joe Conway wrote:
> Tom Lane wrote:
> > Doesn't this duplicate get_func_rettype()? (Which is more clearly
> > named anyway; a function's type is a second-order concept IMHO...)
>
> Here's a second try at all three patches. I removed the foidGetTypeId()
> function from funcapi.c, and replaced references to it in
> contrib/tablefunc with get_func_rettype().
>
> The only change to funcapi now is a minor addition to the comments in
> funcapi.h and the funcapi doc for the previously mentioned undocumented
> function.
>
> As always, thanks for the review.
>
> Joe
>
>

> Index: contrib/tablefunc/Makefile
> ===================================================================
> RCS file: contrib/tablefunc/Makefile
> diff -N contrib/tablefunc/Makefile
> *** /dev/null 1 Jan 1970 00:00:00 -0000
> --- contrib/tablefunc/Makefile 9 Jul 2002 03:34:24 -0000
> ***************
> *** 0 ****
> --- 1,9 ----
> + subdir = contrib/tablefunc
> + top_builddir = ../..
> + include $(top_builddir)/src/Makefile.global
> +
> + MODULES = tablefunc
> + DATA_built = tablefunc.sql
> + DOCS = README.tablefunc
> +
> + include $(top_srcdir)/contrib/contrib-global.mk
> Index: contrib/tablefunc/README.tablefunc
> ===================================================================
> RCS file: contrib/tablefunc/README.tablefunc
> diff -N contrib/tablefunc/README.tablefunc
> *** /dev/null 1 Jan 1970 00:00:00 -0000
> --- contrib/tablefunc/README.tablefunc 21 Jul 2002 02:50:53 -0000
> ***************
> *** 0 ****
> --- 1,272 ----
> + /*
> + * tablefunc
> + *
> + * Sample to demonstrate C functions which return setof scalar
> + * and setof composite.
> + * Joe Conway <mail(at)joeconway(dot)com>
> + *
> + * Copyright 2002 by PostgreSQL Global Development Group
> + *
> + * Permission to use, copy, modify, and distribute this software and its
> + * documentation for any purpose, without fee, and without a written agreement
> + * is hereby granted, provided that the above copyright notice and this
> + * paragraph and the following two paragraphs appear in all copies.
> + *
> + * IN NO EVENT SHALL THE AUTHORS OR DISTRIBUTORS BE LIABLE TO ANY PARTY FOR
> + * DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING
> + * LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS
> + * DOCUMENTATION, EVEN IF THE AUTHOR OR DISTRIBUTORS HAVE BEEN ADVISED OF THE
> + * POSSIBILITY OF SUCH DAMAGE.
> + *
> + * THE AUTHORS AND DISTRIBUTORS SPECIFICALLY DISCLAIM ANY WARRANTIES,
> + * INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY
> + * AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS
> + * ON AN "AS IS" BASIS, AND THE AUTHOR AND DISTRIBUTORS HAS NO OBLIGATIONS TO
> + * PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.
> + *
> + */
> + Version 0.1 (20 July, 2002):
> + First release
> +
> + Release Notes:
> +
> + Version 0.1
> + - initial release
> +
> + Installation:
> + Place these files in a directory called 'tablefunc' under 'contrib' in the
> + PostgreSQL source tree. Then run:
> +
> + make
> + make install
> +
> + You can use tablefunc.sql to create the functions in your database of choice, e.g.
> +
> + psql -U postgres template1 < tablefunc.sql
> +
> + installs following functions into database template1:
> +
> + show_all_settings()
> + - returns the same information as SHOW ALL, but as a query result
> +
> + normal_rand(int numvals, float8 mean, float8 stddev, int seed)
> + - returns a set of normally distributed float8 values
> +
> + crosstabN(text sql)
> + - returns a set of row_name plus N category value columns
> + - crosstab2(), crosstab3(), and crosstab4() are defined for you,
> + but you can create additional crosstab functions per the instructions
> + in the documentation below.
> +
> + Documentation
> + ==================================================================
> + Name
> +
> + show_all_settings() - returns the same information as SHOW ALL,
> + but as a query result.
> +
> + Synopsis
> +
> + show_all_settings()
> +
> + Inputs
> +
> + none
> +
> + Outputs
> +
> + Returns setof tablefunc_config_settings which is defined by:
> + CREATE VIEW tablefunc_config_settings AS
> + SELECT
> + ''::TEXT AS name,
> + ''::TEXT AS setting;
> +
> + Example usage
> +
> + test=# select * from show_all_settings();
> + name | setting
> + -------------------------------+---------------------------------------
> + australian_timezones | off
> + authentication_timeout | 60
> + checkpoint_segments | 3
> + .
> + .
> + .
> + wal_debug | 0
> + wal_files | 0
> + wal_sync_method | fdatasync
> + (94 rows)
> +
> + ==================================================================
> + Name
> +
> + normal_rand(int, float8, float8, int) - returns a set of normally
> + distributed float8 values
> +
> + Synopsis
> +
> + normal_rand(int numvals, float8 mean, float8 stddev, int seed)
> +
> + Inputs
> +
> + numvals
> + the number of random values to be returned from the function
> +
> + mean
> + the mean of the normal distribution of values
> +
> + stddev
> + the standard deviation of the normal distribution of values
> +
> + seed
> + a seed value for the pseudo-random number generator
> +
> + Outputs
> +
> + Returns setof float8, where the returned set of random values are normally
> + distributed (Gaussian distribution)
> +
> + Example usage
> +
> + test=# SELECT * FROM
> + test=# normal_rand(1000, 5, 3, EXTRACT(SECONDS FROM CURRENT_TIME(0))::int);
> + normal_rand
> + ----------------------
> + 1.56556322244898
> + 9.10040991424657
> + 5.36957140345079
> + -0.369151492880995
> + 0.283600703686639
> + .
> + .
> + .
> + 4.82992125404908
> + 9.71308014517282
> + 2.49639286969028
> + (1000 rows)
> +
> + Returns 1000 values with a mean of 5 and a standard deviation of 3.
> +
> + ==================================================================
> + Name
> +
> + crosstabN(text) - returns a set of row_name plus N category value columns
> +
> + Synopsis
> +
> + crosstabN(text sql)
> +
> + Inputs
> +
> + sql
> +
> + 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.
> +
> + e.g. provided sql must produce a set something like:
> +
> + row_name cat value
> + ----------+-------+-------
> + row1 cat1 val1
> + row1 cat2 val2
> + row1 cat3 val3
> + row1 cat4 val4
> + row2 cat1 val5
> + row2 cat2 val6
> + row2 cat3 val7
> + row2 cat4 val8
> +
> + 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,
> + .
> + .
> + .
> + ''::TEXT AS category_N;
> +
> + for the default installed functions, where N is 2, 3, or 4.
> +
> + e.g. the provided crosstab2 function produces a set something like:
> + <== values columns ==>
> + row_name category_1 category_2
> + ---------+------------+------------
> + row1 val1 val2
> + row2 val5 val6
> +
> + Notes
> +
> + 1. The 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.
> +
> + 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.
> +
> + 4. Extra values (i.e. too many adjacent rows of same row_name to fill
> + the number of result values columns) are skipped.
> +
> + 5. Rows with all nulls in the values columns are skipped.
> +
> + 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:
> +
> + 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
> +
> + create table ct(id serial, rowclass text, rowid text, attribute text, value text);
> + insert into ct(rowclass, rowid, attribute, value) values('group1','test1','att1','val1');
> + insert into ct(rowclass, rowid, attribute, value) values('group1','test1','att2','val2');
> + insert into ct(rowclass, rowid, attribute, value) values('group1','test1','att3','val3');
> + insert into ct(rowclass, rowid, attribute, value) values('group1','test1','att4','val4');
> + insert into ct(rowclass, rowid, attribute, value) values('group1','test2','att1','val5');
> + insert into ct(rowclass, rowid, attribute, value) values('group1','test2','att2','val6');
> + insert into ct(rowclass, rowid, attribute, value) values('group1','test2','att3','val7');
> + insert into ct(rowclass, rowid, attribute, value) values('group1','test2','att4','val8');
> +
> + select * from crosstab3(
> + 'select rowid, attribute, value
> + from ct
> + where rowclass = ''group1''
> + and (attribute = ''att2'' or attribute = ''att3'') order by 1,2;');
> +
> + row_name | category_1 | category_2 | category_3
> + ----------+------------+------------+------------
> + test1 | val2 | val3 |
> + test2 | val6 | val7 |
> + (2 rows)
> +
> + ==================================================================
> + -- Joe Conway
> +
> Index: contrib/tablefunc/tablefunc-test.sql
> ===================================================================
> RCS file: contrib/tablefunc/tablefunc-test.sql
> diff -N contrib/tablefunc/tablefunc-test.sql
> *** /dev/null 1 Jan 1970 00:00:00 -0000
> --- contrib/tablefunc/tablefunc-test.sql 21 Jul 2002 03:01:13 -0000
> ***************
> *** 0 ****
> --- 1,47 ----
> + --
> + -- show_all_settings()
> + --
> + SELECT * FROM show_all_settings();
> +
> + --
> + -- normal_rand()
> + --
> + SELECT * FROM normal_rand(100, 250, 5, EXTRACT(SECONDS FROM CURRENT_TIME(0))::int);
> +
> + --
> + -- crosstab()
> + --
> + create table ct(id serial, rowclass text, rowid text, attribute text, value text);
> +
> + insert into ct(rowclass, rowid, attribute, value) values('group1','test1','att1','val1');
> + insert into ct(rowclass, rowid, attribute, value) values('group1','test1','att2','val2');
> + insert into ct(rowclass, rowid, attribute, value) values('group1','test1','att3','val3');
> + insert into ct(rowclass, rowid, attribute, value) values('group1','test1','att4','val4');
> + insert into ct(rowclass, rowid, attribute, value) values('group1','test2','att1','val5');
> + insert into ct(rowclass, rowid, attribute, value) values('group1','test2','att2','val6');
> + insert into ct(rowclass, rowid, attribute, value) values('group1','test2','att3','val7');
> + insert into ct(rowclass, rowid, attribute, value) values('group1','test2','att4','val8');
> + insert into ct(rowclass, rowid, attribute, value) values('group2','test3','att1','val1');
> + insert into ct(rowclass, rowid, attribute, value) values('group2','test3','att2','val2');
> + insert into ct(rowclass, rowid, attribute, value) values('group2','test3','att3','val3');
> + insert into ct(rowclass, rowid, attribute, value) values('group2','test4','att1','val4');
> + insert into ct(rowclass, rowid, attribute, value) values('group2','test4','att2','val5');
> + insert into ct(rowclass, rowid, attribute, value) values('group2','test4','att3','val6');
> +
> + select * from crosstab2('select rowid, attribute, value from ct where rowclass = ''group1'' and (attribute = ''att2'' or attribute = ''att3'') order by 1,2;');
> + select * from crosstab3('select rowid, attribute, value from ct where rowclass = ''group1'' and (attribute = ''att2'' or attribute = ''att3'') order by 1,2;');
> + select * from crosstab4('select rowid, attribute, value from ct where rowclass = ''group1'' and (attribute = ''att2'' or attribute = ''att3'') order by 1,2;');
> +
> + select * from crosstab2('select rowid, attribute, value from ct where rowclass = ''group1'' order by 1,2;');
> + select * from crosstab3('select rowid, attribute, value from ct where rowclass = ''group1'' order by 1,2;');
> + select * from crosstab4('select rowid, attribute, value from ct where rowclass = ''group1'' order by 1,2;');
> +
> + select * from crosstab2('select rowid, attribute, value from ct where rowclass = ''group2'' and (attribute = ''att1'' or attribute = ''att2'') order by 1,2;');
> + select * from crosstab3('select rowid, attribute, value from ct where rowclass = ''group2'' and (attribute = ''att1'' or attribute = ''att2'') order by 1,2;');
> + select * from crosstab4('select rowid, attribute, value from ct where rowclass = ''group2'' and (attribute = ''att1'' or attribute = ''att2'') order by 1,2;');
> +
> + select * from crosstab2('select rowid, attribute, value from ct where rowclass = ''group2'' order by 1,2;');
> + select * from crosstab3('select rowid, attribute, value from ct where rowclass = ''group2'' order by 1,2;');
> + select * from crosstab4('select rowid, attribute, value from ct where rowclass = ''group2'' order by 1,2;');
> +
> +
> Index: contrib/tablefunc/tablefunc.c
> ===================================================================
> RCS file: contrib/tablefunc/tablefunc.c
> diff -N contrib/tablefunc/tablefunc.c
> *** /dev/null 1 Jan 1970 00:00:00 -0000
> --- contrib/tablefunc/tablefunc.c 21 Jul 2002 05:36:00 -0000
> ***************
> *** 0 ****
> --- 1,665 ----
> + /*
> + * tablefunc
> + *
> + * Sample to demonstrate C functions which return setof scalar
> + * and setof composite.
> + * Joe Conway <mail(at)joeconway(dot)com>
> + *
> + * Copyright 2002 by PostgreSQL Global Development Group
> + *
> + * Permission to use, copy, modify, and distribute this software and its
> + * documentation for any purpose, without fee, and without a written agreement
> + * is hereby granted, provided that the above copyright notice and this
> + * paragraph and the following two paragraphs appear in all copies.
> + *
> + * IN NO EVENT SHALL THE AUTHORS OR DISTRIBUTORS BE LIABLE TO ANY PARTY FOR
> + * DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING
> + * LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS
> + * DOCUMENTATION, EVEN IF THE AUTHOR OR DISTRIBUTORS HAVE BEEN ADVISED OF THE
> + * POSSIBILITY OF SUCH DAMAGE.
> + *
> + * THE AUTHORS AND DISTRIBUTORS SPECIFICALLY DISCLAIM ANY WARRANTIES,
> + * INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY
> + * AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS
> + * ON AN "AS IS" BASIS, AND THE AUTHOR AND DISTRIBUTORS HAS NO OBLIGATIONS TO
> + * PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.
> + *
> + */
> + #include <stdlib.h>
> + #include <math.h>
> +
> + #include "postgres.h"
> +
> + #include "fmgr.h"
> + #include "funcapi.h"
> + #include "executor/spi.h"
> + #include "utils/builtins.h"
> + #include "utils/guc.h"
> + #include "utils/lsyscache.h"
> +
> + #include "tablefunc.h"
> +
> + static bool compatTupleDescs(TupleDesc tupdesc1, TupleDesc tupdesc2);
> + static void get_normal_pair(float8 *x1, float8 *x2);
> +
> + typedef struct
> + {
> + float8 mean; /* mean of the distribution */
> + float8 stddev; /* stddev of the distribution */
> + float8 carry_val; /* hold second generated value */
> + bool use_carry; /* use second generated value */
> + } normal_rand_fctx;
> +
> + typedef struct
> + {
> + SPITupleTable *spi_tuptable; /* sql results from user query */
> + char *lastrowid; /* rowid of the last tuple sent */
> + } crosstab_fctx;
> +
> + #define GET_TEXT(cstrp) DatumGetTextP(DirectFunctionCall1(textin, CStringGetDatum(cstrp)))
> + #define GET_STR(textp) DatumGetCString(DirectFunctionCall1(textout, PointerGetDatum(textp)))
> + #define xpfree(var_) \
> + do { \
> + if (var_ != NULL) \
> + { \
> + pfree(var_); \
> + var_ = NULL; \
> + } \
> + } while (0)
> +
> + /*
> + * show_all_settings - equiv to SHOW ALL command but implemented as
> + * a Table Function.
> + */
> + PG_FUNCTION_INFO_V1(show_all_settings);
> + Datum
> + show_all_settings(PG_FUNCTION_ARGS)
> + {
> + FuncCallContext *funcctx;
> + TupleDesc tupdesc;
> + int call_cntr;
> + int max_calls;
> + TupleTableSlot *slot;
> + AttInMetadata *attinmeta;
> +
> + /* stuff done only on the first call of the function */
> + if(SRF_IS_FIRSTCALL())
> + {
> + Oid funcid = fcinfo->flinfo->fn_oid;
> + Oid functypeid;
> +
> + /* create a function context for cross-call persistence */
> + funcctx = SRF_FIRSTCALL_INIT();
> +
> + /* get the typeid that represents our return type */
> + functypeid = get_func_rettype(funcid);
> +
> + /* Build a tuple description for a funcrelid tuple */
> + tupdesc = TypeGetTupleDesc(functypeid, NIL);
> +
> + /* allocate a slot for a tuple with this tupdesc */
> + slot = TupleDescGetSlot(tupdesc);
> +
> + /* assign slot to function context */
> + funcctx->slot = slot;
> +
> + /*
> + * Generate attribute metadata needed later to produce tuples from raw
> + * C strings
> + */
> + attinmeta = TupleDescGetAttInMetadata(tupdesc);
> + funcctx->attinmeta = attinmeta;
> +
> + /* total number of tuples to be returned */
> + funcctx->max_calls = GetNumConfigOptions();
> + }
> +
> + /* stuff done on every call of the function */
> + funcctx = SRF_PERCALL_SETUP();
> +
> + call_cntr = funcctx->call_cntr;
> + max_calls = funcctx->max_calls;
> + slot = funcctx->slot;
> + attinmeta = funcctx->attinmeta;
> +
> + if (call_cntr < max_calls) /* do when there is more left to send */
> + {
> + char **values;
> + char *varname;
> + char *varval;
> + bool noshow;
> + HeapTuple tuple;
> + Datum result;
> +
> + /*
> + * Get the next visible GUC variable name and value
> + */
> + do
> + {
> + varval = GetConfigOptionByNum(call_cntr, (const char **) &varname, &noshow);
> + if (noshow)
> + {
> + /* varval is a palloc'd copy, so free it */
> + xpfree(varval);
> +
> + /* bump the counter and get the next config setting */
> + call_cntr = ++funcctx->call_cntr;
> +
> + /* make sure we haven't gone too far now */
> + if (call_cntr >= max_calls)
> + SRF_RETURN_DONE(funcctx);
> + }
> + } while (noshow);
> +
> + /*
> + * Prepare a values array for storage in our slot.
> + * This should be an array of C strings which will
> + * be processed later by the appropriate "in" functions.
> + */
> + values = (char **) palloc(2 * sizeof(char *));
> + values[0] = pstrdup(varname);
> + values[1] = varval; /* varval is already a palloc'd copy */
> +
> + /* build a tuple */
> + tuple = BuildTupleFromCStrings(attinmeta, values);
> +
> + /* make the tuple into a datum */
> + result = TupleGetDatum(slot, tuple);
> +
> + /* Clean up */
> + xpfree(values[0]);
> + xpfree(values[1]);
> + xpfree(values);
> +
> + SRF_RETURN_NEXT(funcctx, result);
> + }
> + else /* do when there is no more left */
> + {
> + SRF_RETURN_DONE(funcctx);
> + }
> + }
> +
> + /*
> + * normal_rand - return requested number of random values
> + * with a Gaussian (Normal) distribution.
> + *
> + * inputs are int numvals, float8 lower_bound, and float8 upper_bound
> + * returns float8
> + */
> + PG_FUNCTION_INFO_V1(normal_rand);
> + Datum
> + normal_rand(PG_FUNCTION_ARGS)
> + {
> + FuncCallContext *funcctx;
> + int call_cntr;
> + int max_calls;
> + normal_rand_fctx *fctx;
> + float8 mean;
> + float8 stddev;
> + float8 carry_val;
> + bool use_carry;
> +
> + /* stuff done only on the first call of the function */
> + if(SRF_IS_FIRSTCALL())
> + {
> + /* create a function context for cross-call persistence */
> + funcctx = SRF_FIRSTCALL_INIT();
> +
> + /* total number of tuples to be returned */
> + funcctx->max_calls = PG_GETARG_UINT32(0);
> +
> + /* allocate memory for user context */
> + fctx = (normal_rand_fctx *) palloc(sizeof(normal_rand_fctx));
> +
> + /*
> + * Use fctx to keep track of upper and lower bounds
> + * from call to call. It will also be used to carry over
> + * the spare value we get from the Box-Muller algorithm
> + * so that we only actually calculate a new value every
> + * other call.
> + */
> + fctx->mean = PG_GETARG_FLOAT8(1);
> + fctx->stddev = PG_GETARG_FLOAT8(2);
> + fctx->carry_val = 0;
> + fctx->use_carry = false;
> +
> + funcctx->user_fctx = fctx;
> +
> + /*
> + * we might actually get passed a negative number, but for this
> + * purpose it doesn't matter, just cast it as an unsigned value
> + */
> + srandom(PG_GETARG_UINT32(3));
> + }
> +
> + /* stuff done on every call of the function */
> + funcctx = SRF_PERCALL_SETUP();
> +
> + call_cntr = funcctx->call_cntr;
> + max_calls = funcctx->max_calls;
> + fctx = funcctx->user_fctx;
> + mean = fctx->mean;
> + stddev = fctx->stddev;
> + carry_val = fctx->carry_val;
> + use_carry = fctx->use_carry;
> +
> + if (call_cntr < max_calls) /* do when there is more left to send */
> + {
> + float8 result;
> +
> + if(use_carry)
> + {
> + /*
> + * reset use_carry and use second value obtained on last pass
> + */
> + fctx->use_carry = false;
> + result = carry_val;
> + }
> + else
> + {
> + float8 normval_1;
> + float8 normval_2;
> +
> + /* Get the next two normal values */
> + get_normal_pair(&normval_1, &normval_2);
> +
> + /* use the first */
> + result = mean + (stddev * normval_1);
> +
> + /* and save the second */
> + fctx->carry_val = mean + (stddev * normval_2);
> + fctx->use_carry = true;
> + }
> +
> + /* send the result */
> + SRF_RETURN_NEXT(funcctx, Float8GetDatum(result));
> + }
> + else /* do when there is no more left */
> + {
> + SRF_RETURN_DONE(funcctx);
> + }
> + }
> +
> + /*
> + * get_normal_pair()
> + * Assigns normally distributed (Gaussian) values to a pair of provided
> + * parameters, with mean 0, standard deviation 1.
> + *
> + * This routine implements Algorithm P (Polar method for normal deviates)
> + * from Knuth's _The_Art_of_Computer_Programming_, Volume 2, 3rd ed., pages
> + * 122-126. Knuth cites his source as "The polar method", G. E. P. Box, M. E.
> + * Muller, and G. Marsaglia, _Annals_Math,_Stat._ 29 (1958), 610-611.
> + *
> + */
> + static void
> + get_normal_pair(float8 *x1, float8 *x2)
> + {
> + float8 u1, u2, v1, v2, s;
> +
> + for(;;)
> + {
> + u1 = (float8) random() / (float8) RAND_MAX;
> + u2 = (float8) random() / (float8) RAND_MAX;
> +
> + v1 = (2.0 * u1) - 1.0;
> + v2 = (2.0 * u2) - 1.0;
> +
> + s = pow(v1, 2) + pow(v2, 2);
> +
> + if (s >= 1.0)
> + continue;
> +
> + if (s == 0)
> + {
> + *x1 = 0;
> + *x2 = 0;
> + }
> + else
> + {
> + *x1 = v1 * sqrt((-2.0 * log(s)) / s);
> + *x2 = v2 * sqrt((-2.0 * log(s)) / s);
> + }
> +
> + return;
> + }
> + }
> +
> + /*
> + * crosstab - create a crosstab of rowids and values columns from a
> + * SQL statement returning one rowid column, one category column,
> + * and one value column.
> + *
> + * e.g. given sql which produces:
> + *
> + * rowid cat value
> + * ------+-------+-------
> + * row1 cat1 val1
> + * row1 cat2 val2
> + * row1 cat3 val3
> + * row1 cat4 val4
> + * row2 cat1 val5
> + * row2 cat2 val6
> + * row2 cat3 val7
> + * row2 cat4 val8
> + *
> + * crosstab returns:
> + * <===== values columns =====>
> + * rowid cat1 cat2 cat3 cat4
> + * ------+-------+-------+-------+-------
> + * row1 val1 val2 val3 val4
> + * row2 val5 val6 val7 val8
> + *
> + * 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
> + * 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
> + * the number of result values columns) are skipped.
> + * 4. Rows with all nulls in the values columns are skipped.
> + */
> + PG_FUNCTION_INFO_V1(crosstab);
> + Datum
> + crosstab(PG_FUNCTION_ARGS)
> + {
> + FuncCallContext *funcctx;
> + TupleDesc ret_tupdesc;
> + int call_cntr;
> + int max_calls;
> + TupleTableSlot *slot;
> + AttInMetadata *attinmeta;
> + SPITupleTable *spi_tuptable;
> + TupleDesc spi_tupdesc;
> + char *lastrowid;
> + crosstab_fctx *fctx;
> + int i;
> + int num_categories;
> +
> + /* stuff done only on the first call of the function */
> + if(SRF_IS_FIRSTCALL())
> + {
> + char *sql = GET_STR(PG_GETARG_TEXT_P(0));
> + Oid funcid = fcinfo->flinfo->fn_oid;
> + Oid functypeid;
> + TupleDesc tupdesc;
> + int ret;
> + int proc;
> +
> + /* create a function context for cross-call persistence */
> + funcctx = SRF_FIRSTCALL_INIT();
> +
> + /* get the typeid that represents our return type */
> + functypeid = get_func_rettype(funcid);
> +
> + /* Build a tuple description for a funcrelid tuple */
> + tupdesc = TypeGetTupleDesc(functypeid, NIL);
> +
> + /* allocate a slot for a tuple with this tupdesc */
> + slot = TupleDescGetSlot(tupdesc);
> +
> + /* assign slot to function context */
> + funcctx->slot = slot;
> +
> + /*
> + * Generate attribute metadata needed later to produce tuples from raw
> + * C strings
> + */
> + attinmeta = TupleDescGetAttInMetadata(tupdesc);
> + funcctx->attinmeta = attinmeta;
> +
> + /* Connect to SPI manager */
> + if ((ret = SPI_connect()) < 0)
> + elog(ERROR, "crosstab: SPI_connect returned %d", ret);
> +
> + /* Retrieve the desired rows */
> + ret = SPI_exec(sql, 0);
> + proc = SPI_processed;
> +
> + /* Check for qualifying tuples */
> + if ((ret == SPI_OK_SELECT) && (proc > 0))
> + {
> + spi_tuptable = SPI_tuptable;
> + spi_tupdesc = spi_tuptable->tupdesc;
> +
> + /*
> + * The provided SQL query must always return three columns.
> + *
> + * 1. rowid 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)
> + elog(ERROR, "crosstab: provided SQL must return 3 columns;"
> + " a rowid, a category, and a values column");
> +
> + /*
> + * Check that return tupdesc is compatible with the one we got
> + * from ret_relname, at least based on number and type of
> + * attributes
> + */
> + if (!compatTupleDescs(tupdesc, spi_tupdesc))
> + elog(ERROR, "crosstab: return and sql tuple descriptions are"
> + " incompatible");
> +
> + /* allocate memory for user context */
> + fctx = (crosstab_fctx *) palloc(sizeof(crosstab_fctx));
> +
> + /*
> + * OK, we have data, and it seems to be valid, so save it
> + * for use across calls
> + */
> + fctx->spi_tuptable = spi_tuptable;
> + fctx->lastrowid = NULL;
> + funcctx->user_fctx = fctx;
> +
> + /* total number of tuples to be returned */
> + funcctx->max_calls = proc;
> + }
> + else
> + {
> + /* no qualifying tuples */
> + funcctx->max_calls = 0;
> + }
> + }
> +
> + /* stuff done on every call of the function */
> + funcctx = SRF_PERCALL_SETUP();
> +
> + /*
> + * initialize per-call variables
> + */
> + call_cntr = funcctx->call_cntr;
> + max_calls = funcctx->max_calls;
> +
> + /* return slot for our tuple */
> + slot = funcctx->slot;
> +
> + /* user context info */
> + fctx = (crosstab_fctx *) funcctx->user_fctx;
> + lastrowid = fctx->lastrowid;
> + spi_tuptable = fctx->spi_tuptable;
> +
> + /* the sql tuple */
> + spi_tupdesc = spi_tuptable->tupdesc;
> +
> + /* attribute return type and return tuple description */
> + attinmeta = funcctx->attinmeta;
> + ret_tupdesc = attinmeta->tupdesc;
> +
> + /* the return tuple always must have 1 rowid + num_categories columns */
> + num_categories = ret_tupdesc->natts - 1;
> +
> + if (call_cntr < max_calls) /* do when there is more left to send */
> + {
> + HeapTuple tuple;
> + Datum result;
> + char **values;
> + bool allnulls = true;
> +
> + while (true)
> + {
> + /* allocate space */
> + values = (char **) palloc((1 + num_categories) * sizeof(char *));
> +
> + /* and make sure it's clear */
> + memset(values, '\0', (1 + num_categories) * sizeof(char *));
> +
> + /*
> + * now loop through the sql results and assign each value
> + * in sequence to the next category
> + */
> + for (i = 0; i < num_categories; i++)
> + {
> + HeapTuple spi_tuple;
> + char *rowid;
> +
> + /* see if we've gone too far already */
> + if (call_cntr >= max_calls)
> + break;
> +
> + /* get the next sql result tuple */
> + spi_tuple = spi_tuptable->vals[call_cntr];
> +
> + /* get the rowid from the current sql result tuple */
> + rowid = SPI_getvalue(spi_tuple, spi_tupdesc, 1);
> +
> + /*
> + * If this is the first pass through the values for this rowid
> + * set it, otherwise make sure it hasn't changed on us. Also
> + * check to see if the rowid is the same as that of the last
> + * tuple sent -- if so, skip this tuple entirely
> + */
> + if (i == 0)
> + values[0] = pstrdup(rowid);
> +
> + if ((rowid != NULL) && (strcmp(rowid, values[0]) == 0))
> + {
> + if ((lastrowid != NULL) && (strcmp(rowid, lastrowid) == 0))
> + break;
> + else if (allnulls == true)
> + allnulls = false;
> +
> + /*
> + * Get the next category item value, which is alway attribute
> + * number three.
> + *
> + * Be careful to sssign the value to the array index based
> + * on which category we are presently processing.
> + */
> + values[1 + i] = SPI_getvalue(spi_tuple, spi_tupdesc, 3);
> +
> + /*
> + * increment the counter since we consume a row
> + * for each category, but not for last pass
> + * because the API will do that for us
> + */
> + if (i < (num_categories - 1))
> + call_cntr = ++funcctx->call_cntr;
> + }
> + else
> + {
> + /*
> + * We'll fill in NULLs for the missing values,
> + * but we need to decrement the counter since
> + * this sql result row doesn't belong to the current
> + * output tuple.
> + */
> + call_cntr = --funcctx->call_cntr;
> + break;
> + }
> +
> + if (rowid != NULL)
> + xpfree(rowid);
> + }
> +
> + xpfree(fctx->lastrowid);
> +
> + if (values[0] != NULL)
> + lastrowid = fctx->lastrowid = pstrdup(values[0]);
> +
> + if (!allnulls)
> + {
> + /* build the tuple */
> + tuple = BuildTupleFromCStrings(attinmeta, values);
> +
> + /* make the tuple into a datum */
> + result = TupleGetDatum(slot, tuple);
> +
> + /* Clean up */
> + for (i = 0; i < num_categories + 1; i++)
> + if (values[i] != NULL)
> + xpfree(values[i]);
> + xpfree(values);
> +
> + SRF_RETURN_NEXT(funcctx, result);
> + }
> + else
> + {
> + /*
> + * Skipping this tuple entirely, but we need to advance
> + * the counter like the API would if we had returned
> + * one.
> + */
> + call_cntr = ++funcctx->call_cntr;
> +
> + /* we'll start over at the top */
> + xpfree(values);
> +
> + /* see if we've gone too far already */
> + if (call_cntr >= max_calls)
> + {
> + /* release SPI related resources */
> + SPI_finish();
> + SRF_RETURN_DONE(funcctx);
> + }
> + }
> + }
> + }
> + else /* do when there is no more left */
> + {
> + /* release SPI related resources */
> + SPI_finish();
> + SRF_RETURN_DONE(funcctx);
> + }
> + }
> +
> + /*
> + * Check if two tupdescs match in type of attributes
> + */
> + static bool
> + compatTupleDescs(TupleDesc ret_tupdesc, TupleDesc sql_tupdesc)
> + {
> + int i;
> + Form_pg_attribute ret_attr;
> + Oid ret_atttypid;
> + Form_pg_attribute sql_attr;
> + Oid sql_atttypid;
> +
> + /* check the rowid types match */
> + ret_atttypid = ret_tupdesc->attrs[0]->atttypid;
> + sql_atttypid = sql_tupdesc->attrs[0]->atttypid;
> + if (ret_atttypid != sql_atttypid)
> + elog(ERROR, "compatTupleDescs: SQL rowid datatype does not match"
> + " return rowid datatype");
> +
> + /*
> + * - attribute [1] of the sql tuple is the category;
> + * no need to check it
> + * - attribute [2] of the sql tuple should match
> + * attributes [1] to [natts] of the return tuple
> + */
> + sql_attr = sql_tupdesc->attrs[2];
> + for (i = 1; i < ret_tupdesc->natts; i++)
> + {
> + ret_attr = ret_tupdesc->attrs[i];
> +
> + if (ret_attr->atttypid != sql_attr->atttypid)
> + return false;
> + }
> +
> + /* OK, the two tupdescs are compatible for our purposes */
> + return true;
> + }
> Index: contrib/tablefunc/tablefunc.h
> ===================================================================
> RCS file: contrib/tablefunc/tablefunc.h
> diff -N contrib/tablefunc/tablefunc.h
> *** /dev/null 1 Jan 1970 00:00:00 -0000
> --- contrib/tablefunc/tablefunc.h 19 Jul 2002 04:46:28 -0000
> ***************
> *** 0 ****
> --- 1,39 ----
> + /*
> + * tablefunc
> + *
> + * Sample to demonstrate C functions which return setof scalar
> + * and setof composite.
> + * Joe Conway <mail(at)joeconway(dot)com>
> + *
> + * Copyright 2002 by PostgreSQL Global Development Group
> + *
> + * Permission to use, copy, modify, and distribute this software and its
> + * documentation for any purpose, without fee, and without a written agreement
> + * is hereby granted, provided that the above copyright notice and this
> + * paragraph and the following two paragraphs appear in all copies.
> + *
> + * IN NO EVENT SHALL THE AUTHORS OR DISTRIBUTORS BE LIABLE TO ANY PARTY FOR
> + * DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING
> + * LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS
> + * DOCUMENTATION, EVEN IF THE AUTHOR OR DISTRIBUTORS HAVE BEEN ADVISED OF THE
> + * POSSIBILITY OF SUCH DAMAGE.
> + *
> + * THE AUTHORS AND DISTRIBUTORS SPECIFICALLY DISCLAIM ANY WARRANTIES,
> + * INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY
> + * AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS
> + * ON AN "AS IS" BASIS, AND THE AUTHOR AND DISTRIBUTORS HAS NO OBLIGATIONS TO
> + * PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.
> + *
> + */
> +
> + #ifndef TABLEFUNC_H
> + #define TABLEFUNC_H
> +
> + /*
> + * External declarations
> + */
> + extern Datum show_all_settings(PG_FUNCTION_ARGS);
> + extern Datum normal_rand(PG_FUNCTION_ARGS);
> + extern Datum crosstab(PG_FUNCTION_ARGS);
> +
> + #endif /* TABLEFUNC_H */
> Index: contrib/tablefunc/tablefunc.sql.in
> ===================================================================
> RCS file: contrib/tablefunc/tablefunc.sql.in
> diff -N contrib/tablefunc/tablefunc.sql.in
> *** /dev/null 1 Jan 1970 00:00:00 -0000
> --- contrib/tablefunc/tablefunc.sql.in 21 Jul 2002 01:19:53 -0000
> ***************
> *** 0 ****
> --- 1,46 ----
> + CREATE VIEW tablefunc_config_settings AS
> + SELECT
> + ''::TEXT AS name,
> + ''::TEXT AS setting;
> +
> + CREATE OR REPLACE FUNCTION show_all_settings()
> + RETURNS setof tablefunc_config_settings
> + AS 'MODULE_PATHNAME','show_all_settings' LANGUAGE 'c' STABLE STRICT;
> +
> + CREATE OR REPLACE FUNCTION normal_rand(int4, float8, float8, int4)
> + RETURNS setof float8
> + AS 'MODULE_PATHNAME','normal_rand' LANGUAGE 'c' VOLATILE STRICT;
> +
> + CREATE VIEW tablefunc_crosstab_2 AS
> + SELECT
> + ''::TEXT AS row_name,
> + ''::TEXT AS category_1,
> + ''::TEXT AS category_2;
> +
> + CREATE VIEW tablefunc_crosstab_3 AS
> + SELECT
> + ''::TEXT AS row_name,
> + ''::TEXT AS category_1,
> + ''::TEXT AS category_2,
> + ''::TEXT AS category_3;
> +
> + CREATE VIEW tablefunc_crosstab_4 AS
> + SELECT
> + ''::TEXT AS row_name,
> + ''::TEXT AS category_1,
> + ''::TEXT AS category_2,
> + ''::TEXT AS category_3,
> + ''::TEXT AS category_4;
> +
> + CREATE OR REPLACE FUNCTION crosstab2(text)
> + RETURNS setof tablefunc_crosstab_2
> + AS 'MODULE_PATHNAME','crosstab' LANGUAGE 'c' STABLE STRICT;
> +
> + CREATE OR REPLACE FUNCTION crosstab3(text)
> + RETURNS setof tablefunc_crosstab_3
> + AS 'MODULE_PATHNAME','crosstab' LANGUAGE 'c' STABLE STRICT;
> +
> + CREATE OR REPLACE FUNCTION crosstab4(text)
> + RETURNS setof tablefunc_crosstab_4
> + AS 'MODULE_PATHNAME','crosstab' LANGUAGE 'c' STABLE STRICT;
> +

> Index: src/backend/utils/misc/guc.c
> ===================================================================
> RCS file: /opt/src/cvs/pgsql/src/backend/utils/misc/guc.c,v
> retrieving revision 1.75
> diff -c -r1.75 guc.c
> *** src/backend/utils/misc/guc.c 20 Jul 2002 15:12:55 -0000 1.75
> --- src/backend/utils/misc/guc.c 21 Jul 2002 01:36:12 -0000
> ***************
> *** 2347,2358 ****
> * form of name. Return value is palloc'd.
> */
> char *
> ! GetConfigOptionByNum(int varnum, const char **varname)
> {
> ! struct config_generic *conf = guc_variables[varnum];
>
> if (varname)
> *varname = conf->name;
>
> return _ShowOption(conf);
> }
> --- 2347,2366 ----
> * form of name. Return value is palloc'd.
> */
> char *
> ! GetConfigOptionByNum(int varnum, const char **varname, bool *noshow)
> {
> ! struct config_generic *conf;
> !
> ! /* check requested variable number valid */
> ! Assert((varnum >= 0) && (varnum < num_guc_variables));
> !
> ! conf = guc_variables[varnum];
>
> if (varname)
> *varname = conf->name;
> +
> + if (noshow)
> + *noshow = (conf->flags & GUC_NO_SHOW_ALL) ? true : false;
>
> return _ShowOption(conf);
> }
> Index: src/include/funcapi.h
> ===================================================================
> RCS file: /opt/src/cvs/pgsql/src/include/funcapi.h,v
> retrieving revision 1.3
> diff -c -r1.3 funcapi.h
> *** src/include/funcapi.h 18 Jul 2002 04:40:30 -0000 1.3
> --- src/include/funcapi.h 21 Jul 2002 05:28:36 -0000
> ***************
> *** 139,144 ****
> --- 139,146 ----
> * HeapTuple BuildTupleFromCStrings(AttInMetadata *attinmeta, char **values) -
> * build a HeapTuple given user data in C string form. values is an array
> * of C strings, one for each attribute of the return tuple.
> + * void get_type_metadata(Oid typeid, Oid *attinfuncid, Oid *attelem) - Get
> + * an attribute "in" function and typelem value given the typeid.
> *
> * Macro declarations:
> * TupleGetDatum(TupleTableSlot *slot, HeapTuple tuple) - get a Datum
> Index: src/include/utils/guc.h
> ===================================================================
> RCS file: /opt/src/cvs/pgsql/src/include/utils/guc.h,v
> retrieving revision 1.19
> diff -c -r1.19 guc.h
> *** src/include/utils/guc.h 20 Jul 2002 15:12:56 -0000 1.19
> --- src/include/utils/guc.h 20 Jul 2002 23:44:52 -0000
> ***************
> *** 87,93 ****
> extern void ShowGUCConfigOption(const char *name);
> extern void ShowAllGUCConfig(void);
> extern char *GetConfigOptionByName(const char *name, const char **varname);
> ! extern char *GetConfigOptionByNum(int varnum, const char **varname);
> extern int GetNumConfigOptions(void);
>
> extern void SetPGVariable(const char *name, List *args, bool is_local);
> --- 87,93 ----
> extern void ShowGUCConfigOption(const char *name);
> extern void ShowAllGUCConfig(void);
> extern char *GetConfigOptionByName(const char *name, const char **varname);
> ! extern char *GetConfigOptionByNum(int varnum, const char **varname, bool *noshow);
> extern int GetNumConfigOptions(void);
>
> extern void SetPGVariable(const char *name, List *args, bool is_local);

> Index: doc/src/sgml/xfunc.sgml
> ===================================================================
> RCS file: /opt/src/cvs/pgsql/doc/src/sgml/xfunc.sgml,v
> retrieving revision 1.53
> diff -c -r1.53 xfunc.sgml
> *** doc/src/sgml/xfunc.sgml 18 Jul 2002 04:47:17 -0000 1.53
> --- doc/src/sgml/xfunc.sgml 21 Jul 2002 05:29:09 -0000
> ***************
> *** 1557,1562 ****
> --- 1557,1570 ----
> </para>
>
> <para>
> + In order to get an attribute "in" function and typelem value given the
> + typeid, use
> + <programlisting>
> + void get_type_metadata(Oid typeid, Oid *attinfuncid, Oid *attelem)
> + </programlisting>
> + </para>
> +
> + <para>
> Finally, in order to return a tuple using the SRF portion of the API
> (described below), the tuple must be converted into a Datum. Use
> <programlisting>

>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026