Saturday, August 22, 2009

MySQL functions for PostgreSQL

Hello

I found article about MySQL function field. PostgreSQL doesn't support similar function (it is nice together with ORDER BY expression clause), but with new variadic parameters is easy to develop it in pg:

CREATE OR REPLACE FUNCTION field(text, variadic text[])
RETURNS int AS $$
  SELECT i
     FROM generate_subscripts($2,1) g(i)
    WHERE $1 = $2[i]
    UNION ALL
    SELECT 0
    LIMIT 1
$$ LANGUAGE sql STRICT;
An using looks like:
select * from pet order by field(species, 'cat', 'dog', 'bird');

+----------+--------+---------+------+------------+------------+
| name     | owner  | species | sex  | birthday   | death      |
+----------+--------+---------+------+------------+------------+
| Fluffy   | Harold | cat     | f    | 1993-02-04 | NULL       |
| Claws    | Gwen   | cat     | m    | 1994-03-17 | NULL       |
| Buffy    | Harold | dog     | f    | 1989-05-13 | NULL       |
| Fang     | Benny  | dog     | m    | 1990-08-27 | NULL       |
| Bowser   | Diane  | dog     | m    | 1989-08-31 | 1995-07-29 |
| Chirpy   | Gwen   | bird    | f    | 1998-09-11 | NULL       |
| Whistler | Gwen   | bird    | NULL | 1997-12-09 | NULL       |
+----------+--------+---------+------+------------+------------+
When I played with it, I ported mostly used MySQL functions to PostgreSQL. I hope, so this file can help somebody with porting applications to PostgreSQL or with better orientation in PostgreSQL. Lot of thing with strings or date PostgreSQL is done little bit different than MySQL - these functions can help.

String Functions

CREATE OR REPLACE FUNCTION concat(variadic str text[])
RETURNS text AS $$
SELECT array_to_string($1, '');
$$ LANGUAGE sql

CREATE OR REPLACE FUNCTION concat_ws(separator text, variadic str text[])
RETURNS text as $$
SELECT array_to_string($2, $1);
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION elt(int, VARIADIC text[])
RETURNS text AS $$
SELECT $2[$1];
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION field(varchar, VARIADIC text[])
RETURNS int AS $$
SELECT i
FROM generate_subscripts($2,1) g(i)
WHERE $1 = $2[i]
UNION ALL
SELECT 0
LIMIT 1
$$ LANGUAGE sql STRICT;

CREATE OR REPLACE FUNCTION find_in_set(str text, strlist text)
RETURNS int AS $$
SELECT i
   FROM generate_subscripts(string_to_array($2,','),1) g(i)
  WHERE (string_to_array($2, ','))[i] = $1
  UNION ALL
  SELECT 0
  LIMIT 1
$$ LANGUAGE sql STRICT;

CREATE OR REPLACE FUNCTION hex(int)
RETURNS text AS $$
SELECT upper(to_hex($1));
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION hex(bigint)
RETURNS text AS $$
SELECT upper(to_hex($1));
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION hex(text)
RETURNS text AS $$
SELECT upper(encode($1::bytea, 'hex'))
$$ LANGUAGE sql;

/*
* char is keyword, double quotes are necessary.
*
*  postgres=# select "char"(77,121,83,81,'76');
*  char
* -------
*  MySQL
*/
CREATE OR REPLACE FUNCTION "char"(VARIADIC int[])
RETURNS text AS $$
SELECT array_to_string(ARRAY(SELECT chr(unnest($1))),'')
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION lcase(str text)
RETURNS text AS $$
SELECT lower($1)
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION left(str text, len int)
RETURNS text AS $$
SELECT substring($1 FROM 1 FOR $2)
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION locate(substr text, str text)
RETURNS int AS $$
SELECT position($1 in $2)
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION reverse(str text)
RETURNS text AS $$
SELECT array_to_string(ARRAY(SELECT substring($1 FROM i FOR 1)
                                FROM generate_series(length($1),1,-1) g(i)),
                       '')
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION right(str text, len int)
RETURNS text AS $$
SELECT substring($1 FROM length($1) - $2 FOR $2)
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION space(n int)
RETURNS text AS $$
SELECT repeat(' ', $1)
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION strcmp(text, text)
RETURNS int AS $$
SELECT CASE WHEN $1 < $2 THEN -1
WHEN $1 > $2 THEN 1
ELSE 0 END;
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION substring_index(str text, delim text, count int)
RETURNS text AS $$
SELECT CASE WHEN $3 > 0 
THEN array_to_string((string_to_array($1, $2))[1:$3], $2)
ELSE array_to_string(ARRAY(SELECT unnest(string_to_array($1,$2))
                             OFFSET array_upper(string_to_array($1,$2),1) + $3),
                     $2)
END
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION ucase(str text)
RETURNS text AS $$
SELECT upper($1)
$$ LANGUAGE sql;

CREATE CAST (bytea AS text) WITHOUT FUNCTION AS ASSIGNMENT;

/*
* SELECT hex('žlutý kůň'), unhex(hex('žlutý kůň'))
*/
CREATE OR REPLACE FUNCTION unhex(text)
RETURNS text AS $$
SELECT decode($1, 'hex')::text;
$$ LANGUAGE sql;
Note: On some newer PostgreSQL the function unhex doesn't work well - you can use a second version, that works well for ascii chars or for utf8:
CREATE OR REPLACE FUNCTION unhex(text)
RETURNS text AS $$
SELECT encode(decode($1, 'hex'),'escape');
$$ LANGUAGE sql;

-- UTF8 version
CREATE OR REPLACE FUNCTION unhex(text)
RETURNS text AS $$
SELECT convert_from(decode($1, 'hex'),'utf8');
$$ LANGUAGE sql;
if you have a superuser rights, you can use a "better" auxilary function, that ensure casting from bytea to text type without changes (it's relative dirty trick):
CREATE OR REPLACE FUNCTION public.direct_bytea_to_cstring(bytea)
 RETURNS cstring
 LANGUAGE internal
 IMMUTABLE STRICT
AS $function$textout$function$

CREATE OR REPLACE FUNCTION unhex(text)
RETURNS text AS $$
SELECT direct_bytea_to_cstring(decode($1, 'hex'))::text;
$$ LANGUAGE sql;
CREATE FUNCTION
You can to try use cast (and use a original function) - you have to have a superuser rights too (this cast can be entered by postgres user only):
create cast (bytea as text) without function;

Date, time Functions

/*
* postgres=# select adddate('2008-01-02','31 day');
*   adddate
* -----------
*  2008-02-02
*/
CREATE OR REPLACE FUNCTION adddate(date, interval)
RETURNS date AS $$
SELECT ($1 + $2)::date; $$
LANGUAGE sql;

CREATE OR REPLACE FUNCTION curdate()
RETURNS date AS $$
SELECT CURRENT_DATE
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION convert_tz(dt timestamp, from_tz text, to_tz text)
RETURNS timestamp AS $$
SELECT ($1 AT TIME ZONE $2) AT TIME ZONE $3;
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION date(anyelement)
RETURNS date AS $$
SELECT $1::date;
$$ LANGUAGE sql;

SELECT OR REPLACE FUNCTION datediff(date, date)
RETURNS int AS $$
SELECT $1 - $2
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION date_add(date, interval)
RETURNS date AS $$
SELECT adddate($1, $2)
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION date_format(date, text)
RETURNS text AS $$
SELECT to_char($1, _mysqlf_pgsql($2))
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION date_format(timestamp, text)
RETURNS text AS $$
SELECT to_char($1, _mysqlf_pgsql($2))
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION date_sub(date, interval)
RETURNS date AS $$
SELECT ($1 - $2)::date;
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION dayofmonth(date)
RETURNS int AS $$
SELECT EXTRACT(day from $1)::int
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION day(date)
RETURNS int AS $$
SELECT dayofmonth($1)
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION dayname(date)
RETURNS text AS $$
SELECT to_char($1, 'TMDay')
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION dayofweek(date)
RETURNS int AS $$
SELECT EXTRACT(dow FROM $1)::int
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION dayofyear(date)
RETURNS int AS $$
SELECT EXTRACT(doy FROM $1)::int
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION from_days(int)
RETURNS date AS $$
SELECT date '0001-01-01bc' + $1
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION from_unixtime(double precision)
RETURNS timestamp AS $$
SELECT to_timestamp($1)::timestamp
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION _mysqlf_pgsql(text)
RETURNS text AS $$
SELECT array_to_string(ARRAY(SELECT s
FROM (SELECT CASE WHEN substring($1 FROM i FOR 1) <> '%'
AND substring($1 FROM i-1 FOR 1) <> '%'
THEN substring($1 FROM i for 1)
ELSE CASE substring($1 FROM i FOR 2)
WHEN '%H' THEN 'HH24'
WHEN '%p' THEN 'am'
WHEN '%Y' THEN 'YYYY'
WHEN '%m' THEN 'MM'
WHEN '%d' THEN 'DD'
WHEN '%i' THEN 'MI'
WHEN '%s' THEN 'SS'
WHEN '%a' THEN 'Dy'
WHEN '%b' THEN 'Mon'
WHEN '%W' THEN 'Day'
WHEN '%M' THEN 'Month'
END
END s
FROM generate_series(1,length($1)) g(i)) g
WHERE s IS NOT NULL),
'')
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION get_format(text, text)
RETURNS text AS $$
SELECT CASE lower($1)
WHEN 'date' THEN
CASE lower($2)
WHEN 'usa' THEN '%m.%d.%Y'
WHEN 'jis' THEN '%Y-%m-%d'
WHEN 'iso' THEN '%Y-%m-%d'
WHEN 'eur' THEN '%d.%m.%Y'
WHEN 'internal' THEN '%Y%m%d'
END
WHEN 'datetime' THEN
CASE lower($2)
WHEN 'usa' THEN '%Y-%m-%d %H-.%i.%s'
WHEN 'jis' THEN '%Y-%m-%d %H:%i:%s'
WHEN 'iso' THEN '%Y-%m-%d %H:%i:%s'
WHEN 'eur' THEN '%Y-%m-%d %H.%i.%s'
WHEN 'internal' THEN '%Y%m%d%H%i%s'
END
WHEN 'time' THEN
CASE lower($2)
WHEN 'usa' THEN '%h:%i:%s %p'
WHEN 'jis' THEN '%H:%i:%s'
WHEN 'iso' THEN '%H:%i:%s'
WHEN 'eur' THEN '%H.%i.%s'
WHEN 'internal' THEN '%H%i%s'
END
END;
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION hour(time)
RETURNS int AS $$
SELECT EXTRACT(hour FROM $1)::int;
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION hour(timestamp)
RETURNS int AS $$
SELECT EXTRACT(hour FROM $1)::int;
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION last_day(date)
RETURNS date AS $$
SELECT (date_trunc('month',$1 + interval '1 month'))::date - 1
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION makedate(year int, dayofyear int)
RETURNS date AS $$
SELECT (date '0001-01-01' + ($1 - 1) * interval '1 year' + ($2 - 1) * interval '1 day'):: date
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION maketime(int, int, double precision)
RETURNS time AS $$
SELECT time '00:00:00' + $1 * interval '1 hour' + $2 * interval '1 min'
+ $3 * interval '1 sec'
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION minute(timestamp)
RETURNS int AS $$
SELECT EXTRACT(minute FROM $1)::int
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION month(date)
RETURNS int AS $$
SELECT EXTRACT(month FROM $1)::int
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION monthname(date)
RETURNS text AS $$
SELECT to_char($1, 'TMMonth')
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION str_to_date(text, text)
RETURNS date AS $$
SELECT to_date($1, _mysqlf_pgsql($2))
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION time(timestamp)
RETURNS time AS $$
SELECT $1::time
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION to_days(date)
RETURNS int AS $$
SELECT $1 - '0001-01-01bc'
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION unix_timestamp()
RETURNS double precision AS $$
SELECT EXTRACT(epoch FROM current_timestamp)
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION unix_timestamp(timestamp)
RETURNS double precision AS $$
SELECT EXTRACT(epoch FROM $1)
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION year(date)
RETURNS int AS $$
SELECT EXTRACT(year FROM $1)
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION week(date)
RETURNS int AS $$
SELECT EXTRACT(week FROM $1)::int;
$$ LANGUAGE sql;

GROUP_CONCAT

PostgreSQL doesn't support aggregate function group_concat. But you can use aggregate array_agg:
postgres=# select * from omega;
+---+
| x |
+---+
| 1 |
| 3 |
| 6 |
+---+
(3 rows)

postgres=# select array_to_string(array_agg(x),',') from omega;
+-----------------+
| array_to_string |
+-----------------+
| 1,3,6           |
+-----------------+
(1 row)
If you search more complete MySQL API, look on mysqlcompat project. Regards Pavel

Thursday, August 13, 2009

epsql available

Hello

Today I uploaded patch enhancing psql on pgfoundry. You can download it from url http://pgfoundry.org/frs/download.php/2335/epsql.diff. This patch should be applied on postgresql 8.4 source:
cd postgresql-8.4.0/src/bin/psql
patch -p1 < epsql.diff
make clean
make all
su
make install
Enhanced psql has new metacommand \lf and new formating option linestyle (+2 new border styles).

\lf

\lf print function's source code (with and without row numbers):
postgres=# \lf foo(int)
**** CREATE OR REPLACE FUNCTION public.foo(_a integer)
**** RETURNS integer
**** LANGUAGE plpgsql
**** AS $function$
1 BEGIN
2 RETURN _a + 1;
3 END;
**** $function$

postgres=# \lf- foo(int)
CREATE OR REPLACE FUNCTION public.foo(_a integer)
RETURNS integer
LANGUAGE plpgsql
AS $function$
BEGIN
RETURN _a + 1;
END;
$function$

smart wrap mode

original wrap mode:
postgres=# \pset format wrapped
Output format is wrapped.
postgres=# select * from test;
+--------------------------------------------------------------------------------------+
| a |
+--------------------------------------------------------------------------------------+
| Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor inci |
| didunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exerc |
| itation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dol |
| or in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. |
| Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt m |
| ollit anim id est laborum. |
+--------------------------------------------------------------------------------------+
(1 row)
modified wrap mode:
postgres=# select * from test;
+--------------------------------------------------------------------------------------+
| a |
+--------------------------------------------------------------------------------------+
| Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor |
| incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud |
| exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute |
| irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla |
| pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia |
| deserunt mollit anim id est laborum. |
+--------------------------------------------------------------------------------------+
(1 row)

Border styles

postgres=# \pset border 0
Border style is 0.
postgres=# \d pg_database
Table "pg_catalog.pg_database"
Column Type Modifiers
------------- --------- ---------
datname name not null
datdba oid not null
encoding integer not null
datcollate name not null
datctype name not null
datistemplate boolean not null
datallowconn boolean not null
datconnlimit integer not null
datlastsysoid oid not null
datfrozenxid xid not null
dattablespace oid not null
datconfig text[]
datacl aclitem[]

postgres=# \pset border 1
Border style is 1.
postgres=# \d pg_database
Table "pg_catalog.pg_database"
Column | Type | Modifiers
---------------+-----------+-----------
datname | name | not null
datdba | oid | not null
encoding | integer | not null
datcollate | name | not null
datctype | name | not null
datistemplate | boolean | not null
datallowconn | boolean | not null
datconnlimit | integer | not null
datlastsysoid | oid | not null
datfrozenxid | xid | not null
dattablespace | oid | not null
datconfig | text[] |
datacl | aclitem[] |

postgres=# \pset border 2
Border style is 2.
postgres=# \d pg_database
Table "pg_catalog.pg_database"
+---------------+-----------+-----------+
| Column | Type | Modifiers |
+---------------+-----------+-----------+
| datname | name | not null |
| datdba | oid | not null |
| encoding | integer | not null |
| datcollate | name | not null |
| datctype | name | not null |
| datistemplate | boolean | not null |
| datallowconn | boolean | not null |
| datconnlimit | integer | not null |
| datlastsysoid | oid | not null |
| datfrozenxid | xid | not null |
| dattablespace | oid | not null |
| datconfig | text[] | |
| datacl | aclitem[] | |
+---------------+-----------+-----------+

postgres=# \pset border 3
Border style is 3.
postgres=# \d pg_database
Table "pg_catalog.pg_database"
+---------------+-----------+-----------+
| Column | Type | Modifiers |
+---------------+-----------+-----------+
| datname | name | not null |
+---------------+-----------+-----------+
| datdba | oid | not null |
+---------------+-----------+-----------+
| encoding | integer | not null |
+---------------+-----------+-----------+
| datcollate | name | not null |
+---------------+-----------+-----------+
| datctype | name | not null |
+---------------+-----------+-----------+
| datistemplate | boolean | not null |
+---------------+-----------+-----------+
| datallowconn | boolean | not null |
+---------------+-----------+-----------+
| datconnlimit | integer | not null |
+---------------+-----------+-----------+
| datlastsysoid | oid | not null |
+---------------+-----------+-----------+
| datfrozenxid | xid | not null |
+---------------+-----------+-----------+
| dattablespace | oid | not null |
+---------------+-----------+-----------+
| datconfig | text[] | |
+---------------+-----------+-----------+
| datacl | aclitem[] | |
+---------------+-----------+-----------+

postgres=# \pset border 4
Border style is 4.
postgres=# \d pg_database
Table "pg_catalog.pg_database"
Column | Type | Modifiers
---------------+-----------+----------
datname | name | not null
---------------+-----------+----------
datdba | oid | not null
---------------+-----------+----------
encoding | integer | not null
---------------+-----------+----------
datcollate | name | not null
---------------+-----------+----------
datctype | name | not null
---------------+-----------+----------
datistemplate | boolean | not null
---------------+-----------+----------
datallowconn | boolean | not null
---------------+-----------+----------
datconnlimit | integer | not null
---------------+-----------+----------
datlastsysoid | oid | not null
---------------+-----------+----------
datfrozenxid | xid | not null
---------------+-----------+----------
dattablespace | oid | not null
---------------+-----------+----------
datconfig | text[] |
---------------+-----------+----------
datacl | aclitem[] |

Linestyles

postgres=# \pset linestyle 0
Border line style is 0.
postgres=# \d pg_user_mapping
Table "pg_catalog.pg_user_mapping"
+-----------+--------+-----------+
| Column | Type | Modifiers |
+-----------+--------+-----------+
| umuser | oid | not null |
+-----------+--------+-----------+
| umserver | oid | not null |
+-----------+--------+-----------+
| umoptions | text[] | |
+-----------+--------+-----------+

postgres=# \pset linestyle 1
Border line style is 1.
postgres=# \d pg_user_mapping
Table "pg_catalog.pg_user_mapping"
┌───────────┬────────┬───────────┐
│ Column │ Type │ Modifiers │
├───────────┼────────┼───────────┤
│ umuser │ oid │ not null │
├───────────┼────────┼───────────┤
│ umserver │ oid │ not null │
├───────────┼────────┼───────────┤
│ umoptions │ text[] │ │
└───────────┴────────┴───────────┘

postgres=# \pset linestyle 2
Border line style is 2.
postgres=# \d pg_user_mapping
Table "pg_catalog.pg_user_mapping"
┌───────────┬────────┬───────────┐
│ Column │ Type │ Modifiers │
├───────────┴────────┴───────────┤
│ umuser │ oid │ not null │
├───────────┼────────┼───────────┤
│ umserver │ oid │ not null │
├───────────┼────────┼───────────┤
│ umoptions │ text[] │ │
└────────────────────────────────┘

postgres=# \pset linestyle 3
Border line style is 3.
postgres=# \d pg_user_mapping
Table "pg_catalog.pg_user_mapping"
╔═══════════╤════════╤═══════════╗
║ Column │ Type │ Modifiers ║
╟───────────┼────────┼───────────╢
║ umuser │ oid │ not null ║
╟───────────┼────────┼───────────╢
║ umserver │ oid │ not null ║
╟───────────┼────────┼───────────╢
║ umoptions │ text[] │ ║
╚═══════════╧════════╧═══════════╝

postgres=# \pset linestyle 4
Border line style is 4.
postgres=# \d pg_user_mapping
Table "pg_catalog.pg_user_mapping"
╔═══════════╤════════╤═══════════╗
║ Column │ Type │ Modifiers ║
╠═══════════╪════════╪═══════════╣
║ umuser │ oid │ not null ║
╟───────────┼────────┼───────────╢
║ umserver │ oid │ not null ║
╟───────────┼────────┼───────────╢
║ umoptions │ text[] │ ║
╚═══════════╧════════╧═══════════╝

postgres=# \pset linestyle 5
Border line style is 5.
postgres=# \d pg_user_mapping
Table "pg_catalog.pg_user_mapping"
╔═══════════╦════════╦═══════════╗
║ Column ║ Type ║ Modifiers ║
╠═══════════╩════════╩═══════════╣
║ umuser │ oid │ not null ║
╟───────────┼────────┼───────────╢
║ umserver │ oid │ not null ║
╟───────────┼────────┼───────────╢
║ umoptions │ text[] │ ║
╚════════════════════════════════╝

postgres=# \pset linestyle 6
Border line style is 6.
postgres=# \d pg_user_mapping
Table "pg_catalog.pg_user_mapping"
╔═══════════╦════════╦═══════════╗
║ Column ║ Type ║ Modifiers ║
╠═══════════╬════════╬═══════════╣
║ umuser ║ oid ║ not null ║
╟───────────╫────────╫───────────╢
║ umserver ║ oid ║ not null ║
╟───────────╫────────╫───────────╢
║ umoptions ║ text[] ║ ║
╚═══════════╩════════╩═══════════╝
Please, test it, use it.
Regards
Pavel Stehule