Tagged Questions

0
votes
3answers
39 views

postgres update rule wont insert

i have a simple table: CREATE TABLE aaa_has_bbb ( aaa_id integer not null, bbb_id integer not null, rank integer not null, primary key(aaa_id, bbb_id), uniq(aaa_id, rank) ) I ...
-1
votes
1answer
16 views

PgSQL trigger on a view with storage's rule

I apologize in advance for my English; I have a problem with the trigger on the view of the rule of storage trigger looks like this: CREATE TRIGGER AFTER INSERT ON add_count test.v_wszystkie FOR EACH ...
0
votes
1answer
31 views

PostgreSQL - set a default cell value according to another cell value

If i have a column say column a of any given values, and i want another column column b to have a default value according to the value of column a In another words: if column a = 'peter' then column ...
0
votes
1answer
20 views

Postgres avoid Trigger on delete cascade

Hello i have a problem with a Trigger i have 2 tables: t_mandant t_user_has_mandant when i delete a row in t_user_has_mandant i call a trigger beforeDeleteUserMandant() but i need a possibility ...
0
votes
1answer
72 views

Custom auto-increment field in postgresql (Invoice/Order No.)

The baseline requirement is to create an order number in the format: (M)M-SSS Where MM represents the current month and SSSS represents the order sequence for that month. For example 1-002 would ...
0
votes
1answer
47 views

Select a column default value into a variable in Pl/PgSQL

I'm trying to implement a generic trigger procedure to enable a sort a versioning scheme on tables. Tables all have version and current fields. On updates, in some situations based on a condition, i ...
0
votes
1answer
22 views

how to circumvent missing record type on insert

I'd like to make a copy of a row in one table addressed by a field in another table, like this: CREATE OR REPLACE FUNCTION f_ins_up_vorb() RETURNS TRIGGER AS $$ DECLARE dienst ...
1
vote
2answers
68 views

Inserted, Deleted tables in postgreSQL, like SQL Server?

I want to create a trigger after a inserted event, but I need the data that I inserted in order to register into a new table for my trigger in PostgreSQL In SQL Server I capture these values from the ...
2
votes
2answers
50 views

a postgres update trigger performs everything else except the actual update

Let's use a test table : CREATE TABLE labs.date_test ( pkey int NOT NULL, val integer, date timestamp without time zone, CONSTRAINT date_test_pkey PRIMARY KEY (pkey) ); I have a trigger ...
0
votes
3answers
30 views

Cycloning in trigger

I have table like this id | col1 | col2 I need write trigger so: after inserting or updating column col1 , I need insert (update) same value in column col2 This is my trigger function: CREATE ...
0
votes
1answer
26 views

PostgreSQL set a table age based on a date

I've run into a problem with my postgreSQL database. I have a table with a name, date of birth, and age. CREATE TABLE guy (name VARCHAR(10), dob DATE, ...
1
vote
1answer
34 views

PostgreSQL trigger raises error 55000

after migrating from PostgreSQL server version 9 to 8.4 I have encountered very strange error. Short description: If there is a trigger on a given table for each row before insert or update and one ...
0
votes
2answers
66 views

PostgreSQL Mathematical Function

I have a table aps_sections with many integer fields (such as bare_width and worn_width). I also have multiple look up tables (such as aps_bare_width and aps_worn_width) which contain an ID column and ...
0
votes
1answer
36 views

Postgresql: notification if a view is modified

I'm building a quite complex database and in order to simplify some queries I made a view that involves a lot of joined tables. I need to receive a notification whenever a row is added / modified / ...
0
votes
2answers
26 views

Compare postgresql enum in a function

I have a function defined that I would like to have update a record value it is modified. CREATE FUNCTION update_project_status_away_from_started() RETURNS TRIGGER AS $$ BEGIN IF OLD.status = ...
1
vote
1answer
48 views

How to pass NEW/OLD to procedures called from trigger functions in PostgreSQL?

Let's suppose I have a trigger function, but its code is so complex that it would be handy to split it to more procedures. I want to operate with the NEW (or OLD for UPDATE/DELETE triggers) variable ...
0
votes
1answer
42 views

How create postgres trigger in rails?

I have a table product(id,name) and table product_raiting(product_id,rate_type,rate). When I create new product, I need to create three raitings for this product. How can I add postgres trigger with ...
1
vote
1answer
57 views

Update field updated by trigger

I have a table with multiple fields and an additional outofsync field. Created a trigger and trigger function to set outofsync field value to true before any update/insert. Trigger: CREATE TRIGGER ...
2
votes
2answers
170 views

Postgresql insert trigger to set value

Assume in Postgresql, I have a table T and one of its column is C1. I want to trigger a function when a new record is adding to the table T. The function should check the value of column C1 in the ...
0
votes
1answer
85 views

Cascade UPDATE on a table on another table

I'm a newbie here and with PostgreSQL as well. I have two different boolean columns in two different tables. When I update my boolean column as true in tableone, I must also set a boolean column in ...
1
vote
1answer
111 views

Postgres partition management with triggers. Issue altering tables

SHORT STORY: I have a partitioned postgres database with a table to track the partitions and triggers. The triggers need to alter the constraints on the partition tables (their valid_date ...
1
vote
4answers
80 views

Ensure that a column can only be modified if it IS NULL and only by some users

I have a column A (type int) in a table for which data is not available at insert time of the other values. I do not want to split the table, as there is no other real reason to do so. I am ...
0
votes
1answer
116 views

Inserting in postgresql table from db2 trigger

My problem is the following one: I have a db2 database and a postgres database. They are in different servers. Every time i add a new record into a db2 table, it should be added immediately to the ...
1
vote
1answer
44 views

How can use custom_variable_class

i am create custom_variable_class= myapp in postgresql.conf. And set the value is inside of function like CREATE OR REPLACE FUNCTION fn_purchase(xmode text, xuserno integer) RETURNS text AS set ...
1
vote
1answer
88 views

Create global variable in Postgresql

I am creating trigger for log table. In that trigger i want to store xuserno to log table. xuserno is a argument of all function example: CREATE OR REPLACE FUNCTION fn_consume(xmode text, xuserno ...
0
votes
1answer
26 views

How to debug plperl script in postgres-8.4 trigger

I am writing the plperl script function for my trigger execution. When INSERT / UPDATE happens ,my plperl script will run , in that I am dynamically forming some query based on event I receive. I ...
1
vote
1answer
103 views

Catch postgres trigger in rails controller after insert or update operation

I have an app with different models. One of the models is a badge model with a many to many relation with users via achievements. In my controllers I have some logic to check if a user has gained an ...
-2
votes
1answer
40 views

Trigger that alters stock table when Item is sold [closed]

Is this the syntax correct for trigger to alter stock table when item is sold? create trigger tr_stockquant on tbl_ticket for insert, update, delete as $$ begin update s set stockQty = s.stockQty - ...
3
votes
2answers
68 views

Need Assistance with a Postgres Trigger and Function

I have a lookup table that holds a column of sources (from various hardcoded campaigns captured through a webservice API I created) and the respective brands that should be associated with them. This ...
0
votes
1answer
45 views

match partial implementation in postgresql

I have to bring a solution to the match partial problem in postgresql. this is what I do.. DROP TRIGGER IF EXISTS match_partial on futbolista; DROP FUNCTION IF EXISTS match_partial(); CREATE ...
0
votes
2answers
64 views

Apply a single trigger procedure to many different tables

In my PostgreSQL 9.1 database I have multiple tables and one trigger function. Right now I am creating the trigger for each table by using that trigger function. This methodology working fine. My ...
0
votes
1answer
62 views

PostgreSQL trigger updating secondary related table

I have two tables: table1 and table2, which triggers on inserts and on updates in the same function. As you insert a value in table1 or table2 a value is inserted in table3, with the value ...
0
votes
1answer
85 views

INSERT from trigger not happening?

I'm new to triggers in PostgreSQL. I have 3 tables in this example table1, table2 and table3. The trigger fires on new record on table1. The trigger function loops through all records of table2 that ...
0
votes
1answer
69 views

String matching in insert trigger - how to use in conditionals to return NULL?

In PostgreSQL 8.4.13 database with UTF8 encoding I keep a (non-English) dictionary in this table: create table good_words ( word varchar(64) primary key ); And a list of wrong (but often ...
0
votes
1answer
113 views

How to fire NOTIFICATION event in front end when table data gets changed

I am trying to make use of the Notification event given by Npgsql in vb.net. I partially got an idea about this mechanism, what i learned was, when ever a particular table's data has got changed, its ...
1
vote
2answers
74 views

SQL trigger not working

CREATE TABLE lab7.standings ( team_name VARCHAR(100) NOT NULL PRIMARY KEY, wins INTEGER, losses INTEGER, winPct NUMERIC, CHECK(wins > 0), CHECK(losses ...
0
votes
1answer
22 views

Trigger Not Executing Yet It's Created

I have a trigger function I'm trying to have execute in Postgres. It compiles and adds the trigger, however it does not insert the value into the table as I had hoped. The function it uses looks ...
0
votes
1answer
22 views

limit trigger scope using modifiable views

We have an architecture where modifications to data tables trigger the creation of jobs and subsequent processing by handlers (written in perl and notified via channels). During job handling the need ...
0
votes
1answer
49 views

postgresql-using trigger to fire

I want to create a trigger so that whenever I make a change (Update or Delete) it should copy the old data to a new table (with same template). I tried this code: create table restrictions(ID ...
1
vote
2answers
221 views

Update multiple columns in a trigger function in plpgsql

Given the following schema: create table account_type_a ( id SERIAL UNIQUE PRIMARY KEY, some_column VARCHAR ); create table account_type_b ( id SERIAL UNIQUE PRIMARY KEY, some_other_column ...
0
votes
1answer
45 views

Postgresql: Creating a Trigger

I have to create a trigger in postgresql (without any function calls). I have two tables LivesIn(pid, name, province) and Places(name, province, population, mayorid) I have the following code: ...
0
votes
0answers
51 views

Create trigger never ends - PostgreSQL [closed]

I'm trying to make a very simple trigger, which add a new row in a table, on update on another table. CREATE OR REPLACE FUNCTION add_history() RETURNS TRIGGER AS ' BEGIN INSERT INTO ...
1
vote
1answer
123 views

Capture columns in plpgsql during UPDATE

I am writing a trigger in plpgsql for Postgres 9.1. I need to be able to capture the column names that were issued in the SET clause of an UPDATE so I can record the specified action in an audit ...
1
vote
1answer
60 views

How can I optimize this: Many Postgres triggers calling the same function within a transaction

I have a cached field in one of my database tables that get's updated based on many different tables and the state of fields in those tables. Each of those tables calls the same function via a ...
0
votes
1answer
60 views

postgresql trigger not working

i have a table "demand_details" on update or delete i want to store values of each row in another table "demand_details_log" my functions is as follows CREATE OR REPLACE FUNCTION water_blogb() ...
1
vote
2answers
65 views

Fine-tuning when a constraint trigger should fire

In our application (with PostgreSQL under the hood (currently 9.1, moving for 9.2) we INSERT (or UPDATE) and UPDATE again a particular row while processing some request. So it looks like: BEGIN ...
1
vote
1answer
66 views

PostgreSQL inheritance : are parent-table triggers inherited?

If I have a before-update trigger on CANINES table that sets a timestamp column to now(), and DOGS inherits from CANINES, when a DOGS row is updated, is the CANINES update-trigger supposed to fire? ...
1
vote
1answer
122 views

PostgreSQL Trigger on Insert or Update

I have two tables. I want to create a trigger on the car table which will insert or delete on the fuel table depending on a certain value. Car id - SERIAL fuel - BOOLEAN Fuel car_id - INTEGER ...
0
votes
1answer
155 views

Autoincrement field with trigger and sequence - Check if ID is in the statement [Postgres]

I´ve got this sample from another thread and it fits perfectly on what I need to know. CREATE SEQUENCE AlimentosSequencia; CREATE TABLE alimento ( id integer NOT NULL PRIMARY KEY ,name ...
0
votes
1answer
129 views

postgreSQL for-each-row before-update triggers : the NEW and OLD pseudo-rows

Does the function that returns the trigger automagically have the ability to reference the NEW and OLD pseudo-rows in a before-update trigger designated for each row? CREATE TRIGGER foo_trigger ...

1 2 3 4 5 6
15 30 50 per page