On Thu, Aug 11, 2005 at 02:26:16PM -0400, Bruce Momjian wrote:
> Alvaro Herrera wrote:
> > Interesting. I wonder why the function is not defined instead with OUT
> > parameters. That way you don't have to declare the record at execution
> > time. See my patch to pgstattuple for an example.
>
> Uh, where is that patch? I can't find it.
Hmm, seems it never made it to the list, I can't find it in the
archives either. Here it is.
--
Alvaro Herrera (<alvherre[a]alvh.no-ip.org>)
"¿Qué importan los años? Lo que realmente importa es comprobar que
a fin de cuentas la mejor edad de la vida es estar vivo" (Mafalda)
Index: pgstattuple.c
===================================================================
RCS file: /home/alvherre/cvs/pgsql/contrib/pgstattuple/pgstattuple.c,v
retrieving revision 1.19
diff -c -r1.19 pgstattuple.c
*** pgstattuple.c 30 May 2005 23:09:06 -0000 1.19
--- pgstattuple.c 28 Jul 2005 14:48:44 -0000
***************
*** 52,58 ****
*/
#define NCOLUMNS 9
- #define NCHARS 32
Datum
pgstattuple(PG_FUNCTION_ARGS)
--- 52,57 ----
***************
*** 105,117 ****
uint64 dead_tuple_len = 0;
uint64 tuple_count = 0;
uint64 dead_tuple_count = 0;
! double tuple_percent;
! double dead_tuple_percent;
uint64 free_space = 0; /* free/reusable space in bytes */
! double free_percent; /* free/reusable space in % */
TupleDesc tupdesc;
! AttInMetadata *attinmeta;
! char **values;
int i;
Datum result;
--- 104,116 ----
uint64 dead_tuple_len = 0;
uint64 tuple_count = 0;
uint64 dead_tuple_count = 0;
! float8 tuple_percent;
! float8 dead_tuple_percent;
uint64 free_space = 0; /* free/reusable space in bytes */
! float8 free_percent; /* free/reusable space in % */
TupleDesc tupdesc;
! Datum *values;
! bool *nulls;
int i;
Datum result;
***************
*** 122,133 ****
/* make sure we have a persistent copy of the tupdesc */
tupdesc = CreateTupleDescCopy(tupdesc);
- /*
- * Generate attribute metadata needed later to produce tuples from raw
- * C strings
- */
- attinmeta = TupleDescGetAttInMetadata(tupdesc);
-
scan = heap_beginscan(rel, SnapshotAny, 0, NULL);
nblocks = scan->rs_nblocks; /* # blocks to be scanned */
--- 121,126 ----
***************
*** 191,230 ****
}
else
{
! tuple_percent = (double) tuple_len *100.0 / table_len;
! dead_tuple_percent = (double) dead_tuple_len *100.0 / table_len;
! free_percent = (double) free_space *100.0 / table_len;
}
/*
! * Prepare a values array for constructing the tuple. This should be
! * an array of C strings which will be processed later by the
! * appropriate "in" functions.
*/
! values = (char **) palloc(NCOLUMNS * sizeof(char *));
! for (i = 0; i < NCOLUMNS; i++)
! values[i] = (char *) palloc(NCHARS * sizeof(char));
i = 0;
! snprintf(values[i++], NCHARS, INT64_FORMAT, table_len);
! snprintf(values[i++], NCHARS, INT64_FORMAT, tuple_count);
! snprintf(values[i++], NCHARS, INT64_FORMAT, tuple_len);
! snprintf(values[i++], NCHARS, "%.2f", tuple_percent);
! snprintf(values[i++], NCHARS, INT64_FORMAT, dead_tuple_count);
! snprintf(values[i++], NCHARS, INT64_FORMAT, dead_tuple_len);
! snprintf(values[i++], NCHARS, "%.2f", dead_tuple_percent);
! snprintf(values[i++], NCHARS, INT64_FORMAT, free_space);
! snprintf(values[i++], NCHARS, "%.2f", free_percent);
/* build a tuple */
! tuple = BuildTupleFromCStrings(attinmeta, values);
/* make the tuple into a datum */
result = HeapTupleGetDatum(tuple);
/* Clean up */
- for (i = 0; i < NCOLUMNS; i++)
- pfree(values[i]);
pfree(values);
! return (result);
}
--- 184,220 ----
}
else
{
! tuple_percent = (float8) tuple_len *100.0 / table_len;
! dead_tuple_percent = (float8) dead_tuple_len *100.0 / table_len;
! free_percent = (float8) free_space *100.0 / table_len;
}
/*
! * Prepare a values array for constructing the tuple.
*/
! values = (Datum *) palloc(NCOLUMNS * sizeof(Datum));
! nulls = (bool *) palloc0(NCOLUMNS * sizeof(bool));
i = 0;
!
! values[i++] = Int64GetDatum(table_len);
! values[i++] = Int64GetDatum(tuple_count);
! values[i++] = Int64GetDatum(tuple_len);
! values[i++] = Float8GetDatum(tuple_percent);
! values[i++] = Int64GetDatum(dead_tuple_count);
! values[i++] = Int64GetDatum(dead_tuple_len);
! values[i++] = Float8GetDatum(dead_tuple_percent);
! values[i++] = Int64GetDatum(free_space);
! values[i++] = Float8GetDatum(free_percent);
/* build a tuple */
! tuple = heap_form_tuple(tupdesc, values, nulls);
/* make the tuple into a datum */
result = HeapTupleGetDatum(tuple);
/* Clean up */
pfree(values);
+ pfree(nulls);
! return result;
}
Index: pgstattuple.sql.in
===================================================================
RCS file: /home/alvherre/cvs/pgsql/contrib/pgstattuple/pgstattuple.sql.in,v
retrieving revision 1.9
diff -c -r1.9 pgstattuple.sql.in
*** pgstattuple.sql.in 1 Oct 2004 15:43:40 -0000 1.9
--- pgstattuple.sql.in 28 Jul 2005 14:45:50 -0000
***************
*** 1,24 ****
-- Adjust this setting to control where the objects get created.
SET search_path = public;
! CREATE TYPE pgstattuple_type AS (
! table_len BIGINT, -- physical table length in bytes
! tuple_count BIGINT, -- number of live tuples
! tuple_len BIGINT, -- total tuples length in bytes
! tuple_percent FLOAT, -- live tuples in %
! dead_tuple_count BIGINT, -- number of dead tuples
! dead_tuple_len BIGINT, -- total dead tuples length in bytes
! dead_tuple_percent FLOAT, -- dead tuples in %
! free_space BIGINT, -- free space in bytes
! free_percent FLOAT -- free space in %
! );
!
! CREATE OR REPLACE FUNCTION pgstattuple(text)
! RETURNS pgstattuple_type
AS 'MODULE_PATHNAME', 'pgstattuple'
LANGUAGE 'C' STRICT;
! CREATE OR REPLACE FUNCTION pgstattuple(oid)
! RETURNS pgstattuple_type
AS 'MODULE_PATHNAME', 'pgstattuplebyid'
LANGUAGE 'C' STRICT;
--- 1,32 ----
-- Adjust this setting to control where the objects get created.
SET search_path = public;
! CREATE OR REPLACE FUNCTION pgstattuple (
! IN table_name TEXT,
! OUT table_len BIGINT,
! OUT tuple_count BIGINT,
! OUT tuple_len BIGINT,
! OUT tuple_percent FLOAT8,
! OUT dead_tuple_count BIGINT,
! OUT dead_tuple_len BIGINT,
! OUT dead_tuple_percent FLOAT8,
! OUT free_space BIGINT,
! OUT free_percent FLOAT8
! ) RETURNS RECORD
AS 'MODULE_PATHNAME', 'pgstattuple'
LANGUAGE 'C' STRICT;
! CREATE OR REPLACE FUNCTION pgstattuple (
! IN table_oid OID,
! OUT table_len BIGINT,
! OUT tuple_count BIGINT,
! OUT tuple_len BIGINT,
! OUT tuple_percent FLOAT8,
! OUT dead_tuple_count BIGINT,
! OUT dead_tuple_len BIGINT,
! OUT dead_tuple_percent FLOAT8,
! OUT free_space BIGINT,
! OUT free_percent FLOAT8
! ) RETURNS RECORD
AS 'MODULE_PATHNAME', 'pgstattuplebyid'
LANGUAGE 'C' STRICT;
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
