Jack has demonstrated the way to go. However, I feel there is room for improvement.
I place everything in schema x
for convenient testing. Test setup:
DROP SCHEMA x CASCADE;
CREATE SCHEMA x;
-- meta tables for schema and table name
CREATE TABLE x.schma(schma_id int, schma text);
INSERT INTO x.schma VALUES (1, 'x');
CREATE TABLE x.tbl(tbl_id int, tbl text);
INSERT INTO x.tbl VALUES (1, 't1'), (2, 't2');
-- dummy tables to be used in example query:
CREATE TABLE x.t1(id int);
INSERT INTO x.t1 VALUES (1),(2);
CREATE TABLE x.t2(foo text);
INSERT INTO x.t2 VALUES ('some text'), ('some more text');
Old function (original answer):
CREATE OR REPLACE FUNCTION x.f_dynaquery_old(int, int, _col text, _type anyelement, OUT col anyelement)
RETURNS SETOF anyelement AS
$func$
BEGIN
RETURN QUERY EXECUTE '
SELECT ' || quote_ident(_col) || '
FROM ' || (
(SELECT schma FROM schma WHERE schma_id = $1) || '.' ||
(SELECT tbl FROM tbl WHERE tbl_id = $2))::regclass;
END
$func$ LANGUAGE plpgsql;
Cleaner version with format()
(update 2017):
CREATE OR REPLACE FUNCTION x.f_dynaquery(_schma_id int, _tbl_id int
, _col text, _type anyelement)
RETURNS TABLE(col anyelement) AS
$func$
BEGIN
RETURN QUERY EXECUTE format(
'SELECT %I FROM %I.%I'
, _col
, (SELECT schma FROM schma WHERE schma_id = _schma_id)
, (SELECT tbl FROM tbl WHERE tbl_id = _tbl_id)
);
END
$func$ LANGUAGE plpgsql;
COMMENT ON FUNCTION x.f_dynaquery(int, int, text, anyelement)
IS 'Query any column from a dynamically assembled tablename.
$1 .. id of schema
$2 .. id of table
$3 .. name of column
$4 .. type of column (only data type matters, not the value)';
Call:
SELECT col FROM x.f_dynaquery(1, 1, 'id', NULL::int);
col
-----
1
2
SELECT col FROM x.f_dynaquery(1, 2, 'foo', NULL::text);
col
----------------
some text
some more text
Major points
Just remove the ':' characters and the update will work:
UPDATE mactable SET mac_address = CAST(replace(mac_address_temp,':','') as macaddr);
To get rid of everything that's not a hex digit:
postgres=# insert into mactable (mac_address_temp) values ('AAB5:-4f-27:e2-99');
INSERT 0 1
postgres=# select regexp_replace(mac_address_temp, '[^a-fA-F0-9]', '', 'g')
postgres-# from mactable;
regexp_replace
----------------
AAB54f27e299
AAB54f27e299
(2 rows)
postgres=#
So the update would be:
UPDATE mactest SET mac_address = CAST(regexp_replace(mac_address_temp, '[^a-fA-F0-9]', '', 'g') as macaddr);
Best Answer
Barman will create online file system backups, and such backups can only be done of the complete cluster.
To backup parts of a database, you must use a logical backup with
pg_dump
, but then you cannot perform point-in-time recovery.One way to work around that is to have the tables you don't want to back up be part of a different cluster and access them with
postgres_fdw
, or store them as “flat files” and usefile_fdw
to access them. Be warned, though, that there are performance penalties when querying such foreign tables.