Skip site navigation (1) Skip section navigation (2)

Re: Table Function API doc patch

From: Joe Conway <mail(at)joeconway(dot)com>
To: Joe Conway <mail(at)joeconway(dot)com>
Cc: pgsql-patches(at)postgresql(dot)org
Subject: Re: Table Function API doc patch
Date: 2002-07-21 03:47:31
Message-ID: 3D3A2ED3.3060407@joeconway.com (view raw or flat)
Thread:
Lists: pgsql-patches
Joe Conway wrote:
> Here (finally ;-)) is a doc patch covering the Table Function C API. It 
> reflects the changes in the tablefunc-fix patch that I sent in the other 
> day. It also refers to "see contrib/tablefunc for more examples", which 
> is next on my list of things to finish and submit.

As mentioned above, here is my contrib/tablefunc patch. It includes 
three functions which exercise the tablefunc API.

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
   - 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.

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 directions
     in the README.

crosstabN 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)


Note that this patch depends on the guc_and_tablefunc patch I sent in a 
few minutes ago.

Please apply if no objections.

Thanks,

Joe

Attachment: contrib_tablefunc.2002.07.20.1.patch
Description: text/plain (35.2 KB)

In response to

Responses

pgsql-patches by date

Next:From: Tom LaneDate: 2002-07-21 05:15:38
Subject: Re: guc GetConfigOptionByNum and tablefunc API - minor changes
Previous:From: Joe ConwayDate: 2002-07-21 03:38:30
Subject: guc GetConfigOptionByNum and tablefunc API - minor changes

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group