Possible useful knowledge in PostgreSQL development

The time can be added and reduced directly in PostgreSQL:

Query System Current Time:

Select Now ();
Or
Select Current_timestamp;

SELECT now ():: Timestamp + ' 1 year '; --Current time plus 1 years
SELECT now ():: Timestamp + ' 1 month '; --Current time plus one months
SELECT now ():: Timestamp + ' 1 day '; --Current time plus one day
SELECT now ():: Timestamp + ' 1 hour '; --Current time plus one hours
SELECT now ():: Timestamp + ' 1 min '; --Add a minute to the current time
SELECT now ():: Timestamp + ' 1 sec '; --Add a second
Select Now ():: Timestamp + ' 1 year 1 month 1 day 1 hour 1 min 1 sec '; --Add 1 years January 1 days 1:1 1 seconds
SELECT now ():: Timestamp + (col | | "Day"):: Interval from table--Converts the Col field to all day and then adds

second, PostgreSQL storage process small case

1. Create a stored procedure format:

CREATE OR REPLACE function name (parameter 1,[integral type int4, integer array _int4, ...]) RETURNS return value type as$body$declare variable declaration begin function Body end; $BODY $language ' Plpgsql ' VOLATILE;

Instance:

Create or replace function message_deletes (ids  "varchar",  userid int8)   RETURNS int4 AS$BODY$DECLARE  r RECORD;  del bool;   num int4 := 0;  sql  "varchar"; begin  sql :=  ' Select id,receiveuserid,senduserid,senddelete,receivedelete from  message where id in  ('  | |  ids | |   ') ';   for r in execute sql loop    del :=  false;    if r.receiveuserid=userid and r.senduserid=userid then       del := true;    elseif r.receiveuserid= userid then      if r.senddelete=false then         update message set receivedelete=true where id = r.id;      else        del  : = true;      end if;    elseif r.senduserid= userid then      if r.receivedelete=false then         update message set senddelete=true where id =  r.id;      else        del :=  true;      END IF;    END IF;     if del then      delete from message where  id = r.id;      num := num + 1;     END IF;  END LOOP;  return num; END; $BODY $  language  ' Plpgsql '  voLatile; 

second, PostgreSQL trigger

The trigger for PostgreSQL is the callback function that is called when database automatic execution \ Specifies a database event occurs. The following are the main points of the triggers for PostgreSQL: www.yiibai.com

• The trigger for PostgreSQL can specify that a row is attempted before the triggering action (insert,update or delete before checking the constraint) or after the operation completes (after checking the constraint and insert,update or delete (delete) is complete). Or an alternative operation (in the case of inserts, updates, or deletions on the view):

• Each row that is marked by the for every row trigger is called once. In contrast, the for each statement trigger is executed only once for any given operation, regardless of how many rows it has modified.

• The When clause and the trigger action may access the row elements that are inserted, deleted, or updated using the form new.column-name and Old.column-name, where the column name is associated with a reference to the column name from the table. www.yiibai.com

• If a when clause is provided, the report of PostgreSQL only executes rows where the When clause is true. If the When clause is not provided, the PostgreSQL statement executes all the rows.

• If more than one trigger of the same type defines the same event, they will be triggered by the name in alphabetical order.

• The before,after or instead of keyword determines when the trigger action will be executed, relative to the insertion, modification or removal of the related row. www.yiibai.com

• Trigger tables, which are automatically deleted when discarded. yiibai.com

• To modify a table or view that must exist in the same database, the trigger is attached and the table name must be used instead of using Database.tablename.

• Creates a constraint trigger with the options specified when the constraint is constrained. This is an ordinary trigger except that you can adjust the timing of the trigger triggered by using the set constraint. An exception is thrown when the constraint triggers are expected to violate the restrictions they enforce:

Grammar:

The basic syntax for creating triggers is as follows:

CREATE TRIGGER trigger_name [before| after| INSTEAD of] Event_Name
On table_name[
-Trigger logic goes here ...];

HERE  event_name  could be  INSERT, DELETE,   UPDATE,  and  TRUNCATE  database operation on the mentioned table  table_name . You can optionally specify for each ROW after table name.

Following is the syntax of creating a trigger in an UPDATE operation on one or more specified columns of a table as follow S

CREATE TRIGGER trigger_name [before| After] UPDATE of column_name
On table_name[
-Trigger logic goes here ...];

Example

Let's consider a case where we want to keep auditing every record in the company table is inserted, we will create a new following (if already created, then delete company table)

testdb=# CREATE TABLE Company (
ID INT PRIMARY KEY not NULL,
NAME TEXT not NULL,
The age INT is not NULL,
ADDRESS CHAR (50),
SALARY REAL);

In order to maintain the audit test, we will create a new table called Audit will be inserted log message whenever there is a new record Entry table company:www.yiibai.com

testdb=# CREATE TABLE AUDIT (
emp_id INT not NULL,
Entry_date TEXT not NULL);

Here the ID is the audit record id,emp_id the ID from the company table, and the date will be kept when the table of records will be created timestamp. So now let's create a trigger, the company table looks like this:

testdb=# CREATE TRIGGER Example_trigger after INSERT on company
For each ROW EXECUTE PROCEDURE auditlogfunc ();

Auditlogfunc () is a PostgreSQL process that has the following definitions:

CREATE OR REPLACE FUNCTION auditlogfunc () RETURNS TRIGGER as $example _table$ BEGIN
INSERT into AUDIT (emp_id, Entry_date) VALUES (new.id, Current_timestamp);
RETURN NEW;
END; $example _table$ LANGUAGE plpgsql;

Now, let's start the company table Insert record, which will cause audit logging to be created in the audit table. So let's create a company table record as follows:

testdb=# INSERT into Company (id,name,age,address,salary) VALUES (1, ' Paul ', +, ' California ', 20000.00);

This will create a record of the company table as follows: yiibai.com

ID | name | Age | Address | Salary
----+------+-----+--------------+--------
1 |  Paul | 32 |  California | 20000

Also create a record in the audit table. This record is a result of the insert operation that triggered the company table that we have created. In a similar way, you can also create triggers, update and delete operations as required.

emp_id | Entry_date
--------+-------------------------------
1 | 2013-05-05 15:49:59.968+05:30
(1 row)

List triggers

You can list all the triggers in the current database from the Pg_trigger table as follows:

Select Trigger_name from Information_schema.triggers;

The table above PostgreSQL lists all the triggers.

If you want to list the triggers on a specific table, then use the terms and table names as follows:

testdb=# SELECT tgname from Pg_trigger, Pg_class WHERE tgrelid=pg_class.oid and relname= ' company ';

The table above PostgreSQL will also list only one entry as follows:

Tgname
-----------------
Example_trigger
(1 row)

Delete Trigger

The following is a drop command that can be used to delete an existing trigger: yiibai.com

testdb=# DROP TRIGGER trigger_name;