Ha guys,
I am new to postgress and I am trying to write my first function to insert, update or delete and trap errors as a result of the table not existing , the columns not exist or if any other error simply pass back the sqlstate here's my code can you helpCREATE OR REPLACE FUNCTION listings_audit() RETURNS TRIGGER AS
$listings_audit$
BEGIN
IF (TG_OP = 'DELETE') THEN
IF (EXISTS (
SELECT 1
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'schema_name'
AND c.relname = 'table_name'
AND c.relkind = 'r' -- only tables
)) THEN
INSERT INTO listings_changes
SELECT now(), 'DELETE', OLD.*;
RETURN OLD;
ELSE RAISE EXCEPTION 'Table does not exists';
END IF;
ELSIF (TG_OP = 'UPDATE') THEN
IF (EXISTS (
SELECT 1
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'schema_name'
AND c.relname = 'table_name'
AND c.relkind = 'r' -- only tables
)) THEN
INSERT INTO listings_changes
SELECT now(), 'UPDATE', NEW.*;
RETURN NEW;
ELSE RAISE EXCEPTION 'Table does not exists';
END IF;
ELSEIF (TG_OP = 'INSERT') THEN
INSERT INTO listings_changes
SELECT now(), 'INSERT', NEW.*;
RETURN NEW;
END IF;
EXCEPTION
WHEN SQLSTATE '42611' THEN
RAISE EXCEPTION 'Columns do not match audit file does not match user file';
WHEN SQLSTATE '42P16' THEN
RAISE EXCEPTION 'Table does not exists';
WHEN OTHERS THEN
RAISE EXCEPTION 'PostgresSQL error code that has occurred';
RETURN SQLSTATE;
END;
$listings_audit$ LANGUAGE plpgsql;
On Thu, Jun 8, 2017 at 12:49 PM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
Harry Ambrose <harry.ambrose@xxxxxxxxx> writes:
> Please find the jar attached (renamed with a .txt extension as I know some
> email services deem jars a security issue).
Hmm, the output from this script reminds me quite a lot of one I was
sent in connection with bug #14444 awhile back:
https://www.postgresql.org/message-id/20161201165505. 4360.28203%40wrigleys. postgresql.org
Was that a colleague of yours?
Anyway, the bad news is I couldn't reproduce the problem then and I can't
now. I don't know if it's a timing issue or if there's something critical
about configuration that I'm not duplicating. Can you explain what sort
of platform you're testing on, and what nondefault configuration settings
you're using?
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
