PostgreSQL SQL Tricks
SQL is a language where one task can be solved multiple ways with different efficiency. You can see effective use of PostgreSQL's possibilities on this page.
Processing PostgreSQL's logs in JSON format with jq
Author: Pavel Stěhule
The JSON logs are not too readable, but using jq is simple:
cat postgresql-Sun.json | jq 'select(.error_severity=="ERROR").message'|sort -n | uniq -c 1 "canceling statement due to user request" 1 "column \"de.id\" must appear in the GROUP BY clause or be used in an aggregate function" 1 "column reference \"modify_time\" is ambiguous" 3 "column \"us.show_name\" must appear in the GROUP BY clause or be used in an aggregate function" 24 "current transaction is aborted, commands ignored until end of transaction block" 3 "deadlock detected" cat postgresql-Sun.json | \ jq -r 'select(.error_severity=="ERROR") | [.timestamp, .user, .ps, .error_severity, .message ] | @csv' \ | pspg --csv
How to copy result to clipboard from psql
Author: Pavel Stěhule
psql doesn't support clipboard. But there are some special applications that does it (wlclipboard, xclip, pbcopy). psql can use it, and then it is easy to redirect result of query to libre office.
select * from obce \g (format=csv) | cat > tmpfile.csv; libreoffice --view --calc tmpfile.csv "--infilter='Text - txt - csv (StarCalc)':44,34,0,1"; rm tmpfile.csv select * from pg_class limit 10 \g (format=csv tuples_only=off csv_fieldsep='\t') | wl-copy -t application/x-libreoffice-tsvc \set gclip '\\g (format=csv tuples_only=off csv_fieldsep=\'\\t\') | wl-copy -t application/x-libreoffice-tsvc' select * from pg_class limit 10 :gclip
Arrays of jsonb
Author: Pavel Stěhule
PostgreSQL supports arrays of any non array types. Access to fields is implemented by usage square brackets. Same syntax is used for access to fields in jsonb documents. Because we use same syntax, we have problem when we want to set an field of array of json docs. Postgres doesn't supports arrays of arrays, so it cannot to use set of pair of brackets to access to different kind of object. But there is a workaround based on usage of composite types.
CREATE TYPE jsonb_singleval AS (v jsonb);
DO $$
DECLARE a jsonb_singleval[];
BEGIN
a[1].v['x1'] = jsonb '100';
RAISE NOTICE 'first item %', a[1].v;
END;
$$;
NOTICE: first item {"x1": 100}
DO
How to pass value from client side to server side
Author: Daniel Verite
System variable PGOPTION can be used for custom variables (GUC):
$ env PGOPTIONS="-c os.user=$USER" psql -d postgres psql (12.4 (Debian 12.4-1.pgdg90+1)) Type "help" for help. postgres=> show "os.user"; os.user --------- daniel (1 row)
případně s escapeovanými hodnotami:
$ env PGOPTIONS="-c os.user=user\\ is\\ $USER" psql -d postgres
psql (12.4 (Debian 12.4-1.pgdg90+1))
Type "help" for help.
postgres=> show "os.user";
os.user
----------------
user is daniel
(1 row)
Log all SQL statements generated by application
Author: Pavel Stěhule
You can enforce additional libpq connection string options with system variable PGOPTIONS. Next example ensure log all statements of pg_dump:
PGOPTIONS="-c log_min_duration_statement=0" pg_dump -s postgres > /dev/null
Vacuum system tables
Author: Pavel Stěhule
psql -At -c "select 'VACUUM ANALYZE pg_catalog.'||table_name from information_schema.tables where table_schema = 'pg_catalog' and table_type <> 'VIEW'" db_test \ | psql -S db_test
Setting DETAIL and HINT fields in PLPythonu exception
Author: Pavel Stěhule
PLPythonu doesn't allow to set all possible fields in PostgreSQL exception. There are a workaround for setting DETAIL and HINT:
postgres=# do $$
x = plpy.SPIError('Nazdarek');
x.spidata = (100, "Some detail", "some hint", None, None);
raise x;
$$ language plpythonu;
ERROR: plpy.SPIError: Nazdarek
DETAIL: Some detail
HINT: some hint
CONTEXT: Traceback (most recent call last):
PL/Python anonymous code block, line 4, in <module>
raise x;
PL/Python anonymous code block
Note: PostgreSQL 9.6 has native support.
Pretty xml formating
Author: Pavel Stěhule (internet - xslt template)
Extension xml2 supports xslt transformations. With function xslt_process a pretty printing of any xml documenty pretty simple:
create or replace function xml_pretty(xml) returns xml as $$ select xslt_process($1, '<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> <xsl:strip-space elements="*" /> <xsl:output method="xml" indent="yes" /> <xsl:template match="node() | @*"> <xsl:copy> <xsl:apply-templates select="node() | @*" /> </xsl:copy> </xsl:template> </xsl:stylesheet>')::xml $$ language sql immutable strict;
Example:
postgres=# select xml_pretty('<root><a><b>some text</b></a></root>');
xml_pretty
----------------------
<root> +
<a> +
<b>some text</b>+
</a> +
</root> +
(1 row)
Debugging communication between PostgreSQL and client
Author: Pavel Stěhule
There is great tool for this purpose - pgShark.
unbuffer ./pgs-debug --host 172.30.34.72 -i lo --port 6432 | while read line; do echo `date +"%T.%3N"` $line; done | gzip > /mnt/ebs/pgsharklog.gz
Fast replacing a index of PRIMARY KEY constraint
Author: Petr Novak
We can use a following steps, when we have replace a index for PRIMARY KEY constraint quickly:
BEGIN; CREATE UNIQUE INDEX CONCURRENTLY tab_pkey_idx2 ON tab(id); ALTER TABLE tab DROP CONSTRAINT tab_pkey CASCADE, ADD CONSTRAINT tab_pkey PRIMARY KEY USING INDEX tab_pkey_idx2; ALTER TABLE second_tab ADD CONSTRAINT second_tab_fkey FOREIGN KEY (tab_id) REFERENCES tab(id) NOT VALID; COMMIT;
Next step should be a FOREIGN KEY constraint validation.
Passing parameters from command line to DO statement
Author: Pavel Stehule
PostgreSQL statement DO doesn't support parameters. But we can pass parameters there by psql and server side session parameters:
bash-4.1$ cat test.sh
echo "
set myvars.msgcount TO :'msgcount';
DO \$\$
BEGIN
FOR i IN 1..current_setting('myvars.msgcount')::int LOOP
RAISE NOTICE 'Hello';
END LOOP;
END \$\$" | psql postgres -v msgcount=$1
bash-4.1$ sh test.sh 3
SET
Time: 0.341 ms
NOTICE: Hello
NOTICE: Hello
NOTICE: Hello
DO
Time: 2.415 ms
How to unnest an array with element indexes
Sometimes we need to unnest a array with indexes. There are more ways how to do it:
Author: Pavel Stěhule (PostgreSQL 9.1)
CREATE OR REPLACE FUNCTION unnest_rownum(anyarray)
RETURNS TABLE (id int, element anyelement) AS $$
BEGIN
id := 1;
FOREACH element IN array $1
LOOP
RETURN NEXT;
id := id + 1;
END LOOP;
RETURN;
END
$$ LANGUAGE plpgsql;
postgres=# select * from unnest_rownum(ARRAY['A','B','C']);
id | element
----+---------
1 | A
2 | B
3 | C
(3 rows)
Author: Tom Lane (PostgreSQL 9.3)
SELECT i, arraycol[i]
FROM tab,
LATERAL generate_subscripts(arraycol, 1) as i;
9.4 supports clause WITH ORDINALITY:
postgres=# SELECT * FROM unnest(ARRAY['A','D','C']) WITH ORDINALITY; unnest | ordinality --------+------------ A | 1 D | 2 C | 3 (3 rows)
Allow only one NULL in column
Author: Pavel Stěhule
UNIQUE constraint ensures unique values in column(s). But NULL is not a comparable value and unique constraint ignores these values. Sometime we would to allow only one NULL in column. There is a simply solution based on partial index and functional (here only constant) index.
postgres=# CREATE TABLE omega(a int UNIQUE); CREATE TABLE postgres=# INSERT INTO omega VALUES(NULL); INSERT 0 1 postgres=# INSERT INTO omega VALUES(NULL); INSERT 0 1 postgres=# TRUNCATE omega; TRUNCATE TABLE postgres=# CREATE UNIQUE INDEX ON omega ((1)) WHERE a IS NULL; CREATE INDEX postgres=# INSERT INTO omega VALUES(NULL); INSERT 0 1 postgres=# INSERT INTO omega VALUES(NULL); ERROR: duplicate key value violates unique constraint "omega_expr_idx" DETAIL: Key ((1))=(1) already exists. postgres=# UPDATE omega SET a = 10; UPDATE 1 postgres=# INSERT INTO omega VALUES(NULL); INSERT 0 1 Time: 7.601 ms
Bytea to BLOB conversion
Author: unknown
Store bytea string to LO object.
CREATE OR REPLACE FUNCTION make_lo(bytea)
RETURNS oid AS $$
DECLARE
loid oid;
fd integer;
bytes integer;
BEGIN
loid := lo_creat(-1);
fd := lo_open(loid, 131072);
bytes := lowrite(fd, $1);
IF (bytes != LENGTH($1)) THEN
RAISE EXCEPTION 'Not all data copied to blob';
END IF;
PERFORM lo_close(fd);
RETURN loid;
END;
$$ LANGUAGE plpgsql STRICT;
Only one column can hold NULL
Author: Daniel Vérité
How we can calculate how much columns hold a NULL value? Nice example of variadic function usage.
CREATE OR REPLACE FUNCTION public.null_count(VARIADIC anyarray) RETURNS integer LANGUAGE sql AS $function$ SELECT sum(CASE WHEN v IS NULL THEN 1 ELSE 0 END)::int FROM unnest($1) g(v) $function$
Usage:
CREATE TABLE xxx( a int, b int, c int, CHECK (null_count(a,b,c) <= 1))
Overwriting a application name in psql (for psql based script identification)
Author: Erik Rijkers
Application_name is one of connection params, that we can attach to log event and later we can to identify a SQL statement producent. There are more possibilities how to do it (one is a example of connection string in url format).
[pavel@localhost ~]$ PGAPPNAME=splunk psql -c "select application_name from pg_stat_activity where pid = pg_backend_pid();" postgres application_name ------------------ splunk (1 row) [pavel@localhost ~]$ psql -c "select application_name from pg_stat_activity where pid = pg_backend_pid();" "dbname=postgres application_name=splunk" application_name ------------------ splunk (1 row) [pavel@localhost ~]$ psql -c "select application_name from pg_stat_activity where pid = pg_backend_pid();" postgresql://localhost/postgres?application_name=splunk application_name ------------------ splunk (1 row)
Getting N unique most similar values with usage KNNsearch and pg_tgrm module
Author: Tom Lane
SELECT DISTINCT ON (village_name <-> 'Benešov') village_name, village_name<->'Benešov'
FROM villages
ORDER BY village_name <-> 'Benešov'
LIMIT 10;
village_name | ?column?
-----------------------+----------
Benešov | 0
Benešovice | 0.416667
Dolní Benešov | 0.428571
Benešov u Semil | 0.5
Benešov nad Černou | 0.578947
Benešov nad Ploučnicí | 0.636364
Benecko | 0.666667
Benetice | 0.692308
Bečov | 0.727273
Bezkov | 0.75
(10 rows)
postgres=# EXPLAIN SELECT DISTINCT ON (village_name <-> 'Benešov') village_name, village_name<->'Benešov'
FROM obce
ORDER BY village_name <-> 'Benešov'
LIMIT 10;
QUERY PLAN
-------------------------------------------------------------------------------------------------
Limit (cost=0.00..2.76 rows=10 width=10)
-> Unique (cost=0.00..1474.75 rows=5341 width=10)
-> Index Scan using villages_village_name_idx on obce (cost=0.00..1433.14 rows=16644 width=10)
Order By: (village_name <-> 'Benešov'::text)
(4 rows)
Using custom casting when you migrate from Oracle to PostgreSQL
Author: Pavel Stěhule
Oracle doesn't support boolean data type, and developers use a varchar(1) or number(1) instead. It is a issue in PostgreSQL because this width is too less for storing a strings "true" or "false". But we can redefine a default casting (there is used a ugly hack - direct update of system tables under superuser rights). This solution is fast hack - changing to PostgreSQL's boolean is much better.
CREATE OR REPLACE FUNCTION public.boolcast_varchar(boolean)
RETURNS character varying
LANGUAGE sql
AS $$
SELECT CASE WHEN $1 THEN 't' ELSE 'f' END
$$
UPDATE pg_cast SET castfunc='boolcast_varchar'::regproc,
castcontext='i'
WHERE castsource=16 and casttarget=1043;
CREATE TABLE test(a varchar(1));
INSERT INTO test VALUES(true);
JSON creating
Author: Merlin
select
row_to_json(t1)
from (
select
'joe' as username,
(select project from (values(1, 'prj1')) as project(project_id,
project_name)) as project
) t1;
A array's fields trimming
Author: a_horse_with_no_name
postgres=# select * from g;
a
--------------------------------
{" ssss ssss","sss ssss "}
{" ssss ssssaaaa "}
(2 rows)
postgres=# select row_number() over(), unnest(a) e
from g;
row_number | e
------------+-----------------
1 | ssss ssss
1 | sss ssss
2 | ssss ssssaaaa
(3 rows)
postgres=# select array_agg(trim(x.e))
from (select row_number() over() rn, unnest(a) e
from g) x
group by rn;
array_agg
---------------------------
{"ssss ssss","sss ssss"}
{"ssss ssssaaaa"}
(2 rows)
Attention: This trick depends on imlementation - and should not work in future releases. If you use a 9.3 and newer PostgreSQL, use a LATERAL join instead.
Simply query result processing in bash
Author: Pavel Stěhule
psql has not complete macro language, but we can use bash and we can use features, that provides bash:
pavel ~ $ psql postgres -A -t --field-separator=" " \ > -c "copy (select 1,'Ahoj Svete', i > from generate_series(1,3) g(i)) > to stdout delimiter ' '" | \ > while read var1 var2 var3; > do > echo "a=$var1,b=$var2,c=$var3 "; > done a=1,b=Ahoj Svete,c=1 a=1,b=Ahoj Svete,c=2 a=1,b=Ahoj Svete,c=3
Similar solution designed by Hubert Depesz Lubaczewski:
psql -qAtX -c "copy (select * from t) to stdout" | while IFS=$'\t' read -r a b c; do echo -e "a=[$a] b=[$b] c=[$c]"; done
Glue comment to query
Author: Thomas Kellerer
When you need append notice to query, you can use nested comment:
SELECT /* my comments, that I would to see in PostgreSQL log */
a, b, c
FROM mytab;
Logout all connections
Author: Pavel Stěhule
Execute query as user postgres:
postgres=# select * from pg_stat_activity ;
datid | datname | pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start |
-------+----------+-------+----------+----------+------------------+-------------+-----------------+-------------+-------------------------------+
12894 | postgres | 21091 | 10 | postgres | psql | | | -1 | 2012-03-06 09:16:02.466983+01 |
12894 | postgres | 21103 | 16384 | pavel | psql | | | -1 | 2012-03-06 09:17:02.827352+01 |
12894 | postgres | 21104 | 16384 | pavel | psql | | | -1 | 2012-03-06 09:17:12.176979+01 |
(3 rows)
postgres=# select pg_terminate_backend(pid)
from pg_stat_activity
where pid <> pg_backend_pid() ;
pg_terminate_backend
----------------------
t
t
(2 rows)
postgres=# select * from pg_stat_activity ;
datid | datname | pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start |
-------+----------+-------+----------+----------+------------------+-------------+-----------------+-------------+-------------------------------+
12894 | postgres | 21091 | 10 | postgres | psql | | | -1 | 2012-03-06 09:16:02.466983+01 |
(1 row)
Taking first unlocked row from table
Sometimes we need first unlocked row (queue implementation). We can use following trick:
postgres=# select * from queue; id | processed | cmd ----+-----------+------- 1 | f | task1 2 | f | task2 3 | f | task2 4 | f | task3 (4 rows)
Usage:
-- consument1 postgres=# begin; BEGIN postgres=# select * from queue where not processed and pg_try_advisory_xact_lock(id) for update limit 1; id | processed | cmd ----+-----------+------- 1 | f | task1 (1 row) postgres=# update queue set processed = true where id = 1; UPDATE 1 postgres=# commit; COMMIT -- consument2 postgres=# begin; BEGIN postgres=# select * from queue where not processed and pg_try_advisory_xact_lock(id) for update limit 1; id | processed | cmd ----+-----------+------- 2 | f | task2 (1 row) postgres=# update queue set processed = true where id = 2; UPDATE 1 postgres=# commit; COMMIT
With advisory locks we can implement unblocked queue parallel processing.
Searching gaps in time series with window analytic functions
Source: Magnus Hagander http://blog.hagander.net/archives/203-Finding-gaps-in-partitioned-sequences.html
Function lag can be used for any sorted series:
SELECT * FROM (
SELECT
gropid,
year,
month,
seq,
seq-lag(seq,1) OVER (PARTITION BY groupid, year, month ORDER BY seq) AS gap FROM mytable
) AS t
WHERE NOT (t.gap=1)
ORDER BY groupid, year, month, seq
Delete duplicate rows with window analytic functions
We can use a window function for very effective removing of duplicate rows:
DELETE FROM tab
WHERE id IN (SELECT id
FROM (SELECT row_number() OVER (PARTITION BY column_with_duplicate_values), id
FROM tab) x
WHERE x.row_number > 1);
Some PostgreSQL's optimized version (with ctid):
DELETE FROM tab
WHERE (ctid, tableoid) = ANY(ARRAY(SELECT (ctid, tableoid)
FROM (SELECT row_number() OVER (PARTITION BY column_with_duplicate_values), ctid
FROM tab) x
WHERE x.row_number > 1));
LIKE to list of patterns
PostgreSQL cannot to use (I)LIKE together list of patterns (in conformance with ANSI SQL):
-- this is not valid
WHERE somecolumn LIKE ('%some%','%someelse%')
But we can use arrays or regular expressions:
-- this is valid WHERE somecolumn ~~* any(array['%some%', '%someelse']));
Author: Tom Lane
Enforcing unique name for cursor in PL/pgSQL
PL/pgSQL cursors uses PostgreSQL SQL cursors. When we use PL/pgSQL cursor, then PL/pgSQL runtime creates SQL cursor with same name. This behave is unwanted sometimes. But when we know, so PL/pgSQL cursor is +/- text variable with SQL name, we can assign NULL to this variable. Then PL/pgSQL runtime cannot to use a predefined name and it will generate a unique name:
DECLARE mycursor CURSOR FOR SELECT * FROM int4_tbl WHERE f1 > id; newid INTEGER; out TEXT; BEGIN out := id::text || ' '; mycursor := null; OPEN mycursor; ..
Conversion BLOB to Bytea
Source: http://snipplr.com/view/62576/convert-oid-to-bytea/
CREATE OR REPLACE FUNCTION merge_oid(val oid)
RETURNS bytea AS $$
DECLARE
merged bytea;
arr bytea;
BEGIN
FOR arr IN SELECT DATA
FROM pg_largeobject
WHERE loid = val
ORDER BY pageno
LOOP
IF merged IS NULL THEN
merged := arr;
ELSE
merged := merged || arr;
END IF;
END LOOP;
RETURN merged;
END
$$ LANGUAGE plpgsql;
Function for decoding of url code
Author: Marti Raudsepp
CREATE OR REPLACE FUNCTION url_decode(input text) RETURNS text
LANGUAGE plpgsql IMMUTABLE STRICT AS $$
DECLARE
bin bytea = '';
byte text;
BEGIN
FOR byte IN (select (regexp_matches(input, '(%..|.)', 'g'))[1]) LOOP
IF length(byte) = 3 THEN
bin = bin || decode(substring(byte, 2, 2), 'hex');
ELSE
bin = bin || byte::bytea;
END IF;
END LOOP;
RETURN convert_from(bin, 'utf8');
END
$$;
Usage:
ohs=# select url_decode('Hell%C3%B6%20World%21');
url_decode
──────────────
Hellö World!
(1 row)
Another version (by Marc Mamin)
CREATE OR REPLACE FUNCTION urldecode_arr(url text)
RETURNS text AS $$
BEGIN
RETURN
(WITH str AS (SELECT CASE WHEN $1 ~ '^%[0-9a-fA-F][0-9a-fA-F]' THEN array[''] END
|| regexp_split_to_array ($1, '(%[0-9a-fA-F][0-9a-fA-F])+', 'i') plain,
ARRAY(SELECT (regexp_matches ($1, '((?:%[0-9a-fA-F][0-9a-fA-F])+)', 'gi'))[1]) encoded)
SELECT coalesce(string_agg(plain[i] || coalesce( convert_from(decode(replace(encoded[i], '%',''), 'hex'), 'utf8'), ''), ''), $1)
FROM str,
(SELECT generate_series(1, array_upper(encoded,1) + 2) i FROM str) blah);
END
$$ LANGUAGE plpgsql IMMUTABLE STRICT;
Emacs's configuration for PLpgSQL syntax highlighting when function is edited from console
Author: Peter Eisentraut
modify file .profile
PSQL_EDITOR=emacs; export PSQL_EDITOR.
modify file .emacs
(add-to-list 'auto-mode-alist
'("/psql.edit.[0-9]+\\'" . sql-mode))
Cast bytea to text
When we need cast content of bytea variable to adequate text value directly (content of bytea is char codes), then following function is solution:
CREATE OR REPLACE FUNCTION bytea_to_text(bytea)
RETURNS text AS $$
SELECT convert_from($1, current_setting('server_encoding'))
$$ LANGUAGE sql;
xxx=# SELECT bytea_to_text('žluťoučký kůň se napil žluté vody'::bytea);
bytea_to_text
───────────────────────────────────
žluťoučký kůň se napil žluté vody
(1 row)
Simply age calculation from birth date
postgres=# SELECT CURRENT_DATE;
date
------------
2011-09-20
(1 row)
postgres=# SELECT EXTRACT(YEAR FROM age('1972-08-20'::date));
date_part
-----------
39
(1 row)
Is current server master or slave?
When we need to identify server's state, then we can use a function:
SELECT pg_is_in_recovery();
Author: Simon Riggs
Dynamic modification some record's field
PL/pgSQL can modify record's field only when field specification is static. Some fast C libraries are generally available, but when we have no necessary rights, then we cannot use it. Then we can use function:
CREATE OR REPLACE FUNCTION x.setfield2(anyelement, text, text)
RETURNS anyelement
LANGUAGE plpgsql
AS $function$
DECLARE
_name text;
_values text[];
_value text;
_attnum int;
BEGIN
FOR _name, _attnum
IN SELECT a.attname, a.attnum
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = (SELECT typrelid
FROM pg_type
WHERE oid = pg_typeof($1)::oid)
LOOP
IF _name = $2 THEN
_value := $3;
ELSE
EXECUTE 'SELECT (($1).' || quote_ident(_name) || ')::text' INTO _value USING $1;
END IF;
_values[_attnum] := COALESCE('"' || replace(replace(_value, '"', '""'), '''', '''''') || '"', '');
END LOOP;
EXECUTE 'SELECT (' || pg_typeof($1)::text || '''(' || array_to_string(_values,',') || ')'').*' INTO $1;
RETURN $1;
END;
$function$;
postgres=# select setfield2(mypoint '(10,)', 'b', '33');
setfield2
───────────
(10,33)
(1 row)
Time: 9,480 ms
Author: Pavel Stěhule
Erwin Brandstetter designed better (more simply, faster) solution:
-- Erwin 1
CREATE OR REPLACE FUNCTION x.setfield3(anyelement, text, text)
RETURNS anyelement
AS $body$
DECLARE
_list text;
BEGIN
_list := (
SELECT string_agg(x.fld, ',')
FROM (
SELECT CASE WHEN a.attname = $2
THEN quote_literal($3)
ELSE quote_ident(a.attname)
END AS fld
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = (SELECT typrelid
FROM pg_type
WHERE oid = pg_typeof($1)::oid)
ORDER BY a.attnum
) x
);
EXECUTE '
SELECT ' || _list || '
FROM (SELECT $1.*) x'
USING $1
INTO $1;
RETURN $1;
END;
$body$ LANGUAGE plpgsql;
or modernised final version
CREATE FUNCTION f_setfield(INOUT _comp_val anyelement, _field text, _val text)
RETURNS anyelement AS
$func$
BEGIN
EXECUTE 'SELECT ' || array_to_string(ARRAY(
SELECT CASE WHEN attname = _field
THEN '$2'
ELSE '($1).' || quote_ident(attname)
END AS fld
FROM pg_catalog.pg_attribute
WHERE attrelid = pg_typeof(_comp_val)::text::regclass
AND attnum > 0
AND attisdropped = FALSE
ORDER BY attnum
), ',')
USING _comp_val, _val
INTO _comp_val;
END
$func$ LANGUAGE plpgsql;
Iteration over RECORD variable inside trigger
Author: Tom Lane (for PostgreSQL 8.4 and higher)
CREATE OR REPLACE FUNCTION dynamic_trigger()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
ri RECORD;
t TEXT;
BEGIN
RAISE NOTICE E'\n Operation: %\n Schema: %\n Table: %',
TG_OP,
TG_TABLE_SCHEMA,
TG_TABLE_NAME;
FOR ri IN
SELECT ordinal_position, column_name, data_type
FROM information_schema.columns
WHERE
table_schema = quote_ident(TG_TABLE_SCHEMA)
AND table_name = quote_ident(TG_TABLE_NAME)
ORDER BY ordinal_position
LOOP
EXECUTE 'SELECT ($1).' || ri.column_name || '::text' INTO t USING NEW;
RAISE NOTICE E'Column\n number: %\n name: %\n type: %\n value: %.',
ri.ordinal_position,
ri.column_name,
ri.data_type,
t;
END LOOP;
RETURN NEW;
END; $$;
Note: This technique needs one dynamic SQL per one record's field - functions from PL Toolbox library or from Hstore contrib module are more effective.
Enforcing some chars in to_char function
Sometimes we needs to show some chars, that are same like special chars (has some semantic) from mask. Text in quotes (in format mask) is not interpreted and it is showed without changes:
postgres=# select to_char(current_timestamp,'YYYY-MM-DDTHH24:MI:SS');
to_char
-----------------------
2010-02-07THH24:38:10
(1 row)
postgres=# select to_char(current_timestamp,'YYYY-MM-DD"T"HH24:MI:SS');
to_char
---------------------
2010-02-07T07:38:22
(1 row)
Author: Milen A. Radev
Barrier against to unwanted conversion char(n) to text
Almost all string functions have parameters of text type. PostgreSQL ensures automatic conversion from char(n) to varchar. But there are a few cases where this behave causes problems - mainly for older applications, where char(n) type was used (there is a clean advice - don't use this type). In some legacy application is comparison between char(n) type and literal constant with spaces and it doesn't work in PostgreSQL now:
SELECT ... WHERE substr(somecolumn,1,4) = 'ab ';
It doesn't work, because substr function has text parameter - and returns text and 'ab' is not equal to 'ab '. But we can overload substr function with char(n) datatype (it is possible because varchar and char uses same binary format) - and it doesn't do unwanted conversion from char(n) to text:
create or replace function substr(character, int, int) returns character as $$ select substr($1::cstring::text,$2,$3) $$ language sql;
Tome Lane wrote more effective proposal:
create function substr(char,int,int) returns char strict immutable language internal as 'text_substr' ;
It is little bit dirty trick, but 100% functional.
postgres=# create table f(a character(5));
CREATE TABLE
postgres=# insert into f values('a'),('ab'),('abc');
INSERT 0 3
postgres=# select * from f;
a
-------
a
ab
abc
(3 rows)
postgres=# select * from f where substr(a,1,3) = 'a ';
a
-------
a
(1 row)
postgres=# select * from f where substr(a,1,3) = 'ab ';
a
-------
ab
(1 row)
Get functions's DDL command
PostgreSQL allows simple way to get full source code of any function:
postgres=# select oid from pg_proc where proname = 'fu';
oid
-------
16389
(1 row)
postgres=# select pg_get_functiondef(16389);
pg_get_functiondef
---------------------------------------------------------------------
CREATE OR REPLACE FUNCTION public.fu(anyelement)
RETURNS anyelement
LANGUAGE plpgsql
AS $function$
begin
raise notice '%', $1;
return $1;
end; $function$+
(1 row)
from Russian alphabet (Cyrillic) to ASCII
source: http://leha.papulovskiy.ru/blog/
CREATE OR REPLACE FUNCTION urltranslit(text) RETURNS text as $$ SELECT regexp_replace( replace( replace( replace( replace( replace( replace( replace( translate( lower($1), 'абвгдеёзийклмнопрстуфхыэъь', 'abvgdeezijklmnoprstufhye' ), 'ж', 'zh' ), 'ц', 'ts' ), 'ч', 'ch' ), 'ш', 'sh' ), 'щ', 'sch' ), 'ю', 'yu' ), 'я', 'ya' ) , '[^a-z]+', '-', 'g' ) $$ LANGUAGE SQL;