Change your program from sqlserver to PostgreSQL -- 1

Article title: Change your program from sqlserver to PostgreSQL -- 1. Linux is a technology channel of the IT lab in China. Includes basic categories such as desktop applications, Linux system management, kernel research, embedded systems, and open source.
Author: Yunfei
From: yfy001.myetang.com
　　
　　
If you want to port your program from a windows platform to a linux platform, you must consider the database problem. The linux platform has two excellent databases: mysql and postgresql. Considering the product stability and database stability, in addition, the program wants to use stored procedures, so I decided to use the postgresql database as the background database.
I have previously written a flat article "importing SQL server data to postgresql", laying the foundation for this transplantation, now, we need to know how to use the postgresql stored procedure and connect to the database in the qt program.
　　
　　
　　
During the actual migration process, I found a problem. I want to use an SQL statement to create the structure of my database and table. However, I found a statement like this in sqlserver:
If exists (select * from sysobjects where id = object_id (n' [dbo]. [DiaryPrice]) and OBJECTPROPERTY (id, n'isusertable') = 1)
Drop table [dbo]. [DiaryPrice]
GO
This statement is used to determine whether a table exists in the database and delete it. This sentence cannot be implemented in postgresql. I have read the postgresql Documentation and have not found the relevant implementation statements. it is possible in sqlserver.
　　
I just learned from postgresql's PL/pgSQL language. I found such a small function on google that can be used to delete an existing table. I can write an article to keep a record.
　　
First, create a database (my fexdbmaster). If you want to use a certain process language, you must "install" it to the database to use. however, the procedural languages installed in the template1 database will be automatically installed in the subsequently created database. therefore, the database administrator can decide which language the database can use and which language the lack of time can use.
For languages that are released with the standard version, you can use the createlang shell script instead of doing it yourself. I want to use PL/pgSQL, so install PL/pgSQL to the template1 database, use
Createlang plpgsql template1
　　
The code for this program is as follows:
　　
Create or replace function drop_table_if_exists (text, bool) RETURNS bool'
DECLARE
Opt text;
Rec record;
BEGIN
IF $2 THEN
Opt: = ''cascade '';
ELSE
Opt: = '''';
End if;
Select into rec oid FROM pg_class WHERE relname = $1: name;
IF FOUND THEN
EXECUTE ''drop table'' | $1 | opt;
RETURN true;
End if;
RETURN false;
END;
'Language 'plpgsql ';
　　
You can save this code as a file, such as ifexites.
Use psql-d fexdbmaster-f ifexites in the console
Register this function in this database.
　　
Later, you can directly use this function when creating tables in this database. The two table creation scripts are as follows:
SELECT drop_table_if_exists ('fivemprice', false );
Create table FiveMPrice (
FiveMPriceId SERIAL,
ETypeId smallint not null,
UpDateTime timestamp not null,
OpenPrice varchar (50) NULL,
ClosePrice varchar (50) NULL,
MaxPrice varchar (50) NULL,
MinPrice varchar (50) NULL,
TrCurDateTime timestamp NULL
);
　　
SELECT drop_table_if_exists ('changetype ', false );
Create table ExchangeType (
ETypeId SERIAL,
ETypeCode varchar (50) not null,
EnglishName varchar (50) not null,
ChineseName varchar (50) NULL,
Desciption varchar (50) NULL,
CreateDateTime timestamp NULL
);
　　
This script can be stored as test. SQL
Run psql-d fexdbmaster-f test. SQL in the console. I have a limited level. hope you can give me more advice!
　　
　　
　　
　　
　　
　　
Related links
　　
　　
· More information about K-database-postgresql
· Yfy002 provided by news
　　
　　
--------------------------------------------------------------------------------
　　
The most popular articles are in K-database-postgresql:
Change your program from sqlserver to PostgreSQL -- 1