����SPI��Ȥä���ϲĻ�����§���������Ƥ��ޤ���src/test/regress/regress.c��contrib/spi�ˡ������ʣ�����㤬����ޤ���
��������˴�ñ��SPI��Ȥä���Ǥ���execq�ץ����������1���ܤΰ����Ȥ���SQL�䤤��碌��2���ܤΰ����Ȥ���tcount��ȤꡢSPI_exec��Ȥä��䤤��碌��¹Ԥ����䤤��碌���оݤȤʤä����ץ�ο����֤��ޤ���
#include "executor/spi.h" /* SPI ��Ȥ�����ˤϤ��줬ɬ�פǤ� */ int execq(text *sql, int cnt); int execq(text *sql, int cnt) { char *query; int ret; int proc; /* Ϳ����줿 TEXT ���֥������Ȥ� C ��ʸ������Ѵ����ޤ� */ query = DatumGetCString(DirectFunctionCall1(textout, PointerGetDatum(sql))); SPI_connect(); ret = SPI_exec(query, cnt); proc = SPI_processed; /* * SELECT �䤤��碌�ǡ�ʣ���Υ��ץ뤬�������줿��硢 * elog (INFO) ��Ȥäơ����ץ��ƤӽФ������֤��ޤ��� */ if ( ret == SPI_OK_SELECT && SPI_processed > 0 ) { TupleDesc tupdesc = SPI_tuptable->tupdesc; SPITupleTable *tuptable = SPI_tuptable; char buf[8192]; int i,j; for (j = 0; j < proc; j++) { HeapTuple tuple = tuptable->vals[j]; for (i = 1, buf[0] = 0; i <= tupdesc->natts; i++) snprintf(buf + strlen (buf), sizeof(buf) - strlen(buf)," %s%s", SPI_getvalue(tuple, tupdesc, i), (i == tupdesc->natts) ? " " : " |"); elog (INFO, "EXECQ: %s", buf); } } SPI_finish(); pfree(query); return (proc); }
�����ǥ���ѥ����Ԥ����ؿ���������ޤ���
CREATE FUNCTION execq (text, integer) RETURNS integer AS '...path_to_so' LANGUAGE C;
vac=> SELECT execq('CREATE TABLE a (x INTEGER)', 0); execq ----- 0 (1 row) vac=> INSERT INTO a VALUES (execq('INSERT INTO a VALUES (0)',0)); INSERT 167631 1 vac=> SELECT execq('SELECT * FROM a',0); INFO: EXECQ:0 <<< execq �ˤ�ä��������줿��� INFO: EXECQ:1 <<< execq �ˤ�ä��֤��줿��ΤȾ�� INSERT �ˤ�ä��������줿��� execq ----- 2 (1 row) vac=> SELECT execq('INSERT INTO a SELECT x + 2 FROM a',1); execq ----- 1 (1 row) vac=> SELECT execq('SELECT * FROM a', 10); INFO: EXECQ: 0 INFO: EXECQ: 1 INFO: EXECQ: 2 <<< ���ꤵ�줿 0 + 2 �Ȥ����ͤ� 1 �ĤΥ��ץ�Τߤ���������ޤ��� execq ----- 3 <<< 10 �Ϻ����ͤ������Ǥ��� �ºݤΥ��ץ���� 3 �Ȥʤ�ޤ��� (1 row) vac=> DELETE FROM a; DELETE 3 vac=> INSERT INTO a VALUES (execq('SELECT * FROM a', 0) + 1); INSERT 167712 1 vac=> SELECT * FROM a; x - 1 <<< a �ơ��֥�˥��ץ뤬¸�ߤ��ʤ�����(0) + 1 ��Ԥä���� (1 row) vac=> INSERT INTO a VALUES (execq('SELECT * FROM a', 0) + 1); INFO: EXECQ: 0 INSERT 167713 1 vac=> SELECT * FROM a; x - 1 2 <<< a �ơ��֥�˴�¸�� 1 �ĤΥ��ץ� + 1 (2 rows) -- ����ϥǡ����ѹ��˴ؤ���Ļ�����§���������ޤ��� vac=> INSERT INTO a SELECT execq('SELECT * FROM a', 0) * x FROM a; INFO: EXECQ: 1 INFO: EXECQ: 2 INFO: EXECQ: 1 INFO: EXECQ: 2 INFO: EXECQ: 2 INSERT 0 2 vac=> SELECT * FROM a; x - 1 2 2 <<< ���ץ�� 2 * 1��2 * 1 ���ܤΥ��ץ�� x ���͡� 6 <<< ���ץ�� 3 * 2��2 + �������줿 1 * 2 ���ܤΥ��ץ���͡� (4 rows) ^^^^^^^^^^ execq()�ΰۤʤ�ƤӽФ��Ǹ��뤳�Ȥ��Ǥ��륿�ץ�