From: | David Ford <firefighterblu3(at)gmail(dot)com> |
---|---|
To: | postgres <pgsql-sql(at)postgresql(dot)org> |
Subject: | filtering columns in function |
Date: | 2016-01-19 19:59:27 |
Message-ID: | CAFZ8Xq7z_sB3_6tegW24XnbqPj129LkUCp=d7D9Cb_RRwZP5kg@mail.gmail.com |
Views: | Whole Thread | Raw Message | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
i have several APIs which operate over several different tables. first
allow me to show you some plpgsql i use, this is semantically the same in
all of my APIs with different names for tables (this is python generating
the SQL statements):
_proc = '''
CREATE OR REPLACE FUNCTION notify_proc() RETURNS trigger AS
$$
DECLARE
_json json;
_record record;
BEGIN
IF TG_OP = 'INSERT' or TG_OP = 'UPDATE' THEN
SELECT TG_TABLE_NAME AS table, TG_OP AS action,
NEW.*
INTO _record;
ELSE
SELECT TG_TABLE_NAME AS table, TG_OP AS action,
OLD.*
INTO _record;
END IF;
_json = row_to_json(_record);
PERFORM pg_notify(CAST('dfw' AS text), CAST(_json AS
text));
IF TG_OP = 'INSERT' or TG_OP = 'UPDATE' THEN
RETURN NEW;
ELSE
RETURN OLD;
END IF;
END;
$$ LANGUAGE plpgsql;
'''
_trig = '''
DO
$$
BEGIN
IF NOT EXISTS (SELECT *
FROM information_schema.triggers
WHERE event_object_table = '{table}'
AND trigger_name = 'dfw_notify_{table}_{op}'
)
THEN
CREATE TRIGGER dfw_notify_{table}_{op} {when} {op}
ON {table}
FOR EACH ROW
EXECUTE
PROCEDURE notify_proc();
END IF;
END;
$$
'''
c.execute(_proc)
for table in
{'blocklist','recents','filter_meta','filter_whitelist'}:
for op,when in
{'insert':'BEFORE','update':'AFTER','delete':'BEFORE'}.items():
c.execute(_trig.format(op=op, when=when, table=table))
because i use this approach frequently for a number of software packages,
i'd like to improve on it. pg notifications have a fixed size limit and
recently i'm encountering more and more notifications are lost because this
size limit is reached -- PG will emit an error instead of performing the
notification. some of my databases have dozens of different tables with all
sorts of different columns that i use this trigger and function on.
with the background provided, here's the question; without making a complex
series of table name if/else stanzas, how can i more succinctly change my
select query to omit certain columns?
IF TG_OP = 'INSERT' or TG_OP = 'UPDATE' THEN
SELECT TG_TABLE_NAME AS table, TG_OP AS action,
NEW.*
INTO _record;
ELSE
SELECT TG_TABLE_NAME AS table, TG_OP AS action,
OLD.*
INTO _record;
END IF;
in other words, instead of NEW.* or OLD.*, i'd like to do NEW.* EXCLUDING
('somecol1','somecol2')
i've read a lot of dialog debating this over the last decade's worth of
pgsql lists but i've not come up with any result which really fits into my
usage with ease. i don't want to simply truncate the generated json because
i can't guarantee that the columns i really do want will appear in the
non-truncated segment. omitting certain columns will fix my overflow
problems.
-david
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2016-01-19 20:21:21 | Re: filtering columns in function |
Previous Message | Pavel Stehule | 2016-01-19 18:09:06 | Re: remove null values from json |