Using pl/pgsql I've hacked together an update function to run on a bunch of tables that I need to change but I don't know all the names of. What I do know is THEY ALL have a primary key (which could be named anything) and a column specifically called 'metadata'. For example of one of these 'unknown' named tables might look like this:
CREATE TABLE test
(
gid SERIAL NOT NULL PRIMARY KEY,
item varchar,
metadata varchar,
z_order int,
foobar int
);
and I'm trying to return for each row I update (created by an BEFORE UPDATE trigger):
history | action | table_name | column_name| pkey_id | metadata |
1 | UPDATE | test | item | 1 | y |
_____________________________________________________________________ |
2 | UPDATE | test | z_order | 1 | y |
_____________________________________________________________________
3 | UPDATE | test | foobar | 1 | y |
However, I am having problems calling the specific column_name "metadata" in my query...for id'ing the pkey_id (which might have any column name) I used information_schema:
SELECT
INTO pkey_column_name
kcu.column_name
FROM
information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu
ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage AS ccu
ON ccu.constraint_name = tc.constraint_name
WHERE
constraint_type = 'PRIMARY KEY'
AND
ccu.table_name=tg_relname;
/// more code ///
FOR each_entity IN
EXECUTE 'SELECT text((' || QUOTE_NULLABLE(OLD) || '::"' || QUOTE_IDENT(tg_table_schema) || '"."' || QUOTE_IDENT(tg_relname) || '")."' || QUOTE_IDENT(pkey_column_name) || '")
AS pk_val,
/// more code ///
But...my attempts to get the specific column_name "metadata" into my execute query have failed. Ideally I would like to select it by name using attname...but I'm not having much luck...I have a list of each column ('each_column') names (including 'metadata') but no way to call it in the EXECUTE statement...
FOR each_column IN
SELECT
attname as column_name
FROM
pg_attribute
WHERE
attrelid =
(
SELECT
oid -- 'oid' is the internal Postgresql primary key used for various system tables
FROM
pg_class
WHERE
relname = tg_relname -- 'tg_relname' is a special Trigger variable that gives the name of the table invoking the trigger
)
AND attnum > 0 -- returns no system columns
LOOP
This is what I came up with (thanks Denis for the advice)~