Dynamics SQL

CREATE OR REPLACE FUNCTION fx() 
RETURNS int AS 
$$
 BEGIN 
 DECLARE a,b int; 
 EXECUTE IMMEDIATE 'SELECT 10,20' INTO a,b; 
 PRINT a,b; 
 RETURN a+b; 
 END; 
$$ LANGUAGE plpgpsm;

Syntax

EXECUTE IMMEDIATE vyraz [INTO (variables list| RECCORD or ROW variable)] [USING variables list]

CREATE OR REPLACE FUNCTION fxx(a int)
RETURNS float AS 
$$
 BEGIN
 DECLARE f float;
 PREPARE prep(float) from 'select 1ドル';
 EXECUTE prep INTO f USING a; 
 RETURN f ;
 END;
$$ LANGUAGE plpgpsm;
CREATE FUNCTION

Syntax

PREPARE name [ '(' types list ')' ] FROM expression
EXECUTE name [INTO (variables list | RECORD or ROW expression)]
 [USING variables list]

CREATE OR REPLACE FUNCTION fx(a int) 
RETURNS int AS 
$$ 
#option dump 
 BEGIN 
 DECLARE x, y integer; 
 DECLARE SQLSTATE char(5); 
 
 DECLARE cx CURSOR FOR prepstmt; 
 
 PREPARE prepstmt(int) FROM 'SELECT a*100, b*100 FROM Foo WHERE Foo.a = 1ドル'; 
 
 OPEN cx USING a; 
 
 FETCH cx INTO x, y; 
 WHILE SQLSTATE = '00000' DO 
 PRINT x, y; 
 FETCH cx INTO x, y; 
 END WHILE; 
 
 CLOSE cx; 
 RETURN a; 
 END; 
$$ LANGUAGE plpgpsm; 

 --dangerous, variables _tab and _var are security risk
 EXECUTE IMMEDIATE 'SELECT * FROM '|| _tab || ' WHERE a = ''' || _var ||''' INTO _a, _b;
 -- less security risk, _tab cannot contains special chars.
 -- only _tabs is security risk
 PREPARE psel(varchar) 'SELECT * FROM ' || _tab || 'WHERE a = ?';
 EXECUTE psel INTO _a, _b USING _var;