Archive

Posts Tagged ‘plpgsql’

pgScript vs pl/Pgsql postgresql

postgresql

PgScript è un linguaggio di scripting usato per maneggiare i dati del database postgresql tuttavia non è tipico di Postgresql ma di PgAdmin 3 il tool di amministrazione ad interfaccia grafica di postgresql. Pertanto opera esclusivamente a livello client.

pl/Pgsql invece è di fatto una estensione del linguaggio SQL tipica di Postgresql,  ricorda analogo linguaggio di Oracle. Ovviamente pl/Pgsql opera a livello server.

Ne consegue che le finalità sono completamente differenti .

Esempio di pgScript

 SET @N = 0 ; 
 SET @PROGR = 'pgScript' ;
 -- PRINT @PROGR + 'features' + CAST (@N AS STRING ) ;
 
 SET @N = @N +1 ; 
 PRINT @N ;
 WHILE @N < 10 
 BEGIN 
       SET @N = @N +1 ; 
      PRINT @N ;
 END

pgScript

Risorse:

query di tipo plpgsql 

Pagina riepilogativa

Query SQL  – PostgreSQL

Postgresql 

Creare eliminare database e tabelle

plpgsql functions nexted cycles FOR LOOP multiple IF THEN

Come utilizzare plpgsql per ritornare set di records
Utilizzo di:  FOR var_record IN query LOOP …. END LOOP  –
IF FOUND AND condition  THEN    statements   END IF  ;

La prima query 1) richiama i records in una struttura ad albero facendo ricorso a strutture nidificate di
FOR var_record IN query LOOP …. END LOOP
La seconda query 2) interroga la prima passando due punti della struttura dell’albero e ritorna tutti records del percorso tra i due .
Utilizza oltre a:   FOR var_record IN query LOOP …. END LOOP ;
anche

1)Strutture nidificate di   FOR  var_record IN   query  LOOP   ….  END LOOP
che richiamano i records in una struttura ad albero.

CREATE OR REPLACE FUNCTION __getcatalog_ID_Q(integer)
  RETURNS SETOF catalog AS
$BODY$
 DECLARE
 _mytupla  record;   
 _mytupla1 record;  -- 1° livello record
 _mytupla2 record;  -- 2° livello record
 _mytupla3 record;  -- 3° livello record
 _mytupla4 record;  -- 4° livello record
 _searchsql text := '' ;
 _myIDC int;  -- memorizza ID temporaneo

BEGIN

-- esploriamo primo livello distinta
FOR _mytupla1 IN (select  * FROM catalog  WHERE "IDCatalog" = $1 )    
LOOP
   RETURN NEXT _mytupla1 ;
   _myIDC = _mytupla1."IDCatalog" ;  

   FOR _mytupla2 IN (select  * FROM catalog  WHERE "IDCatalogFather" = _myIDC )
   LOOP
         RETURN NEXT _mytupla2 ;
         _myIDC = _mytupla2."IDCatalog" ;   

         FOR _mytupla3 IN (select  * FROM catalog  WHERE "IDCatalogFather" = _myIDC )
         LOOP
               RETURN NEXT _mytupla3 ;
              _myIDC = _mytupla3."IDCatalog" ;   

              FOR _mytupla4 IN (select  * FROM catalog  WHERE "IDCatalogFather" = _myIDC )
              LOOP
                  RETURN NEXT _mytupla4 ;
                  _myIDC = _mytupla4."IDCatalog" ;   

              END LOOP;    
         END LOOP;                      
   END LOOP;   
END LOOP;

END ;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER
  COST 100
  ROWS 1000;
ALTER FUNCTION __getcatalog_ID_Q(integer) OWNER TO postgres;

———————————————————————————————————-

2)
Strutture nidificate di
FOR var_record IN query LOOP …. END LOOP
and IF FOUND  AND _mytupla1.”IDCatalog” <> $1 THEN   END IF ;

CREATE OR REPLACE FUNCTION _get_getcatalog_id_qt(integer, integer)
  RETURNS SETOF catalog AS
$BODY$
 DECLARE
 _mytupla record;
 _mytupla1 record;
 _mytupla2 record;
 _mytupla3 record;
 _myIDC int;  
 _myIDC1 int;
BEGIN
select  * INTO _mytupla1
FROM __getcatalog_id_qt($1,$2)  WHERE "IDCatalog" = $2 ;
-- return NEXT _mytupla1;    
IF FOUND AND _mytupla1."IDCatalog" <> $1  THEN  
   -- visualizziamo solo le row del percorso diretto         
   FOR _mytupla1 IN select *
    FROM __getcatalog_id_qt($1,$2)  WHERE "IDCatalog" = $2  
   LOOP
    return next _mytupla1;    
    _myIDC =  _mytupla1."IDCatalogFather" ;

    -- estraiamo secondo livello
    select * INTO _mytupla2
    FROM __getcatalog_id_qt($1,$2)  WHERE "IDCatalog" = _myIDC ;        
    IF FOUND  THEN      
           FOR _mytupla2 IN select *
              FROM __getcatalog_id_qt($1,$2)  WHERE "IDCatalog" = _myIDC
            LOOP  
                return next _mytupla2;    
            _myIDC =  _mytupla2."IDCatalogFather" ;      

            -- estraiamo terzo livello
            SELECT * INTO _mytupla3
            FROM __getcatalog_id_qt($1,$2)  WHERE "IDCatalog" = _myIDC;        
            IF FOUND THEN      
                   FOR _mytupla3 IN select *
                     FROM __getcatalog_id_qt($1,$2)  WHERE "IDCatalog" = _myIDC
                   LOOP  
                       return next _mytupla3;    
                   _myIDC =  _mytupla1."IDCatalogFather" ;                             

                END LOOP ;  
                     END IF ;

        END LOOP ;  
        END IF ;

    END LOOP;
  ELSE
        select  * INTO _mytupla1
        FROM __getcatalog_id_qt($1,$2)  WHERE "IDCatalog" = $2 ;  
    return next _mytupla1;

  END IF;
END ;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER
  COST 100
  ROWS 1000;
ALTER FUNCTION _get_getcatalog_id_qt(integer,integer) OWNER TO postgres;
---------------------------------------------------------------------------

Esempio di chiamata nel generatore di query
di una query che utilizza la query 2) che utilizza la query 1)

 Select * FROM _get_getcatalog_id_qt(300, 412) ;

Risorse:

Plpgsql 

Postgresql 

Creare eliminare database e tabelle

plpgsql Postgres FOR LOOP END LOOP

Esempio di funzione plpgsql che utilizza
cicli  FOR LOOP END LOOP 
e IF FOUND THEN END IF 

CREATE OR REPLACE FUNCTION __getcatalog_ID0(integer)
RETURNS SETOF catalog AS
$BODY$
DECLARE
_mytupla1 record;
_mytupla2 record;
_myIDC int;
BEGIN
SELECT * INTO _mytupla1 FROM catalog 
    WHERE "IDCatalogFather" = $1 ;
IF FOUND THEN
     -- visualizziamo primo livello distina
     -- ed estraiamo secondo livello distinta
     FOR _mytupla1 IN SELECT * FROM catalog
         WHERE ("IDCatalogFather") = $1
     LOOP
         return next _mytupla1;
         -- estraiamo secondo livello
        _myIDC =  _mytupla1."IDCatalog" ;
        SELECT  * INTO _mytupla2
        FROM catalog  WHERE "IDCatalogFather" = _myIDC ;
        IF FOUND THEN
             return next _mytupla2;
        END IF ;
     END LOOP;
 ELSE
    _myIDC = _mytupla."IDCatalog" ;
    FOR _mytupla in select * from catalog
        WHERE ("IDCatalog") = $1 ;
    LOOP
        return next _mytupla;
    END LOOP;
 END IF;
END ;
$BODY$
LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER

Risorse:

Plpgsql 

Postgresql 

Creare eliminare database e tabelle

Postgresql plpgsql function example IF … THEN … ELSE … END IF

Postgres Plpgsql function.

Use of :
– DECLARE
– more SELECT queries.
– FOUND to find value for use in other query.
– more IF ELSE END IF ;
– Loop return next “record” End Loop ;
– date_part(‘year’,”Data”)
– LIKE % % ;

-- Function: _getnews_y(character varying, integer)
-- DROP FUNCTION _getnews_y(character varying, integer);

CREATE OR REPLACE FUNCTION _getnews_y(character varying, integer)
  RETURNS SETOF "Newslist" AS
$BODY$

DECLARE
    _record     RECORD ;    
    _year       ALIAS FOR $2;
    _category   ALIAS FOR $1;
    _yeartemp   integer ; 
BEGIN

    IF _category = 'news' THEN     
        -- ritorna tutte le news     
	for _record in 
	SELECT * from "Newslist" 
	WHERE "AgeFlag" = 'New' AND date_part('year',"Data") = _yeartemp 

	ORDER BY "Data" DESC loop	
		return next _record;
	end loop;
	return;
    ELSE
        -- ritorna le news che hanno uno specifico tag = category   
	for _record in 
	SELECT * from "Newslist" 

	WHERE "AgeFlag" = 'New' AND date_part('year',"Data") = _yeartemp
        AND category LIKE '%' || _category || '%'

	ORDER BY "Data" DESC 
        LOOP	
		return next _record;
	END LOOP;
	return;
    END IF ;

END;$BODY$
  LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER
  COST 100
  ROWS 1000;
ALTER FUNCTION _getnews_y(character varying, integer) 
OWNER TO postgres;

Risorse:

Plpgsql 

Postgresql 

Postgresql cancella record duplicati

Creare eliminare database e tabelle

plpgsql how to loop record

To loop record inplpgsql postgres you need
to use FOR …. LOOP RETURN NEXT “record” END LOOP

CREATE OR REPLACE FUNCTION __foo(character varying)
  RETURNS SETOF catalog AS
$BODY$
 DECLARE   
   _category ALIAS for $1 ;
   _mytupla record ;
BEGIN

	FOR _mytupla IN (select  * FROM catalog  WHERE "Category" LIKE '%' || _category || '%')    
	LOOP
	   RETURN NEXT _mytupla ;  
	END LOOP ; 
	
END;
$BODY$
  LANGUAGE plpgsql VOLATILE SECURITY DEFINER
  COST 100
  ROWS 1000;
ALTER FUNCTION __foo(character varying) OWNER TO postgres;

..

Risorse:

plpgsql 

Postgresql

Categorie:Webmaster Tag:,