Beispiel mit wenigen Zeilen für EXPLAIN

CREATE TABLE planer_test (
  id         SERIAL        NOT NULL UNIQUE,
  inhalt     VARCHAR       NOT NULL
);

INSERT INTO planer_test (inhalt) VALUES ('Inhalt 001');

INSERT INTO planer_test (inhalt) VALUES ('Inhalt 002');

INSERT INTO planer_test (inhalt) VALUES ('Inhalt 003');

INSERT INTO planer_test (inhalt) VALUES ('Inhalt 004');

INSERT INTO planer_test (inhalt) VALUES ('Inhalt 005');

Statistiken der Datenbank aktualisieren

ANALYZE VERBOSE planer_test;

SELECT id, inhalt FROM planer_test ORDER BY id;

einen bestimmten Wert suchen

EXPLAIN SELECT id, inhalt FROM planer_test WHERE id=1;

Ausführungszeit der Anfrage messen

EXPLAIN ANALYZE SELECT id, inhalt FROM planer_test WHERE id=1;

Beispiel mit vielen Zeilen für EXPLAIN

CREATE TABLE planer_test2 (
  id         SERIAL        NOT NULL UNIQUE,
  inhalt     VARCHAR       NOT NULL
);

Beispieltabelle mit vielen Inhalten füllen dies dauert einige Zeit …

INSERT INTO planer_test2 (id, inhalt)
            SELECT generate_series (1, 10000000), RANDOM();

Statistiken der Datenbank aktualisieren

ANALYZE VERBOSE planer_test2;

einen bestimmten Wert suchen

EXPLAIN SELECT id, inhalt FROM planer_test WHERE id=8500000;

Ausführungszeit der Anfrage messen

EXPLAIN ANALYZE SELECT id, inhalt FROM planer_test WHERE id=8500000;

Die gleiche Messung ohne Index die Nutzung aller vorhandenen Indicies verbieten

SET enable_indexscan = off;

SET enable_bitmapscan = off;

Messungen wiederholen

EXPLAIN SELECT id, inhalt FROM planer_test2 WHERE id=8500000;

EXPLAIN ANALYZE SELECT id, inhalt FROM planer_test2 WHERE id=8500000;

– Einstellung zurücksetzen

RESET enable_indexscan;

RESET enable_bitmapscan;

Beispieltabelle für Deadlock

CREATE TABLE lock_test (
  id             INTEGER      NOT NULL
                              PRIMARY KEY,
  content        VARCHAR      NOT NULL
);

INSERT INTO lock_test (id, content)
            VALUES ('1', 'eingetragen von a');

INSERT INTO lock_test (id, content)
            VALUES ('2', 'eingetragen von b');

Deadlock erzeugen

T 1: BEGIN;
T 2: BEGIN;
T 1: UPDATE lock_test SET content='geaendert von 1' WHERE id=1;
T 2: UPDATE lock_test SET content='geaendert von 2' WHERE id=2;
T 2: UPDATE lock_test SET content='geaendert von 2' WHERE id=1;
T 1: UPDATE lock_test SET content='geaendert von 1' WHERE id=2;

Beispiel für Verbindungsaufbau mit PQconnectdb

#include <postgresql/libpq-fe.h>
#include <stdio.h>

int main (void) {
  const char *connstring = "host=localhost";
  PGconn *conninfo;

  conninfo = PQconnectdb(connstring);

  if (PQstatus(conninfo) == CONNECTION_BAD) {
    printf("Fehler beim Verbinden!\n");
  } else if (PQstatus(conninfo) == CONNECTION_OK) {
    printf("Verbindung in Ordnung\n");
  } else {
    printf("Unbekannter Fehler!\n");
  }

  return 0;
}

Compilieren mit:

gcc -Wall -lpq -L`pg_config --libdir` -I`pg_config --includedir` -o connect connect.c

Beispiel für Verbindungsaufbau mit PQconnectStart

#include <postgresql/libpq-fe.h>
#include <stdio.h>
#include <stdlib.h>
#include <unistd.h>

int main (void) {
  const char *connstring = "host=localhost dbname=template1";
  PGconn *conninfo;
  PostgresPollingStatusType pstat;
  int count;

  conninfo = PQconnectStart(connstring);

  if (PQstatus(conninfo) == CONNECTION_BAD) {
    printf("Fehler beim Verbinden!\n");
  }
  pstat = PQconnectPoll(conninfo);

  sleep(10);

  for (count = 1; count <= 10; count++) {

    pstat = PQconnectPoll(conninfo);
    switch (pstat) {
      case PGRES_POLLING_FAILED:
        {
          char *errorm = PQerrorMessage(conninfo);
          printf("Fehler: %s\n", errorm);
          exit(1);
        }
      case PGRES_POLLING_OK:
        printf("Polling OK\n");
        break;
      case PGRES_POLLING_READING:
        printf("Polling lesend\n");
        break;
      case PGRES_POLLING_WRITING:
        printf("Polling schreibend\n");
        break;
      case PGRES_POLLING_ACTIVE:
        printf("Polling aktiv\n");
        break;
      default:
        printf("Unbekannter Polling Status Code: %08x\n", pstat);
    }
    sleep(1);
  }

  return 0;
}

Compilieren mit:

gcc -Wall -lpq -L`pg_config --libdir` -I`pg_config --includedir` -o connect connect.c

Verbindungsaufbau mit pg_pconnect

$db = pg_pconnect("host=einserver port=5432 dbname=meine_datenbank
                   user=pascal password=geheim");
if (!$db) {
  ... Fehlerbehandlung
}

pg_close($db);

PHP-kompatibler BOOLEAN-Ersatz

--
-- erstelle einen PHP (native) kompatiblen BOOLEAN Datentyp
--

-- Datentyp löschen, falls dieser existiert:
-- das löscht alle Funktionen und Spalten, die diesen Typ nutzen!
-- DROP TYPE IF EXISTS boolean2 CASCADE;

-- Ein- und Ausgabefunktionen
-- dafür können bereits existierende Funktionen genutzt werden
CREATE FUNCTION boolean2_in(cstring)
   RETURNS boolean2
   AS 'boolin'
   LANGUAGE internal STRICT;
CREATE FUNCTION boolean2_out(boolean2)
   RETURNS cstring
   AS 'int2out'
   LANGUAGE internal STRICT;
CREATE FUNCTION boolean2_recv(internal)
   RETURNS boolean2
   AS 'boolrecv'
   LANGUAGE internal STRICT;
CREATE FUNCTION boolean2_send(boolean2)
   RETURNS bytea
   AS 'boolsend'
   LANGUAGE internal STRICT;

-- den eigentlichen Datentyp erstellen
CREATE TYPE boolean2 (
   input = boolean2_in,
   output = boolean2_out,
   receive = boolean2_recv,
   send = boolean2_send,
   internallength = 1,
   alignment = char,
   storage = plain,
   passedbyvalue
);
COMMENT ON TYPE boolean2 IS 'boolean, ''1''/''0''';


-- da BOOLEAN2 binär.kompatibel mit BOOLEAN ist, kann dieser Typ
-- in beide Richtungen gecastet werden
CREATE CAST (boolean2 AS boolean)
    WITHOUT FUNCTION
         AS IMPLICIT;
CREATE CAST (boolean AS boolean2)
    WITHOUT FUNCTION
         AS IMPLICIT;


-- CASTing Funktionen für INTEGER vs BOOLEAN2
CREATE FUNCTION int4(boolean2)
   RETURNS int4
   AS 'bool_int4'
   LANGUAGE internal STRICT;
CREATE FUNCTION boolean2(int4)
   RETURNS boolean2
   AS 'int4_bool'
   LANGUAGE internal STRICT;

-- der eigentliche CAST für INTEGER vs BOOLEAN2
CREATE CAST (boolean2 AS int4)
       WITH FUNCTION int4(boolean2)
         AS ASSIGNMENT;
CREATE CAST (int4 AS boolean2)
       WITH FUNCTION boolean2(int4)
         AS ASSIGNMENT;




-- Operatoren und Hilfsfunktionen
CREATE FUNCTION boollt(boolean2, boolean)
   RETURNS boolean
   AS 'boollt'
   LANGUAGE internal STRICT;
CREATE FUNCTION boollt(boolean, boolean2)
   RETURNS boolean
   AS 'boollt'
   LANGUAGE internal STRICT;
CREATE FUNCTION boollt(boolean2, boolean2)
   RETURNS boolean
   AS 'boollt'
   LANGUAGE internal STRICT;
CREATE OPERATOR < (
    PROCEDURE = boollt,
    LEFTARG = boolean2,
    RIGHTARG = boolean,
    COMMUTATOR = >,
    NEGATOR = >=,
    RESTRICT = scalarltsel,
    JOIN = scalarltjoinsel
);
CREATE OPERATOR < (
    PROCEDURE = boollt,
    LEFTARG = boolean,
    RIGHTARG = boolean2,
    COMMUTATOR = >,
    NEGATOR = >=,
    RESTRICT = scalarltsel,
    JOIN = scalarltjoinsel
);
CREATE OPERATOR < (
    PROCEDURE = boollt,
    LEFTARG = boolean2,
    RIGHTARG = boolean2,
    COMMUTATOR = >,
    NEGATOR = >=,
    RESTRICT = scalarltsel,
    JOIN = scalarltjoinsel
);

CREATE FUNCTION boolle(boolean2, boolean)
   RETURNS boolean
   AS 'boolle'
   LANGUAGE internal STRICT;
CREATE FUNCTION boolle(boolean, boolean2)
   RETURNS boolean
   AS 'boolle'
   LANGUAGE internal STRICT;
CREATE FUNCTION boolle(boolean2, boolean2)
   RETURNS boolean
   AS 'boolle'
   LANGUAGE internal STRICT;
CREATE OPERATOR <= (
    PROCEDURE = boolle,
    LEFTARG = boolean2,
    RIGHTARG = boolean,
    COMMUTATOR = >=,
    NEGATOR = >,
    RESTRICT = scalarltsel,
    JOIN = scalarltjoinsel
);
CREATE OPERATOR <= (
    PROCEDURE = boolle,
    LEFTARG = boolean,
    RIGHTARG = boolean2,
    COMMUTATOR = >=,
    NEGATOR = >,
    RESTRICT = scalarltsel,
    JOIN = scalarltjoinsel
);
CREATE OPERATOR <= (
    PROCEDURE = boolle,
    LEFTARG = boolean2,
    RIGHTARG = boolean2,
    COMMUTATOR = >=,
    NEGATOR = >,
    RESTRICT = scalarltsel,
    JOIN = scalarltjoinsel
);

CREATE FUNCTION boolne(boolean2, boolean)
   RETURNS boolean
   AS 'boolne'
   LANGUAGE internal STRICT;
CREATE FUNCTION boolne(boolean, boolean2)
   RETURNS boolean
   AS 'boolne'
   LANGUAGE internal STRICT;
CREATE FUNCTION boolne(boolean2, boolean2)
   RETURNS boolean
   AS 'boolne'
   LANGUAGE internal STRICT;
CREATE OPERATOR <> (
    PROCEDURE = boolne,
    LEFTARG = boolean2,
    RIGHTARG = boolean,
    COMMUTATOR = <>,
    NEGATOR = =,
    RESTRICT = neqsel,
    JOIN = neqjoinsel
);
CREATE OPERATOR <> (
    PROCEDURE = boolne,
    LEFTARG = boolean,
    RIGHTARG = boolean2,
    COMMUTATOR = <>,
    NEGATOR = =,
    RESTRICT = neqsel,
    JOIN = neqjoinsel
);
CREATE OPERATOR <> (
    PROCEDURE = boolne,
    LEFTARG = boolean2,
    RIGHTARG = boolean2,
    COMMUTATOR = <>,
    NEGATOR = =,
    RESTRICT = neqsel,
    JOIN = neqjoinsel
);

CREATE FUNCTION booleq(boolean2, boolean)
   RETURNS boolean
   AS 'booleq'
   LANGUAGE internal STRICT;
CREATE FUNCTION booleq(boolean, boolean2)
   RETURNS boolean
   AS 'booleq'
   LANGUAGE internal STRICT;
CREATE FUNCTION booleq(boolean2, boolean2)
   RETURNS boolean
   AS 'booleq'
   LANGUAGE internal STRICT;
CREATE OPERATOR = (
    PROCEDURE = booleq,
    LEFTARG = boolean2,
    RIGHTARG = boolean,
    COMMUTATOR = =,
    NEGATOR = <>,
    HASHES,
    RESTRICT = eqsel,
    JOIN = eqjoinsel,
    SORT1 = <,
    SORT2 = <,
    LTCMP = <,
    GTCMP = >
);
CREATE OPERATOR = (
    PROCEDURE = booleq,
    LEFTARG = boolean,
    RIGHTARG = boolean2,
    COMMUTATOR = =,
    NEGATOR = <>,
    HASHES,
    RESTRICT = eqsel,
    JOIN = eqjoinsel,
    SORT1 = <,
    SORT2 = <,
    LTCMP = <,
    GTCMP = >
);
CREATE OPERATOR = (
    PROCEDURE = booleq,
    LEFTARG = boolean2,
    RIGHTARG = boolean2,
    COMMUTATOR = =,
    NEGATOR = <>,
    HASHES,
    RESTRICT = eqsel,
    JOIN = eqjoinsel,
    SORT1 = <,
    SORT2 = <,
    LTCMP = <,
    GTCMP = >
);

CREATE FUNCTION boolgt(boolean2, boolean)
   RETURNS boolean
   AS 'boolgt'
   LANGUAGE internal STRICT;
CREATE FUNCTION boolgt(boolean, boolean2)
   RETURNS boolean
   AS 'boolgt'
   LANGUAGE internal STRICT;
CREATE FUNCTION boolgt(boolean2, boolean2)
   RETURNS boolean
   AS 'boolgt'
   LANGUAGE internal STRICT;
CREATE OPERATOR > (
    PROCEDURE = boolgt,
    LEFTARG = boolean2,
    RIGHTARG = boolean,
    COMMUTATOR = <,
    NEGATOR = <=,
    RESTRICT = scalargtsel,
    JOIN = scalargtjoinsel
);
CREATE OPERATOR > (
    PROCEDURE = boolgt,
    LEFTARG = boolean,
    RIGHTARG = boolean2,
    COMMUTATOR = <,
    NEGATOR = <=,
    RESTRICT = scalargtsel,
    JOIN = scalargtjoinsel
);
CREATE OPERATOR > (
    PROCEDURE = boolgt,
    LEFTARG = boolean2,
    RIGHTARG = boolean2,
    COMMUTATOR = <,
    NEGATOR = <=,
    RESTRICT = scalargtsel,
    JOIN = scalargtjoinsel
);

CREATE FUNCTION boolge(boolean2, boolean)
   RETURNS boolean
   AS 'boolge'
   LANGUAGE internal STRICT;
CREATE FUNCTION boolge(boolean, boolean2)
   RETURNS boolean
   AS 'boolge'
   LANGUAGE internal STRICT;
CREATE FUNCTION boolge(boolean2, boolean2)
   RETURNS boolean
   AS 'boolge'
   LANGUAGE internal STRICT;
CREATE OPERATOR >= (
    PROCEDURE = boolge,
    LEFTARG = boolean2,
    RIGHTARG = boolean,
    COMMUTATOR = <=,
    NEGATOR = <,
    RESTRICT = scalargtsel,
    JOIN = scalargtjoinsel
);
CREATE OPERATOR >= (
    PROCEDURE = boolge,
    LEFTARG = boolean,
    RIGHTARG = boolean2,
    COMMUTATOR = <=,
    NEGATOR = <,
    RESTRICT = scalargtsel,
    JOIN = scalargtjoinsel
);
CREATE OPERATOR >= (
    PROCEDURE = boolge,
    LEFTARG = boolean2,
    RIGHTARG = boolean2,
    COMMUTATOR = <=,
    NEGATOR = <,
    RESTRICT = scalargtsel,
    JOIN = scalargtjoinsel
);
-- fertig mit den Operatoren


-- Funktionen zum Unterstützen der Operatorenklassen
CREATE FUNCTION btboolcmp(boolean2, boolean)
   RETURNS int4
   AS 'btboolcmp'
   LANGUAGE internal STRICT;
CREATE FUNCTION btboolcmp(boolean, boolean2)
   RETURNS int4
   AS 'btboolcmp'
   LANGUAGE internal STRICT;
CREATE FUNCTION btboolcmp(boolean2, boolean2)
   RETURNS int4
   AS 'btboolcmp'
   LANGUAGE internal STRICT;


-- erstelle Operatorenklassen
-- das ist notwendig für die Index Unterstützung
CREATE OPERATOR CLASS _bool2_ops
    DEFAULT FOR TYPE boolean2[] USING gin AS
    STORAGE boolean2 ,
    OPERATOR 1 &&(anyarray,anyarray) ,
    OPERATOR 2 @>(anyarray,anyarray) ,
    OPERATOR 3 <@(anyarray,anyarray) RECHECK ,
    OPERATOR 4 =(anyarray,anyarray) RECHECK ,
    FUNCTION 1 btboolcmp(boolean2,boolean2) ,
    FUNCTION 2 ginarrayextract(anyarray,internal) ,
    FUNCTION 3 ginarrayextract(anyarray,internal) ,
    FUNCTION 4 ginarrayconsistent(internal,smallint,internal);

CREATE OPERATOR CLASS bool2_ops
    DEFAULT FOR TYPE boolean2 USING btree AS
    OPERATOR 1 <(boolean2,boolean2) ,
    OPERATOR 2 <=(boolean2,boolean2) ,
    OPERATOR 3 =(boolean2,boolean2) ,
    OPERATOR 4 >=(boolean2,boolean2) ,
    OPERATOR 5 >(boolean2,boolean2) ,
    FUNCTION 1 btboolcmp(boolean2,boolean2);

CREATE OPERATOR CLASS bool2_ops
    DEFAULT FOR TYPE boolean2 USING hash AS
    OPERATOR 1 =(boolean2,boolean2) ,
    FUNCTION 1 hashchar("char");

Beispiel für die Nutzung des SQL-Präprozessors

#include <stdio.h>
#include <stdlib.h>
#include <unistd.h>


/* Exceptions müssen innerhalb des Quellcodes vor der Nutzung
 * definiert werden. Da die Exceptions vom Präprozessor
 * abgearbeitet werden, müssen die Defintionen jedoch nicht
 * innerhalb einer Funktion stehen. */
EXEC SQL WHENEVER SQLERROR SQLPRINT;


void ausgabe (void) {
  EXEC SQL BEGIN DECLARE SECTION;
  int id;
  VARCHAR inhalt[20];
  EXEC SQL END DECLARE SECTION;

  /* Bei einem Fehler gilt weiterhin die SQLPRINT Exception */

  EXEC SQL SELECT id, inhalt INTO :id, :inhalt FROM test;

  printf("\%i: \%s\n", id, inhalt.arr);


}

int main (void) {

  EXEC SQL CONNECT TO datenbank;


  /* Die SQLPRINT Exception hat keine Auswirkung auf die 
   * Funktion ausgabe(), da die Exception im Quellcode 
   * hinter der Funktion definiert wird */
  EXEC SQL WHENEVER SQLERROR STOP;

  /* allerdings hat die Änderung von SQLPRINT auf STOP Auswirkung
   * auf die nachfolgenden Anweisungen:
   * wenn die Tabelle "test" existiert, 
   * wird das Programm abgebrochen */

  EXEC SQL CREATE TABLE test (id SERIAL, inhalt VARCHAR(20));
  EXEC SQL INSERT INTO test (inhalt) VALUES ('Beispiel 1');
  EXEC SQL COMMIT;

  ausgabe();

  EXEC SQL DISCONNECT ALL;

  return 0;
}

Compilieren mit:

ecpg programm.pgc
gcc -Wall -lecpg -L`pg_config --libdir` -I`pg_config --includedir` -o programm programm.c

Ersatzfunktion für COUNT(*) Datentabelle

CREATE TABLE trigger_test (
  id        SERIAL         PRIMARY KEY,
  daten     TEXT           NOT NULL
);

Tabelle mit Zähler

CREATE TABLE trigger_count (
  id        SMALLINT       PRIMARY KEY
                           CHECK (id = 1),
  max_wert  INTEGER        NOT NULL,
  anzahl    INTEGER        NOT NULL
);

Zähler einfügen:

INSERT INTO trigger_count (id, max_wert, anzahl)
     VALUES (1, COALESCE(
(SELECT MAX(id) FROM trigger_test), 0),
             COALESCE(
(SELECT COUNT(id) FROM trigger_test), 0));

und überprüfen:

SELECT id, max_wert, anzahl FROM trigger_count;

Triggerfunktion zum Zählen erstellen

CREATE OR REPLACE FUNCTION
          trigger_count()
          RETURNS TRIGGER
AS $$
DECLARE
  loop_record RECORD;
BEGIN

  -- höchsten Wert herausfinden
  SELECT MAX(id) AS max_id,
         COUNT(id) AS anzahl
    INTO loop_record FROM trigger_test;
  IF loop_record.anzahl IS NULL THEN
    -- es gibt keine Einträge in der Tabelle
    UPDATE trigger_count SET max_wert = 0, anzahl = 0;
  ELSE
    -- den Maximalwert in die zweite Tabelle übertragen
    UPDATE trigger_count
       SET max_wert = loop_record.max_id,
           anzahl = loop_record.anzahl;
  END IF;

  RETURN NEW;
END;
$$
LANGUAGE 'plpgsql';

Trigger erstellen

 CREATE TRIGGER trigger_update
  AFTER INSERT OR UPDATE OR DELETE
     ON trigger_test FOR EACH ROW
EXECUTE PROCEDURE trigger_count();

Testdaten einfügen

INSERT INTO trigger_test (daten) VALUES ('Zeile 1');

INSERT INTO trigger_test (daten) VALUES ('Zeile 2');

INSERT INTO trigger_test (daten) VALUES ('Zeile 3');

Daten und Counter anschauen

SELECT id, daten FROM trigger_test;

SELECT id, max_wert, anzahl FROM trigger_count;

alle Einträge löschen

DELETE FROM trigger_test;

Daten und Counter anschauen

SELECT id, daten FROM trigger_test;

SELECT id, max_wert, anzahl FROM trigger_count;

Setzen der aktuellen Zeit

CREATE TABLE zeitstempel_demo (
  id             SERIAL       PRIMARY KEY,
  eingefuegt     TIMESTAMPTZ
                 NOT NULL,
  aktualisiert   TIMESTAMPTZ
                 NOT NULL,
  daten          VARCHAR,
  ...
);

CREATE OR REPLACE FUNCTION aktuelle_zeit_setzen()
                  RETURNS trigger
AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        NEW.eingefuegt := NOW();
    ELSE
        -- ursprünglichen Wert behalten beim Update
        NEW.eingefuegt := OLD.eingefuegt;
    END IF;

    -- Zeit der letzten Änderung setzen
    NEW.aktualisiert := NOW(); 

    RETURN NEW;
END;
$$
LANGUAGE 'plpgsql';

 CREATE TRIGGER addiere_aktuelle_zeit
 BEFORE INSERT OR UPDATE
     ON zeitstempel_demo
    FOR EACH ROW
EXECUTE PROCEDURE aktuelle_zeit_setzen();

INSERT testen

INSERT INTO zeitstempel_demo (daten) VALUES ('abc');

SELECT id, eingefuegt, aktualisiert, daten FROM zeitstempel_demo;

UPDATE testen

UPDATE zeitstempel_demo
   SET eingefuegt = '2020-01-01 00:00:00',
       aktualisiert = '2020-01-01 00:00:00',
       daten = 'def'
 WHERE id = 1;

SELECT id, eingefuegt, aktualisiert, daten FROM zeitstempel_demo;

Implizite Casts aus PostgreSQL 8.2

  CREATE FUNCTION pg_catalog.text(integer)
 RETURNS text STRICT IMMUTABLE
LANGUAGE SQL
      AS 'SELECT textin(int4out($1));';

  CREATE CAST (integer AS text)
    WITH FUNCTION pg_catalog.text(integer)
      AS IMPLICIT;

  CREATE FUNCTION pg_catalog.text(smallint)
 RETURNS text STRICT IMMUTABLE
LANGUAGE SQL
      AS 'SELECT textin(int2out($1));';

  CREATE CAST (smallint AS text)
    WITH FUNCTION pg_catalog.text(smallint)
      AS IMPLICIT;

  CREATE FUNCTION pg_catalog.text(oid)
 RETURNS text STRICT IMMUTABLE
LANGUAGE SQL
      AS 'SELECT textin(oidout($1));';

  CREATE CAST (oid AS text)
    WITH FUNCTION pg_catalog.text(oid)
      AS IMPLICIT;

  CREATE FUNCTION pg_catalog.text(date)
 RETURNS text STRICT IMMUTABLE
LANGUAGE SQL
      AS 'SELECT textin(date_out($1));';

  CREATE CAST (date AS text)
    WITH FUNCTION pg_catalog.text(date)
      AS IMPLICIT;

  CREATE FUNCTION pg_catalog.text(double precision)
 RETURNS text STRICT IMMUTABLE
LANGUAGE SQL
      AS 'SELECT textin(float8out($1));';

  CREATE CAST (double precision AS text)
    WITH FUNCTION pg_catalog.text(double precision)
      AS IMPLICIT;

  CREATE FUNCTION pg_catalog.text(real)
 RETURNS text STRICT IMMUTABLE
LANGUAGE SQL
      AS 'SELECT textin(float4out($1));';

  CREATE CAST (real AS text)
    WITH FUNCTION pg_catalog.text(real)
      AS IMPLICIT;

  CREATE FUNCTION pg_catalog.text(time with time zone)
 RETURNS text STRICT IMMUTABLE
LANGUAGE SQL
      AS 'SELECT textin(timetz_out($1));';

  CREATE CAST (time with time zone AS text)
    WITH FUNCTION pg_catalog.text(time with time zone)
      AS IMPLICIT;

  CREATE FUNCTION pg_catalog.text(time without time zone)
 RETURNS text STRICT IMMUTABLE
LANGUAGE SQL
      AS 'SELECT textin(time_out($1));';

  CREATE CAST (time without time zone AS text)
    WITH FUNCTION pg_catalog.text(time without time zone)
      AS IMPLICIT;

  CREATE FUNCTION pg_catalog.text(timestamp with time zone)
 RETURNS text STRICT IMMUTABLE
LANGUAGE SQL
      AS 'SELECT textin(timestamptz_out($1));';

  CREATE CAST (timestamp with time zone AS text)
    WITH FUNCTION pg_catalog.text(timestamp with time zone)
      AS IMPLICIT;

  CREATE FUNCTION pg_catalog.text(interval)
 RETURNS text STRICT IMMUTABLE
LANGUAGE SQL
      AS 'SELECT textin(interval_out($1));';

  CREATE CAST (interval AS text)
    WITH FUNCTION pg_catalog.text(interval)
      AS IMPLICIT;

  CREATE FUNCTION pg_catalog.text(bigint)
 RETURNS text STRICT IMMUTABLE
LANGUAGE SQL
      AS 'SELECT textin(int8out($1));';

  CREATE CAST (bigint AS text)
    WITH FUNCTION pg_catalog.text(bigint)
      AS IMPLICIT;

  CREATE FUNCTION pg_catalog.text(numeric)
 RETURNS text STRICT IMMUTABLE
LANGUAGE SQL
      AS 'SELECT textin(numeric_out($1));';

  CREATE CAST (numeric AS text)
    WITH FUNCTION pg_catalog.text(numeric)
      AS IMPLICIT;

  CREATE FUNCTION pg_catalog.text(timestamp without time zone)
 RETURNS text STRICT IMMUTABLE
LANGUAGE SQL
      AS 'SELECT textin(timestamp_out($1));';

  CREATE CAST (timestamp without time zone AS text)
     WITH FUNCTION pg_catalog.text(timestamp without time zone)
       AS IMPLICIT;

Die ursprüngliche Version dieser CASTs und Funktionen wurde von Peter Eisentraut in seinem Blog veröffentlicht.