Comments (3)
Although the PG docs have a couple of examples of CREATE TRIGGER ... REFERENCING
, it would be helpful if you could also provide a couple of examples of the setup needed to invoke such a statement, so that they can be used in tests for covering the feature. They don't need to be too complex or use realistic names, just the basic CREATE TABLE/FUNCTIONs needed. Thanks.
from pyrseas.
I forgot about the example.....
CREATE FUNCTION public.test_tr_insert()
RETURNS trigger
LANGUAGE 'plpgsql'
AS $BODY$
DECLARE
qnt int;
BEGIN
SELECT count(*) INTO qnt FROM inserted;
RAISE NOTICE 'qnt = %', qnt;
RETURN NULL;
END;
$BODY$;
CREATE FUNCTION public.test_tr_delete()
RETURNS trigger
LANGUAGE 'plpgsql'
AS $BODY$
DECLARE
qnt int;
BEGIN
SELECT count(*) INTO qnt FROM deleted;
RAISE NOTICE 'qnt = %', qnt;
RETURN NULL;
END;
$BODY$;
CREATE TABLE test(c1 character varying(20))
CREATE TRIGGER test_tr_insert
AFTER INSERT
ON public.test
REFERENCING NEW TABLE AS inserted
FOR EACH STATEMENT
EXECUTE FUNCTION public.test_tr_insert();
CREATE TRIGGER test_tr_delete
AFTER DELETE
ON public.test
REFERENCING OLD TABLE AS deleted
FOR EACH STATEMENT
EXECUTE FUNCTION public.test_tr_delete();
REFERENCING can contain both tables
......
REFERENCING NEW TABLE AS inserted OLD TABLE AS deleted
......
launch
insert into test(c1) VALUES('q'), ('w'), ('e');
result:
NOTICE: qnt = 3
INSERT 0 3
delete from test where c1 = 'q' OR c1 = 'w'
result:
NOTICE: qnt = 2
DELETE 2
yaml for table my implementation
table test:
columns:
- c1:
statistics: 0
type: character varying(20)
owner: dmfms
triggers:
test_tr_delete:
events:
- delete
level: statement
procedure: public.test_tr_delete
ref_old_table: deleted
timing: after
test_tr_insert:
events:
- insert
level: statement
procedure: public.test_tr_insert
ref_new_table: inserted
timing: after
from pyrseas.
Fixed by 3a385ba. Adapted mostly from your code above, but using more synthetic tests.
from pyrseas.
Related Issues (20)
- yamltodb fails on Postgres 13 due to removal of pg_pltemplate catalog HOT 25
- ALTER SEQUENCE OWNER comes before CREATE TABLE HOT 3
- SSL Connection to remote database HOT 3
- datacopy CSV with null trailing columns not importing with -u HOT 7
- CREATE EXTENSION statements in the change script HOT 5
- [Contribution] Docker support HOT 2
- Sequences processing doesn't skip schemas defined with '-N' option. HOT 3
- dbtoyaml fails when dealing with extension-owned triggers/functions HOT 7
- Multiline Strings (source: |2) Output Unstable HOT 5
- Enable Github Discussions HOT 2
- Fails to Install: Error: pg_config executable not found HOT 5
- Error Using dbtoyaml With Supabase Schema HOT 67
- dbtoyaml Fails When PG Password Has Spaces (Improper Escaping) HOT 1
- Convert .travis.yml to GH Actions
- Fold pgdbconn back into Pyrseas HOT 1
- Key error when converting aggregate to YAML HOT 4
- plv8 to extension and fix reserved words in the primary key HOT 5
- Support columns GENERATED AS (x) STORED HOT 2
- Support --revert for tables HOT 4
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from pyrseas.