PostgreSQL extend function - C example

背景

用C写PostgreSQL扩展，有很多例子可以参考。

其中一些来自PostgreSQL的contrib, 还有一些来自PostgreSQL自身的adt函数。

当然你还可以去pgfoundry和pgxn还有github上找到很多例子。

以PostgreSQL自己提供的函数为例，看看如何找到自己需要的例子，例如我要写返回record类型的函数

查找这些例子

postgres=# select proname from pg_proc where prorettype='record'::regtype and not proretset;  
               proname                 
-------------------------------------  
 pg_sequence_parameters  
 pg_stat_get_archiver  
 pg_identify_object  
 pg_xlogfile_name_offset  
 pg_stat_file  
 record_in  
 record_recv  
 json_to_record  
 jsonb_to_record  
 pg_create_physical_replication_slot  
 pg_create_logical_replication_slot  
 get_rcv_replication_stat  
(12 rows)  

如果我要写返回多条记录的函数，可以查找这些例子

postgres=# select proname from pg_proc where prorettype='record'::regtype and proretset;  
               proname                 
-------------------------------------  
 aclexplode  
 pg_get_keywords  
 pg_options_to_table  
 pg_stat_get_activity  
 pg_stat_get_wal_senders  
 pg_show_all_settings  
 pg_lock_status  
 pg_prepared_xact  
 pg_get_multixact_members  
 pg_prepared_statement  
 pg_cursor  
 pg_timezone_abbrevs  
 pg_timezone_names  
 json_each  
 json_each_text  
 json_to_recordset  
 ts_stat  
 ts_stat  
 ts_token_type  
 ts_token_type  
 ts_parse  
 ts_parse  
 jsonb_each  
 jsonb_each_text  
 jsonb_to_recordset  
 pg_available_extensions  
 pg_available_extension_versions  
 pg_extension_update_paths  
 pg_get_replication_slots  
 pg_event_trigger_dropped_objects  
 ts_debug  
 ts_debug  
 pg_logical_slot_get_changes  
 pg_logical_slot_peek_changes  
 pg_logical_slot_get_binary_changes  
 pg_logical_slot_peek_binary_changes  
 _pg_expandarray  
(37 rows)  

例如 :

postgres=# \df+ pg_stat_get_archiver  
                                                                                                                                                                                                          List of   
functions  
   Schema   |         Name         | Result data type |                                                                                                                Argument data types                          
                                                                                         |  Type  | Security | Volatility |  Owner   | Language |     Source code      |                Description                 
    
------------+----------------------+------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------  
-----------------------------------------------------------------------------------------+--------+----------+------------+----------+----------+----------------------+------------------------------------------  
--  
 pg_catalog | pg_stat_get_archiver | record           | OUT archived_count bigint, OUT last_archived_wal text, OUT last_archived_time timestamp with time zone, OUT failed_count bigint, OUT last_failed_wal text,  
 OUT last_failed_time timestamp with time zone, OUT stats_reset timestamp with time zone | normal | invoker  | stable     | postgres | internal | pg_stat_get_archiver | statistics: information about WAL archive  
r  
(1 row)  

对应的代码在，你可以学到如何构造一条返回记录。

src/backend/utils/adt/pgstatfuncs.c

Datum  
pg_stat_get_archiver(PG_FUNCTION_ARGS)  
{  
        TupleDesc       tupdesc;  
        Datum           values[7];  
        bool            nulls[7];  
        PgStat_ArchiverStats *archiver_stats;  
  
        /* Initialise values and NULL flags arrays */  
        MemSet(values, 0, sizeof(values));  
        MemSet(nulls, 0, sizeof(nulls));  
  
        /* Initialise attributes information in the tuple descriptor */  
        tupdesc = CreateTemplateTupleDesc(7, false);  
        TupleDescInitEntry(tupdesc, (AttrNumber) 1, "archived_count",  
                                           INT8OID, -1, 0);  
        TupleDescInitEntry(tupdesc, (AttrNumber) 2, "last_archived_wal",  
                                           TEXTOID, -1, 0);  
        TupleDescInitEntry(tupdesc, (AttrNumber) 3, "last_archived_time",  
                                           TIMESTAMPTZOID, -1, 0);  
        TupleDescInitEntry(tupdesc, (AttrNumber) 4, "failed_count",  
                                           INT8OID, -1, 0);  
        TupleDescInitEntry(tupdesc, (AttrNumber) 5, "last_failed_wal",  
                                           TEXTOID, -1, 0);  
        TupleDescInitEntry(tupdesc, (AttrNumber) 6, "last_failed_time",  
                                           TIMESTAMPTZOID, -1, 0);  
        TupleDescInitEntry(tupdesc, (AttrNumber) 7, "stats_reset",  
                                           TIMESTAMPTZOID, -1, 0);  
  
        BlessTupleDesc(tupdesc);  
  
        /* Get statistics about the archiver process */  
        archiver_stats = pgstat_fetch_stat_archiver();  
  
        /* Fill values and NULLs */  
        values[0] = Int64GetDatum(archiver_stats->archived_count);  
        if (*(archiver_stats->last_archived_wal) == '\0')  
                nulls[1] = true;  
        else  
                values[1] = CStringGetTextDatum(archiver_stats->last_archived_wal);  
  
        if (archiver_stats->last_archived_timestamp == 0)  
                nulls[2] = true;  
        else  
                values[2] = TimestampTzGetDatum(archiver_stats->last_archived_timestamp);  
  
        values[3] = Int64GetDatum(archiver_stats->failed_count);  
        if (*(archiver_stats->last_failed_wal) == '\0')  
                nulls[4] = true;  
        else  
                values[4] = CStringGetTextDatum(archiver_stats->last_failed_wal);  
  
        if (archiver_stats->last_failed_timestamp == 0)  
                nulls[5] = true;  
        else  
                values[5] = TimestampTzGetDatum(archiver_stats->last_failed_timestamp);  
  
        if (archiver_stats->stat_reset_timestamp == 0)  
                nulls[6] = true;  
        else  
                values[6] = TimestampTzGetDatum(archiver_stats->stat_reset_timestamp);  
  
        /* Returns the record as Datum */  
        PG_RETURN_DATUM(HeapTupleGetDatum(  
                                                                   heap_form_tuple(tupdesc, values, nulls)));  
}  

参考

1. http://www.postgresql.org/docs/9.5/static/xfunc-c.html

2. http://www.postgresql.org/docs/9.5/static/spi.html

3. http://pgfoundry.org/

4. http://pgxn.org/

digoal’s 大量PostgreSQL文章入口