Dynamics SQL

Z PostgreSQL
Verze z 13. 2. 2010, 00:08, kterou vytvořil Pavel (diskuse | příspěvky)

(rozdíl) ← Starší verze | zobrazit aktuální verzi (rozdíl) | Novější verze → (rozdíl)
Přejít na: navigace, hledání
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;