You may optionally enter a comparison operator (<, <=, >, >=, <> or =) at the beginning of each of your search values to specify how the comparison should be done.
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
299
PL/pgSQL
Datenbank
Alle Datenbanken
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, ...
;
301
PL/pgSQL
Array
Über alle Array-Elemente interieren
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$;
120
PL/pgSQL
Strings
String in Dezimal konvertieren
-- 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))
121
PL/pgSQL
Datum, Zeit
Monat aus Datum extrahieren
-- http://www.postgresql.org/docs/9.1/static/functions-datetime.html
select extract(month from now())
123
PL/pgSQL
Datum, Zeit
Alter zu gegebenen Datum ermitteln
select age({ts '1983-08-21 17:25:00'})
124
PL/pgSQL
Strings
String nach Delimenter zerlegen
SELECT split_part('A;B;C;D;E',';',2)
-- Result: B
146
PL/pgSQL
Datenbank
Tabelle kopieren
CREATE TABLE new_table AS SELECT * FROM old_table
-- this action can't be done cross databases
