postgresql 多行變一行，C語言自訂函數

 

函數效果如下：

select * from cxf limit 10;id----------abcdefghij(10 row)mysql=# select STRCAT2(id,'|') from (select * from cxf limit 10) t;                                         strcat2                                         ----------------------------------------------------------------------------------------- a|b|c|d|e|f|g|h|i|j(1 row)

 

建立聚集合函式的子函數：

 

drop FUNCTION strcat2_sfunc(varchar, varchar,varchar);CREATE FUNCTION strcat2_sfunc(varchar, varchar,varchar)RETURNS varchar AS '/home/mysql/cxf/postgresql-8.2.16/contrib/strcat/libstrcat2_sfunc'LANGUAGE C IMMUTABLE;

建立聚集合函式

drop AGGREGATE PUBLIC.STRCAT2(VARCHAR,varchar);CREATE  AGGREGATE PUBLIC.STRCAT2(VARCHAR,varchar)(  SFUNC=strcat2_sfunc,   STYPE=VARCHAR  );

 

子函數源碼如下：

strcat2_sfunc.c#include "postgres.h"#include "funcapi.h"#include "fmgr.h"#ifdef PG_MODULE_MAGICPG_MODULE_MAGIC;#endifPG_FUNCTION_INFO_V1(strcat2_sfunc);Datumstrcat2_sfunc(PG_FUNCTION_ARGS){                VarChar  *arg1;                VarChar  *arg2;                VarChar  *arg3;                if(PG_ARGISNULL(0) && PG_ARGISNULL(1))                {                         PG_RETURN_NULL();                }                else if(PG_ARGISNULL(0)){                                PG_RETURN_VARCHAR_P(PG_GETARG_VARCHAR_P(1));                }                else if(PG_ARGISNULL(1))                {                                PG_RETURN_VARCHAR_P(PG_GETARG_VARCHAR_P(0));                }                else{            arg1 = PG_GETARG_VARCHAR_P(0);            arg2 = PG_GETARG_VARCHAR_P(1);            arg3 = PG_GETARG_VARCHAR_P(2);             int32 new_text_size = VARSIZE(arg1) + VARSIZE(arg2) +VARSIZE(arg3)- VARHDRSZ*2;            VarChar *new_text = (VarChar *) palloc(new_text_size);             VARATT_SIZEP(new_text) = new_text_size;            memcpy(VARDATA(new_text), VARDATA(arg1), VARSIZE(arg1) - VARHDRSZ);            memcpy(VARDATA(new_text) + (VARSIZE(arg1) - VARHDRSZ), VARDATA(arg3), VARSIZE(arg3) - VARHDRSZ);            memcpy(VARDATA(new_text) + (VARSIZE(arg1)+VARSIZE(arg3) - VARHDRSZ*2),                  VARDATA(arg2), VARSIZE(arg2)- VARHDRSZ);            PG_RETURN_VARCHAR_P(new_text);  }}

 Makefile:

 

## Makefile for building PostgreSQL extension modules#MODULE_big = strcat2_sfuncOBJS = strcat2_sfunc.oDATA = DOCS = REGRESS = ifdef USE_PGXSPG_CONFIG = pg_configPGXS := $(shell $(PG_CONFIG) --pgxs)include $(PGXS)elsesubdir = contrib/statfunctop_builddir = ../..include $(top_builddir)/src/Makefile.globalinclude $(top_srcdir)/contrib/contrib-global.mkendif