- publishing free software manuals
The PostgreSQL 9.0 Reference Manual - Volume 2 - Programming Guide
by The PostgreSQL Global Development Group
Paperback (6"x9"), 478 pages
ISBN 9781906966065
RRP £14.95 ($19.95)

Sales of this book support the PostgreSQL project! Get a printed copy>>>

13.5 Examples

This section contains a very simple example of SPI usage. The procedure execq takes an SQL command as its first argument and a row count as its second, executes the command using SPI_exec and returns the number of rows that were processed by the command. You can find more complex examples for SPI in the source tree in ‘src/test/regress/regress.c’ and in ‘contrib/spi’.

#include "postgres.h"

#include "executor/spi.h"
#include "utils/builtins.h"

#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif

int execq(text * sql, int cnt);

int
execq(text * sql, int cnt)
{
  char *command;
  int ret;
  int proc;

  /* Convert given text object to a C string */
  command = text_to_cstring(sql);

  SPI_connect();

  ret = SPI_exec(command, cnt);

  proc = SPI_processed;
  /* If some rows were fetched, print them via elog(INFO). */
  if (ret > 0 && SPI_tuptable != NULL) {
    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(command);

  return (proc);
}

(This function uses call convention version 0, to make the example easier to understand. In real applications you should use the new version 1 interface.)

This is how you declare the function after having compiled it into a shared library (details are in section 5.9.6 Compiling and Linking Dynamically-Loaded Functions.):

CREATE FUNCTION execq(text, integer) RETURNS integer
    AS 'filename'
    LANGUAGE C;

Here is a sample session:

=> SELECT execq('CREATE TABLE a (x integer)', 0);
 execq
-------
     0
(1 row)

=> INSERT INTO a VALUES (execq('INSERT INTO a VALUES (0)', 0));
INSERT 0 1
=> SELECT execq('SELECT * FROM a', 0);
INFO:  EXECQ:  0    -- inserted by execq
INFO:  EXECQ:  1    -- returned by execq and inserted by 
  upper INSERT

 execq
-------
     2
(1 row)

=> SELECT execq('INSERT INTO a SELECT x + 2 FROM a', 1);
 execq
-------
     1
(1 row)

=> SELECT execq('SELECT * FROM a', 10);
INFO:  EXECQ:  0
INFO:  EXECQ:  1
INFO:  EXECQ:  2    -- 0 + 2, only one row inserted -
                    -- as specified

 execq
-------
     3              -- 10 is the max value only, 3 is the 
  real number of rows
(1 row)

=> DELETE FROM a;
DELETE 3
=> INSERT INTO a VALUES (execq('SELECT * FROM a', 0) + 1);
INSERT 0 1
=> SELECT * FROM a;
 x
---
 1                  -- no rows in a (0) + 1
(1 row)

=> INSERT INTO a VALUES (execq('SELECT * FROM a', 0) + 1);
INFO:  EXECQ:  1
INSERT 0 1
=> SELECT * FROM a;
 x
---
 1
 2                  -- there was one row in a + 1
(2 rows)

-- This demonstrates the data changes visibility rule:

=> 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
=> SELECT * FROM a;
 x
---
 1
 2
 2         -- 2 rows * 1 (x in first row)
 6         -- 3 rows (2 + 1 just inserted) * 2 (x in second row)
(4 rows)      ^^^^^^
              rows visible to execq() in different invocations
ISBN 9781906966065The PostgreSQL 9.0 Reference Manual - Volume 2 - Programming GuideSee the print edition