essay | tech | year-summary | about

返回上级菜单

postgre sql note

日期：2022-12-23T00:00:00Z

set up

1. set up pg_hba.conf file
   this file should set in db server, it will define where/what can access db.

2. use psql to access direct
   if in linux, should use below command.

sudo -u <user_name> psql

3. be care, table name is case-sensitive in linux

basic concept

1. varchar = character varying = text, however, text has different type with varchar and character varying.
   (varchar and character varying have the same type)

2. varchar = varchar(max) in sqlserver, however, varchar is recommanded as performance reason. (and easy to use)

3. function must return some value/table, procedure should not return anything. in postgre sql, procedure a kind of function (which returns void)

4. can write function in many language, can use basic sql, or plpgsql. plpgsql is recommended.

details

example

CREATE OR REPLACE FUNCTION blog.fnsaveblog(blogtitle character varying, blogurl character varying, blogbody character varying, category character varying, createdate date, modifydate timestamp with time zone, tags character varying, comment character varying, isdeleted boolean, summary character varying)
 RETURNS bigint
 LANGUAGE plpgsql
AS $function$
	<<fn>>
	declare 
		"blogid" int8 := 0;
		 "docid" int8 := 0;
	begin
		
		INSERT INTO blog."Blog"
		(blogtitle, docid, blogurl, category, createdate, modifydate, summary, tags, "comment", isdeleted)
		VALUES(fnsaveblog."blogtitle", fnsaveblog."docid", fnsaveblog."blogurl", fnsaveblog."category", fnsaveblog."createdate", 
			fnsaveblog."modifydate", fnsaveblog."summary", fnsaveblog."tags", fnsaveblog."comment", false)
		returning "Blog".blogid into fn."blogid";

		return "blogid";
		
	END;
$function$
;

this should be same as below sql server (t-sql) (have not tested)

CREATE procedure blog.fnsaveblog(@blogtitle varchar(100), @blogurl varchar(100), @blogbody varchar(1000), @category varchar(100), @createdate date, modifydate datetime2(3), @tags varchar(100), @comment varchar(100), @isdeleted boolean, @summary varchar(100))
BEGIN
    declare @blogid int = 0;
    declare @docid int = 0;

    INSERT INTO blog.[Blog]
    (blogtitle, docid, blogurl, category, createdate, modifydate, summary, tags, [comment], isdeleted)
    VALUES(@blogtitle, @docid, @blogurl, @category, @createdate, 
        @modifydate, @summary, @tags, @comment, false)
    OUTPUT INSERTED.blogid into @blogid;

    select @blogid;
END