Shulou(Shulou.com)05/31 Report--

This article mainly introduces what knowledge may be useful in postgresql development, which is very detailed and has a certain reference value. Friends who are interested must read it!

1. Time can be added and subtracted directly in PostgreSQL:

Query the current time of the system:

Select now ()

Or

Select current_timestamp

SELECT now ():: timestamp +'1 year';-current time plus 1 year

SELECT now ():: timestamp +'1 month';-current time plus one month

SELECT now ():: timestamp +'1 day';-current time plus one day

SELECT now ():: timestamp +'1 hour';-current time plus one hour

SELECT now ():: timestamp +'1 min';-current time plus one minute

SELECT now ():: timestamp +'1 sec';-add one second

Select now ():: timestamp +'1 year 1 month 1 day 1 hour 1 min 1 sec';-plus 01:01:01 on January 1

SELECT now ():: timestamp + (col | | 'day'):: interval FROM table-- converts the col field to a day and then adds it

Second, a small case of PostgreSQL stored procedure

1. Create a stored procedure format:

CREATE OR REPLACE FUNCTION function name (argument 1, [integer int4, integer array _ int4,...]) RETURNS returns the value type AS$BODY$DECLARE variable declares the BEGIN function body END;$BODY$LANGUAGE 'plpgsql' VOLATILE

Example:

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

2. Check the commands of your own stored procedures or functions

SELECT pg_proc.proname AS "function name", pg_type.typname AS "return value data type", pg_proc.pronargs AS "number of parameters" FROM pg_proc JOIN pg_type ON (pg_proc.prorettype = pg_type.oid) WHERE pg_type.typname! = 'void' AND pronamespace = (SELECT pg_namespace.oid FROM pg_namespace WHERE nspname =' public')

For example:

Function name | data type of returned value | number of parameters-- +-- somefunc | int4 | 0 getemp | emp | 1 delucsmmempreviousdata | trigger | 0 deldiskstatuspreviousdata | trigger | 0

3. Delete function

For example, delete the delucsmmempreviousdata function:

Delete from pg_proc where pg_proc.proname='delucsmmempreviousdata'

II. PostgreSQL trigger

Reference to: Postgresql trigger

The trigger of PostgreSQL is a callback function that is called when the database automatically executes the specified database event. Here are the main points about PostgreSQL triggers: www.yiibai.com

The trigger of the PostgreSQL can specify that a row is attempted before the trigger action (INSERT,UPDATE or DELETE before the check constraint) or after the operation is completed (after the check constraint and INSERT,UPDATE or DELETE (delete) has been completed), or an alternative operation (in the case of insert, update or delete on the view)..

Each line in which the FOR EACH ROW trigger is modified by the marked operation is called once. By contrast, a FOR EACH STATEMENT trigger performs only once for any given operation, no matter how many rows it has modified.

Row elements that may be accessed by WHEN clauses and trigger actions are inserted, deleted, or updated in the forms NEW.column-name and OLD.column-name, where column names are associated with the trigger from references to column names in the table. Www.yiibai.com

If the WHEN clause is provided, the PostgreSQL report executes only rows where the WHEN clause is true. If no WHEN clause is provided, all lines executed by the statement of PostgreSQL.

If there are multiple triggers of the same type that define the same event, they will be triggered in alphabetical order.

The BEFORE,AFTER or INSTEAD OF keyword determines when the triggered action will be executed, as opposed to inserting, modifying, or removing the relevant row. Www.yiibai.com

Trigger tables, which are automatically deleted when discarded. Yiibai.com

The table to be modified must exist, the trigger is appended to the table or view in the same database, and the table name must be used instead of database.tablename.

The option specified when the constraint creates a constraint trigger. Except for an ordinary trigger, you can adjust the timing of triggers that use SET (setting) constraints. An exception is expected to be thrown when constraint triggers violate the restrictions they impose.

Syntax:

The basic syntax for creating a trigger 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 on an UPDATE operation on one or more specified columns of a table as follows:

CREATE TRIGGER trigger_name [BEFORE | AFTER] UPDATE OF column_name

ON table_name [

-- Trigger logic goes here....]

Examples

Let's consider a case where we want to keep every record in the audit COMPANY table inserted, and we will create a new one as follows (delete the COMPANY table if it has already been created)

Testdb=# CREATE TABLE COMPANY (

ID INT PRIMARY KEY NOT NULL

NAME TEXT NOT NULL

AGE INT NOT NULL

ADDRESS CHAR (50)

SALARY REAL)

To maintain the audit test, we will create a new table called audit that will be inserted into the 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 's ID from the COMPANY table, and the date will keep the company table when the record will be created with a timestamp. So now let's create a trigger with the following COMPANY table:

Testdb=# CREATE TRIGGER example_trigger AFTER INSERT ON COMPANY

FOR EACH ROW EXECUTE PROCEDURE auditlogfunc ()

Auditlogfunc () is a PostgreSQL process with 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 inserting records in the COMPANY table, which will result in the creation of audit log records 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', 32,' California', 20000.00)

This will create a record COMPANY table as follows: yiibai.com

Id | name | age | address | salary

-- +-

1 | Paul | 32 | California | 20000

At the same time, create a record in the audit table. This record is a result of triggering the INSERT operation on 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-49-9-9-96-05-05 30 |

(1 row)

List triggers

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

Testdb=# SELECT * FROM pg_trigger

Or

Select trigger_name from information_schema.triggers

The above PostgreSQL table lists all triggers.

If you want to list the triggers on a particular table, then the terms of use and the table name are as follows:

Testdb=# SELECT tgname FROM pg_trigger, pg_class WHERE tgrelid=pg_class.oid AND relname='company'

The above PostgreSQL table also lists only one entry as follows:

Tgname

-

Example_trigger

(1 row)

Delete trigger

Here is the DROP command that can be used to delete an existing trigger: yiibai.com

Testdb=# DROP TRIGGER trigger_name on tablename

Execute postgresql-related commands in centos

1. Enter the database terminal

Psql database name-U user name

2. View all database names

\ l or\ list

The above is all the content of the article "what knowledge may be useful in postgresql development". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.