PlItems

ID: 275
PlCodelang: PL/pgSQL
PlGroup: Datenbank
PlItemTitle: Stored Procedure anlegen/löschen
Code: CREATE OR REPLACE FUNCTION increment(i INT) RETURNS void AS $$ BEGIN INSERT INTO accounts (id,name, balance) VALUES (i, 'Test', 1200); END; $$ LANGUAGE plpgsql; DROP FUNCTION increment(i INT);
Result Example:

CREATE FUNCTION DROP FUNCTION

ID: 277
PlCodelang: PL/pgSQL
PlGroup: Datenbank
PlItemTitle: Datenbankobjekte Kommentar
Link Technical Documentation: https://www.postgresql.org/docs/9.1/sql-comment.html
Code: COMMENT ON FUNCTION increment(i INT) IS 'This is a comment';

ID: 288
PlCodelang: PL/pgSQL
PlGroup: Datum, Zeit
PlItemTitle: Gestriges Datum
Code: SELECT (now())::date SELECT (now() + interval '-1 day')::date
Result Example:

2020-12-13 2020-12-12

ID: 289
PlCodelang: PL/pgSQL
PlGroup: Strings
PlItemTitle: Führende Nullen vor einer Zahl
Code: WITH cte_example AS ( SELECT 5 AS int_example UNION ALL SELECT 10 AS int_example UNION ALL SELECT 50 AS int_example ) SELECT int_example, RPAD(int_example::text, 3, '0'), LPAD(int_example::text, 3, '0') FROM cte_example
Result Example:

---

int_example	rpad	lpad
5	500	005
10	100	010
50	500	050

ID: 299
PlCodelang: PL/pgSQL
PlGroup: Datenbank
PlItemTitle: Alle Datenbanken
Code: SELECT "oid", datname, -- database name datdba, "encoding", datcollate, datctype, datistemplate, datallowconn, datconnlimit, datlastsysoid, datfrozenxid, datminmxid, dattablespace, datacl FROM pg_catalog.pg_database WHERE datistemplate = FALSE -- do not list template0, template1, ... ;
Result Example:

oid	datname	datdba	encoding	datcollate	datctype	datistemplate	datallowconn	datconnlimit	datlastsysoid	datfrozenxid	datminmxid	dattablespace	datacl
12345	postgres	10	6	de_DE.UTF-8	de_DE.UTF-8	false	true	-1	13604	478	1	1663
1	template1	10	6	de_DE.UTF-8	de_DE.UTF-8	true	true	-1	13604	478	1	1663	{=c/postgres,postgres=CTc/postgres}

ID: 301
PlCodelang: PL/pgSQL
PlGroup: Array
PlItemTitle: Über alle Array-Elemente interieren
Link Reference: https://stackoverflow.com/questions/9783422/loop-over-array-dimension-in-plpgsql
Link Credits: https://stackoverflow.com/a/9784986
Code: DO $do$ DECLARE m text[]; arr text[] := '{{key1,val1},{key2,val2}}'; -- array literal BEGIN FOREACH m SLICE 1 IN ARRAY arr LOOP RAISE NOTICE 'another_func(%,%)', m[1], m[2]; END LOOP; END $do$; -- --------------------------------------------------------- DO $do$ DECLARE i text; arr text[] := '{key1,key2}'; -- array literal BEGIN FOREACH i IN ARRAY arr LOOP RAISE NOTICE 'another_func(%)', i; END LOOP; END $do$;

ID: 120
PlCodelang: PL/pgSQL
PlGroup: Strings
PlItemTitle: String in Dezimal konvertieren
Code: -- Da im Deutschen das Trennzeichen für eine -- Dezimalstelle ein Komma ist, muss dieses erst -- gegen einen Punkt ersetzt werden select cast(replace(cast('12.12' as varchar(10)),',','.') as numeric(12,2))

ID: 121
PlCodelang: PL/pgSQL
PlGroup: Datum, Zeit
PlItemTitle: Monat aus Datum extrahieren
Code: -- http://www.postgresql.org/docs/9.1/static/functions-datetime.html select extract(month from now())

ID: 123
PlCodelang: PL/pgSQL
PlGroup: Datum, Zeit
PlItemTitle: Alter zu gegebenen Datum ermitteln
Code: select age({ts '1983-08-21 17:25:00'})

ID: 124
PlCodelang: PL/pgSQL
PlGroup: Strings
PlItemTitle: String nach Delimenter zerlegen
Code: SELECT split_part('A;B;C;D;E',';',2) -- Result: B

ID: 146
PlCodelang: PL/pgSQL
PlGroup: Datenbank
PlItemTitle: Tabelle kopieren
Code: CREATE TABLE new_table AS SELECT * FROM old_table -- this action can't be done cross databases

ID: 147
PlCodelang: PL/pgSQL
PlGroup: Datum, Zeit
PlItemTitle: Jahr aus Datum extrahieren
Code: select extract(year from now())

ID: 148
PlCodelang: PL/pgSQL
PlGroup: Strings
PlItemTitle: Strings miteinander verbinden
Code: -- Bei PL/pgSQL sollte man die Funktion concat verwenden. -- Felder verbinden mit || kann zu NULL im Ergebnisfeld führen SELECT field_a ,field_b ,NULL as field_c ,concat(field_a, field_b, field_c) as together FROM table_1

ID: 150
PlCodelang: PL/pgSQL
PlGroup: Datenbank
PlItemTitle: DB-Funktion erstellen
Code: CREATE OR REPLACE FUNCTION whichContinent(country TEXT) RETURNS character varying AS $BODY$ DECLARE returnValue character varying; BEGIN SELECT INTO returnValue CASE country WHEN 'ARGENTINA' THEN 'SOUTH AMERICA' WHEN 'BELGIUM' THEN 'EUROPE' WHEN 'BRAZIL' THEN 'SOUTH AMERICA' WHEN 'CANADA' THEN 'NORTH AMERICA' WHEN 'DENMARK' THEN 'EUROPE' WHEN 'FINLAND' THEN 'EUROPE' WHEN 'FRANCE' THEN 'EUROPE' ELSE 'UNKNOWN' END; RETURN returnValue; END; $BODY$ LANGUAGE PLPGSQL;

ID: 152
PlCodelang: PL/pgSQL
PlGroup: Datenbank
PlItemTitle: Automatisch hochzählendes Feld
Code: CREATE TABLE myTable ( id SERIAL, field1 varchar(100) )

ID: 153
PlCodelang: PL/pgSQL
PlGroup: Datum, Zeit
PlItemTitle: Aktuelles Datum/Uhrzeit
Code: SELECT now();

ID: 154
PlCodelang: PL/pgSQL
PlGroup: Datenbank
PlItemTitle: Erstellen und Befüllen Tabelle aus Query
Code: DROP TABLE myTable; CREATE TABLE myTable AS SELECT * FROM mySecondTable;

ID: 156
PlCodelang: PL/pgSQL
PlGroup: Datum, Zeit
PlItemTitle: Monat aus Datum extrahieren
Code: -- You get the local name for the spoken month name SELECT to_char(now(), 'TMMonth') -- results in german to "Januar" or "Dezember" (= "January", "December")

ID: 157
PlCodelang: PL/pgSQL
PlGroup: Datum, Zeit
PlItemTitle: Erster und letzter Tag des Monats ermitteln
Code: select date_trunc('month', now()) -- first day in month select date_trunc('month', now()) + interval '1 month - 1 day' -- last day in month

ID: 181
PlCodelang: PL/pgSQL
PlGroup: Datenbank
PlItemTitle: NULL Wert eines Feldes übersteuern
Code: SELECT COALESCE(MyColumn1, 1.0) AS MyColumnWithoutNULL;