85.160.82.76: /* assignment */

2006-12-28T10:20:37Z

assignment

Nová stránka

=== assignment ===
<pre>
-- 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;
</pre>

=== one statement's functions ===
<pre>
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;
</pre>

=== Conditional control ===
<pre>
-- 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;
</pre>

=== Iterative processing with loops ===
<pre>
-- 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;
</pre>

Basic statements SQL/PSM samples - Historie editací

85.160.82.76: /* assignment */