Basic statements SQL/PSM samples

assignment

-- only assignment
CREATE OR REPLACE FUNCTION fr(OUT a int) 
AS
$$
  SET a = 10;
$$ LANGUAGE plpgpsm;

-- assignment result of select
CREATE OR REPLACE FUNCTION fx(b int) 
RETURNS int AS
$$
  BEGIN
    SET b = (SELECT 4);
    RETURN b;
  END;
$$ LANGUAGE plpgpsm;

-- multiple variable assignment (simultaneous assignment)
CREATE OR REPLACE FUNCTION fx(b int) 
RETURNS int AS 
$$ 
  BEGIN 
    DECLARE x, y integer; 
    DECLARE z integer DEFAULT 0;
    SET (x,y)=(10,20); 
    PRINT x,y,z; 
    SET (x,y,z) = (SELECT x+1,y+1, x+y); 
    PRINT x,y,z;
    -- DB2 form of simultaneous assignment
    SET x = 0, y = x + 1, z = y + 1;
    PRINT x,y,z;
    RETURN b; 
  END 
$$ LANGUAGE plpgpsm;

-- assign system variable
CREATE OR REPLACE FUNCTION fx(a bool)
RETURNS VOID AS 
$$
  BEGIN
    SET enable_seqscan = a; 
  END;
$$ LANGUAGE plpgpsm;

-- handling not found signal 
CREATE OR REPLACE FUNCTION fx(b int)
RETURNS int as
$$
  BEGIN
    DECLARE c int;
    DECLARE CONTINUE HANDLER FOR NOT FOUND PRINT 'not found';
    --DELETE FROM foo;                                                                                      
    SELECT INTO b a FROM foo;
    SET (b,c) = (SELECT a,a FROM foo);
    RETURN b+c;
  END;
$$ LANGUAGE plpgpsm;

one statement's functions

CREATE OR REPLACE FUNCTION fr(OUT a int) 
AS
$$
  SET a = 10;
$$ LANGUAGE plpgpsm;

-- only return
CREATE OR REPLACE FUNCTION f(a integer, b integer) 
RETURNS int AS
$$
  RETURN a + b;
$$ LANGUAGE plpgpsm;

Conditional control

-- if statement
CREATE OR REPLACE FUNCTION fx(b int) 
RETURNS int AS
$$
  BEGIN
    IF b < 2 THEN
      PRINT 'mensi';
    ELSE
      PRINT 'vetsi';
    END IF;
    RETURN b;
END;
$$ LANGUAGE plpgpsm

-- searched case statement
CREATE OR REPLACE FUNCTION fx(b int) 
RETURNS int as
$$
  BEGIN
    CASE WHEN b = 1 THEN PRINT 'jedna';
         WHEN b = 2 THEN PRINT 'dve';
         ELSE PRINT 'neco';
              PRINT 'je jinak';
    END CASE;
    RETURN b;
  END;
$$ LANGUAGE plpgpsm;

-- simple case statement
CREATE OR REPLACE FUNCTION fx(b int)
RETURNS int as
$$
  BEGIN
    CASE b
         WHEN 1 THEN PRINT 'jedna';
         WHEN 2 THEN PRINT 'dve';
         ELSE PRINT 'neco';
              PRINT 'je jinak';
    END CASE;
    RETURN b;
  END;
$$ LANGUAGE plpgpsm;

-- simple case statement, comma separated predicates
CREATE OR REPLACE FUNCTION fx(b int)
RETURNS int as
$$
  BEGIN
    CASE b
         WHEN 1.0,   3, 5.0 THEN PRINT 'liche'; PRINT 'cisla';
         WHEN   2, 4.0,   6 THEN PRINT 'sude';  PRINT 'cisla';
    END CASE;
    RETURN b;
  END;
$$ LANGUAGE plpgpsm;

-- block, variables with default value
CREATE OR REPLACE FUNCTION fx(a int) 
RETURNS int AS
$$
  xx:BEGIN
       DECLARE o,c integer DEFAULT 10;
       PRINT o,c;
       RETURN o;
     END;
$$ LANGUAGE plpgpsm;

Iterative processing with loops

-- loop cycle
CREATE OR REPLACE FUNCTION fx(b int)
RETURNS int as
$$
  BEGIN
    lc:LOOP
         IF b > 10 THEN
           LEAVE lc;
         END IF;
         SET b = b + 1;
       END LOOP;
    RETURN b;
  END;
$$ LANGUAGE plpgpsm;

-- while
CREATE OR REPLACE FUNCTION fx(b int) 
RETURNS int AS
$$
  BEGIN
    DECLARE a int DEFAULT 1;
    llx : WHILE a < b
          DO
            SET a = a + 1;
            PRINT a;
          END WHILE llx;
    RETURN a;
  END;
$$ LANGUAGE plpgpsm;

-- repeat
CREATE OR REPLACE FUNCTION f() 
RETURNS int AS
$$
  BEGIN
    DECLARE i integer DEFAULT 0;
    REPEAT
      SET i = i + 1;
      PRINT i;
    UNTIL i < 10
    END REPEAT;
  END;
$$ LANGUAGE plpgpsm;