snippet

Writing A On Update Trigger in Postgresql

The previous article was about deletion trigger. This article is about update triggers. I had two tables what_did_you_eat and what_was_the_type. On insertion into what_did_you_eat, the what_type column was populated with the food type. what_did_you_eat column namedatatype what_did_you_eat_idserial whattext what_was_the_type column namedatatype what_was_the_type_idserial what_did_you_eat_idint what_typetext Now, if I update the value in what column of a row from what_did_you_eat, I would want the corresponding row in what_was_the_type table to be updated too.

Writing a On Delete Trigger in Postgresql

The previous article was about insertion trigger. I had two tables what_did_you_eat and what_was_the_type. On insertion into what_did_you_eat, the what_type column was populated with the food type. what_did_you_eat column namedatatype what_did_you_eat_idserial whattext what_was_the_type column namedatatype what_was_the_type_idserial what_did_you_eat_idint what_typetext Now, if I delete a row from what_did_you_eat, I would not want a row to remain in what_was_the_type to refer to a non existing row in the what_did_you_eat table.

Writing A On Insert Trigger In Postgresql

Here is a simple example of a on insert postgresql trigger. I have a table called what_did_you_eat. It has the following structure - column namedatatype what_did_you_eat_idserial whattext what will contain what was eaten. I have another table called what_was_the_type with the following structure - column namedatatype what_was_the_type_idserial what_did_you_eat_idint what_typetext I want to store the type of food that was eaten in what_type column. And I want to do this to happen on insert into the what_did_you_eat table.

How To Parse Json In A Stored Procedure In Postgresql

Say we have a table called person with the following structure --------------------------- column| datatype --------------------------- _id | autonumber name | text age | integer --------------------------- We want to insert multiple records into the table using a stored procedure. If we have a stored procedure with say two input variables name and age, then it will take multiple calls. We can do this using a single call if we use a json input and using the json_to_recordset function.

Writing a Postgresql Stored Procedure Returning a Single Value

Say we have a table called person with the following structure --------------------------- column| datatype --------------------------- _id | autonumber name | text age | integer --------------------------- We want to write a stored procedure to return the highest age that we have in the table. We will use OUT param here. CREATE OR REPLACE FUNCTION get_max_age(OUT max_age int) RETURNS int LANGUAGE 'plpgsql' AS $BODY$ BEGIN SELECT MAX(age) INTO max_age FROM person; END $BODY$; That’s it.

Writing a Postgresql Stored Procedure Returning Resultset

Coming from Microsoft SQL Server, I keep on forgetting how to return a resultset from a stored procedure in postgresql. Here is a small sample of how to do it. Needs a bit more code than SQL Server. Note that postgresql does not have stored procedure, they have function. They are equivalent. Say we have a table called person with the following structure --------------------------- column| datatype --------------------------- _id | autonumber name | text age | integer --------------------------- We want to write a stored procedure to return records with age greater than a specified age.