Dynamic cursors support samples

Z PostgreSQL
Přejít na: navigace, hledání
CREATE OR REPLACE FUNCTION fx(a int)                                                                                    
RETURNS int AS                                                                                                          
$$                                                                                                                                                                                                                 
  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;