Stack Overflow
Stack Overflow

Questions tagged [postgresql-triggers]

44 questions
5
votes
1 answer

Run Python script from PostgreSQL function

I'm facing a "little" problem regarding executing a Python script every time is an update or insert action on the PostgreSQL table. This script will extract and write to a file updated or inserted data. Environment data: Ubuntu 18.04 (Bionic…
Georgian
  • 73
  • 2
  • 9
4
votes
1 answer

Modify OLD to be returned by DELETE in postgresql trigger function

I have a trigger function in postgresql which will insert rows in the audit table on INSERT, UPDATE and DELETE operations. In my tables, there is a column called audit_id and I need to write the ID of inserted audit row in this field. This is my…
Sophio
  • 43
  • 5
2
votes
2 answers

How can I concat two strings to update a column in a postgres table using trigger function

I have user table and profile table. When a record is created in user table, it automatically create a record in profile table with the returned id. The profile table is like: create table app_public.profiles ( user_id int not null primary key…
Wacademy
  • 101
  • 2
  • 13
2
votes
1 answer

How to write a generic update trigger for PostgreSQL view without known columns?

I'm using a PostgreSQL view to simulate a legacy table that has since been split into two separate tables, so we can maintain backwards compatibility with a range of services. The goal is essentially for this view to function transparently as if it…
SCB
  • 5,821
  • 1
  • 34
  • 43
2
votes
1 answer

Get Table Name of PostgreSQL Event Trigger

I have multiple tables in a PostgreSQL 9.6 database which changes I want to monitor and handle in an extern application. Handling only data changes wasn't hard, but now I want to monitor structure changes of my database to be stored. This is what I…
torblerone
  • 163
  • 1
  • 1
  • 10
2
votes
3 answers

PostgreSQL copy to a new table with one extra column. How?

I am trying to make a forum database and in this database I have a trigger. After the Insert it should copy the values of the other table into the new one, but should also make a new column showing the actual registerdate of the user. Here is the…
Raven sama
  • 23
  • 4
1
vote
2 answers

PostgreSQL trigger race condition updating a balance table from transactions

I have a financial system where users have tokens and can add transactions. The system has to calculate the balance and mean acquisition price of each token. Data integrity is of utmost importance in the system and it should be impossible to have…
drpexe
  • 466
  • 4
  • 13
1
vote
1 answer

Postgres statement-level trigger ERROR: transition tables cannot be specified for triggers with column lists

I have a table my_table with columns id (int), value (varchar), and modified (timestamp). I want to update the timestamp by the trigger each time I set a new value. I'm changing thousands of rows in one query so I do not want to use the row-level…
Pavlo Kuchereshko
  • 74
  • 8
1
vote
1 answer

Postgres: raise exception from trigger if column is in INSERT or UPDATE satement

I want to audit created_by, created_timestamp, modified_by, and modified_timestamp columns in my PostgreSQL table with triggers. Creating BEFORE INSERT and BEFORE UPDATE triggers to set these values to current_user and now() is reasonably…
Nevo
  • 752
  • 2
  • 9
  • 22
1
vote
2 answers

Using ST_DWithin to get id of closest object within an area

I have a trigger function in pgAdmin4 that grabs the closest object to the start point and end point of a line on placement in QGIS. However, I want to not only grab the id of the closest object, but only grab that id if the object is within a 20'…
AThomspon
  • 135
  • 12
1
vote
1 answer

Using Triggers in PGAdmin4 v5 to get the geom of a line being generated in QGIS

So I have a database of pipes that is visually coded in qgis. My goal is to add a trigger to PGAdmin4 v5 that uses ST_StartPoint() and ST_EndPoint() to get the end points of the polyline (the pipe) as it is being entered. Then, I was going use this…
AThomspon
  • 135
  • 12
1
vote
1 answer

How to insert stored functions and triggers in a Postgres container

I have a containerized Postgres database and I am trying to create a function and a trigger: a simple notification on a new row insertion. It may seem stupid but I am unable to find how to write the code, where should I push it to the Postgres…
NevD
  • 265
  • 2
  • 10
1
vote
1 answer

How to use the same trigger function for insert/update/delete triggers avoiding the problem with new and old objects

I am looking for an elegant solution to this situation: I have created a trigger function that updates the table supply with the sum of some detail rows, whenever a row is inserted or updated on warehouse_supplies. PostgreSQL insert or update…
coterobarros
  • 941
  • 1
  • 16
  • 25
1
vote
1 answer

Postgres - move rows to different table

Using postgres 11 I would like to automatically move rows from one table to another. I have setup a query, trigger function, and trigger but my test inserts fail with '0 0' when the trigger is enabled. source table to move rows from is…
refriedjello
  • 657
  • 8
  • 18
1
vote
1 answer

How to register trigger in PostgreSQL?

I have trigger and procedure. I want to be sure that string saved to database will have no '-' characters. After executing UPDATE interesant SET interesant_nip = '555-555-5555' I get error value is too long for varchar(10). This suggests that…
JJ_
  • 47
  • 1
  • 5
1
2 3