Re: Trying to move away from Firebird

Adrian Klaver <adrian.klaver@xxxxxxxxxxx> · Fri, 12 Feb 2016 14:48:06 -0800

On 02/12/2016 02:11 PM, ioan ghip wrote:
I was able to create all the domains, tables, views, etc, but I have
trouble creating stored procedures and triggers. Also, a question, does
Postgres support events, for example in Firebird I could do something
like this and then receive the event in the GUI:

   if ((NEW.MUSED_M_>=NEW.MLIMIT_M_) and (NEW.MLIMIT_M_>0) and
(NEW.ISACTIVE=1) and (NEW.FAXTOFAXFLAG=1)) then
   begin
     POST_EVENT 'deactivate_f2f';
   end

LISTEN/NOTIFY?

http://www.postgresql.org/docs/9.5/interactive/sql-listen.html

What exactly does POST_EVENT do?

Please help me translate the examples bellow so I can understand the
differences:

CREATE GENERATOR GENADMINID START WITH 0 INCREMENT BY 1;
SET GENERATOR GENADMINID TO 108;

http://www.postgresql.org/docs/9.5/interactive/sql-createsequence.html
http://www.postgresql.org/docs/9.5/interactive/functions-sequence.html

CREATE TRIGGER B_UPDATE_COMPANY FOR COMPANY
ACTIVE AFTER UPDATE POSITION 10
AS
begin
   if (old.AGENTID != new.AGENTID) then
   begin
     update USERS set USERS.AGENTID=new.AGENTID where USERS.COMPANYID =
new.COMPANYID;
   end
end

http://www.postgresql.org/docs/9.5/interactive/sql-createtrigger.html

Difference in Postgres, you specify a separate function you want the 
trigger to execute.

CREATE PROCEDURE GET_ATA_STATUS (
     MAC VARCHAR(128),
     NOW_D_ INTEGER)
RETURNS (
     ATA_STATUS INTEGER)
AS
begin
   SELECT IIF((a.TIMESTAMP_D_ + a.EXPIRE) > :NOW_D_, 1, 0) FROM ATA a
WHERE a.ATAMAC = :MAC into :ATA_STATUS;
   SUSPEND;
end

http://www.postgresql.org/docs/9.5/interactive/sql-createfunction.html

In Postgres you have a choice of languages. Built in as of recent 
versions are:
C
http://www.postgresql.org/docs/9.5/interactive/xfunc-c.html

SQL
http://www.postgresql.org/docs/9.5/interactive/sql-createfunction.html

and the one you probably want to start with

plpgsql
http://www.postgresql.org/docs/9.5/interactive/plpgsql.html

Thanks a lot.

--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx

--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general