Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit45e2544

Browse files
committed
As discussed on several occasions previously, the new anonymous
composite type capability makes it possible to create a system viewbased on a table function in a way that is hopefully palatable toeveryone. The attached patch takes advantage of this, movingshow_all_settings() from contrib/tablefunc into the backend (renamedall_settings(). It is defined as a builtin returning type RECORD. Duringinitdb a system view is created to expose the same information presentlyavailable through SHOW ALL. For example:test=# select * from pg_settings where name like '%debug%'; name | setting-----------------------+--------- debug_assertions | on debug_pretty_print | off debug_print_parse | off debug_print_plan | off debug_print_query | off debug_print_rewritten | off wal_debug | 0(7 rows)Additionally during initdb two rules are created which make it possibleto change settings by updating the system view -- a "virtual table" asTom put it. Here's an example:Joe Conway
1 parent4c4854c commit45e2544

File tree

10 files changed

+371
-219
lines changed

10 files changed

+371
-219
lines changed

‎contrib/tablefunc/README.tablefunc‎

Lines changed: 98 additions & 41 deletions
Original file line numberDiff line numberDiff line change
@@ -46,9 +46,6 @@ Installation:
4646

4747
installs following functions into database template1:
4848

49-
show_all_settings()
50-
- returns the same information as SHOW ALL, but as a query result
51-
5249
normal_rand(int numvals, float8 mean, float8 stddev, int seed)
5350
- returns a set of normally distributed float8 values
5451

@@ -58,45 +55,12 @@ Installation:
5855
but you can create additional crosstab functions per the instructions
5956
in the documentation below.
6057

61-
Documentation
62-
==================================================================
63-
Name
64-
65-
show_all_settings() - returns the same information as SHOW ALL,
66-
but as a query result.
67-
68-
Synopsis
69-
70-
show_all_settings()
71-
72-
Inputs
73-
74-
none
75-
76-
Outputs
77-
78-
Returns setof tablefunc_config_settings which is defined by:
79-
CREATE VIEW tablefunc_config_settings AS
80-
SELECT
81-
''::TEXT AS name,
82-
''::TEXT AS setting;
83-
84-
Example usage
85-
86-
test=# select * from show_all_settings();
87-
name | setting
88-
-------------------------------+---------------------------------------
89-
australian_timezones | off
90-
authentication_timeout | 60
91-
checkpoint_segments | 3
92-
.
93-
.
94-
.
95-
wal_debug | 0
96-
wal_files | 0
97-
wal_sync_method | fdatasync
98-
(94 rows)
58+
crosstab(text sql, N int)
59+
- returns a set of row_name plus N category value columns
60+
- requires anonymous composite type syntax in the FROM clause. See
61+
the instructions in the documentation below.
9962

63+
Documentation
10064
==================================================================
10165
Name
10266

@@ -267,6 +231,99 @@ select * from crosstab3(
267231
test2 | val6 | val7 |
268232
(2 rows)
269233

234+
==================================================================
235+
Name
236+
237+
crosstab(text, int) - returns a set of row_name
238+
plus N category value columns
239+
240+
Synopsis
241+
242+
crosstab(text sql, int N)
243+
244+
Inputs
245+
246+
sql
247+
248+
A SQL statement which produces the source set of data. The SQL statement
249+
must return one row_name column, one category column, and one value
250+
column.
251+
252+
e.g. provided sql must produce a set something like:
253+
254+
row_name cat value
255+
----------+-------+-------
256+
row1 cat1 val1
257+
row1 cat2 val2
258+
row1 cat3 val3
259+
row1 cat4 val4
260+
row2 cat1 val5
261+
row2 cat2 val6
262+
row2 cat3 val7
263+
row2 cat4 val8
264+
265+
N
266+
267+
number of category value columns
268+
269+
Outputs
270+
271+
Returns setof record, which must defined with a column definition
272+
in the FROM clause of the SELECT statement, e.g.:
273+
274+
SELECT *
275+
FROM crosstab(sql, 2) AS ct(row_name text, category_1 text, category_2 text);
276+
277+
the example crosstab function produces a set something like:
278+
<== values columns ==>
279+
row_name category_1 category_2
280+
---------+------------+------------
281+
row1 val1 val2
282+
row2 val5 val6
283+
284+
Notes
285+
286+
1. The sql result must be ordered by 1,2.
287+
288+
2. The number of values columns is determined at run-time. The
289+
column definition provided in the FROM clause must provide for
290+
N + 1 columns of the proper data types.
291+
292+
3. Missing values (i.e. not enough adjacent rows of same row_name to
293+
fill the number of result values columns) are filled in with nulls.
294+
295+
4. Extra values (i.e. too many adjacent rows of same row_name to fill
296+
the number of result values columns) are skipped.
297+
298+
5. Rows with all nulls in the values columns are skipped.
299+
300+
301+
Example usage
302+
303+
create table ct(id serial, rowclass text, rowid text, attribute text, value text);
304+
insert into ct(rowclass, rowid, attribute, value) values('group1','test1','att1','val1');
305+
insert into ct(rowclass, rowid, attribute, value) values('group1','test1','att2','val2');
306+
insert into ct(rowclass, rowid, attribute, value) values('group1','test1','att3','val3');
307+
insert into ct(rowclass, rowid, attribute, value) values('group1','test1','att4','val4');
308+
insert into ct(rowclass, rowid, attribute, value) values('group1','test2','att1','val5');
309+
insert into ct(rowclass, rowid, attribute, value) values('group1','test2','att2','val6');
310+
insert into ct(rowclass, rowid, attribute, value) values('group1','test2','att3','val7');
311+
insert into ct(rowclass, rowid, attribute, value) values('group1','test2','att4','val8');
312+
313+
SELECT *
314+
FROM crosstab(
315+
'select rowid, attribute, value
316+
from ct
317+
where rowclass = ''group1''
318+
and (attribute = ''att2'' or attribute = ''att3'') order by 1,2;', 3)
319+
AS ct(row_name text, category_1 text, category_2 text, category_3 text);
320+
321+
row_name | category_1 | category_2 | category_3
322+
----------+------------+------------+------------
323+
test1 | val2 | val3 |
324+
test2 | val6 | val7 |
325+
(2 rows)
326+
270327
==================================================================
271328
-- Joe Conway
272329

‎contrib/tablefunc/tablefunc-test.sql‎

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -44,4 +44,6 @@ select * from crosstab2('select rowid, attribute, value from ct where rowclass =
4444
select*from crosstab3('select rowid, attribute, value from ct where rowclass =''group2'' order by 1,2;');
4545
select*from crosstab4('select rowid, attribute, value from ct where rowclass =''group2'' order by 1,2;');
4646

47-
47+
select*from crosstab('select rowid, attribute, value from ct where rowclass =''group1'' order by 1,2;',2)as c(rowidtext, att1text, att2text);
48+
select*from crosstab('select rowid, attribute, value from ct where rowclass =''group1'' order by 1,2;',3)as c(rowidtext, att1text, att2text, att3text);
49+
select*from crosstab('select rowid, attribute, value from ct where rowclass =''group1'' order by 1,2;',4)as c(rowidtext, att1text, att2text, att3text, att4text);

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp