Postgresql

https://www.postgresql.org/docs/9.6/plpgsql-declarations.html


drop table account;
--create table
CREATE TABLE account(
   id SERIAL PRIMARY KEY,
   name VARCHAR NOT null,
   age numeric,
   amount numeric
);

-- insert data into table
INSERT INTO account (name,age,amount) values ('bonh',27,0);
INSERT INTO account (name,age,amount) values ('kynp',27,0);


--- select * from account
SELECT * FROM account;

-- delete record by id
delete from account where id >= 3;
SELECT * FROM account;

-- procedure for tranfering
drop procedure if exists transfer;
CREATE OR REPLACE PROCEDURE transfer(INT, INT, DEC)
LANGUAGE plpgsql 
AS $$
BEGIN
    -- subtracting the amount from the sender's account
    UPDATE account
    SET amount = amount - $3
    WHERE id = $1;

    -- adding the amount to the receiver's account
    UPDATE account
    SET amount = amount + $3
    WHERE id = $2;

    COMMIT;
END;
$$;

-- call demo procedure
CALL transfer(1,2,1000);

-- call demo procedure
drop function if exists helloWorld( text);
CREATE OR REPLACE FUNCTION helloWorld(name text) RETURNS void AS $helloWorld$
DECLARE
BEGIN
    RAISE LOG 'Hello, %', name;
END;
$helloWorld$ LANGUAGE plpgsql;

select helloWorld('bonh');



-------
drop function if exists inc(numeric);
CREATE OR replace FUNCTION inc(val numeric) RETURNS integer
AS $$
BEGIN
RETURN val + 1;
END; $$
LANGUAGE PLPGSQL;

select inc(1);

-----------------

drop function if exists getall();
CREATE OR replace FUNCTION getall()
RETURNS TABLE (
      user_name VARCHAR,
      user_age INT
)
AS $$
BEGIN
RETURN QUERY SELECT
      name,
      cast( age as integer)
   FROM
      account
   WHERE
      age = 27 ;
END; $$
LANGUAGE PLPGSQL;

select * from getall();