PostgreSQL Stored Procedures/functions

1. Useful Links

PostgreSQL Common small functions

some string manipulation functions of the PostgreSQL database

function called in PostgreSQL function

PostgreSQL Learning Manual (Functions and Operators < two >)

A simple way to get started with PostgreSQL stored procedures

2. Building a block environment (execution environment)

 Do language Plpgsql $$  declare  begin  ...  End $$;  

Such as

  Dolanguage Plpgsql $$ declare today date:=Now ();           Yesterday date; Beginday Date:='2012-12-12';    Val date; Iint; I_str varchar ( -); Begin--calls in the stored procedure need to use the Perform keyword perform initsfunction (); if(Today is NULL) then RAISE NOTICE'today is null' ; ElseRAISE NOTICE'today is not NULL' ; Endif; end;$$;

3. Create a stored procedure

Create or Replace function initstandardcheckindays () returnsvoid   as$body $ declare today date;        Beginday date;        Val date; Iint; Begin Today:=current_date; ifNot EXISTS (Select 1  fromstandardcheckindays) then Beginday:='2012-12-12'; ELSEIF (Not EXISTS (Select 1  fromStandardcheckindayswhereStcheckindate=today)) Then Beginday= (SelectMax (Stcheckindate) fromstandardcheckindays); Endif; if(Beginday is  NULL) then RAISE NOTICE'the standard sign-in table for the day's data already exists'     ; ElseRAISE NOTICE'the data for the day does not exist in the standard registration table'  ; RAISE NOTICE'initialized or 30 years later! Data for the day does not exist in the standard sign-in table'  ;  forIinch 1.. -* -Loop val:= Beginday +i;                Insert into Standardcheckindays (stcheckindate) values (val); RAISE NOTICE' %', Val;        End Loop; Endif; End; $body $ language plpgsql;

Select ' Current Date: ' | | To_char (now (),'yyyy-mm-dd HH24:MI:SS.MS');

PostgreSQL Stored Procedures/functions