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.