TZSQLProcessor & Postgres stored proc
lazarus / 16-Databases

----------------------------------------------------------------------
nelson777 · 2007-05-24 21:44

I´m trying to create a stored procedure on Postgres using Zeos
Component´s TSQLProcessor but I allways get the "unterminated dollar-
quoted string at or near "$$ DECLARE ..."

Someone has any idea what´s the problem ? The dollar-quoted string
ends ok... here´s the code in Script property:

Code: [Select]
CREATE OR REPLACE FUNCTION rel_plantas() RETURNS void AS $$
DECLARE
  planta PLANTAS%ROWTYPE;
  C_HES CURSOR (key INTEGER) IS SELECT TO_CHAR(HES_DATA,
'DD/MM/YYYY'), HES_ESPESSURA FROM HISTORICO_ESPESSURA WHERE PLA_ISN =
key;
  C_HCL CURSOR (key INTEGER) IS SELECT TO_CHAR(HCL_DATA, 'DD/MM/YYYY')
FROM HISTORICO_COLHEITA WHERE PLA_ISN = key;
  C_HAD CURSOR (key INTEGER) IS SELECT TO_CHAR(HAD_DATA, 'DD/MM/YYYY')
FROM HISTORICO_ADUBACAO WHERE PLA_ISN = key;
  C_HPD CURSOR (key INTEGER) IS SELECT TO_CHAR(HPD_DATA, 'DD/MM/YYYY')
FROM HISTORICO_PODA WHERE PLA_ISN = key;
  C_HBG CURSOR (key INTEGER) IS SELECT TO_CHAR(HBG_DATA, 'DD/MM/YYYY')
FROM HISTORICO_BIOGEO WHERE PLA_ISN = key;
  dt_espessura VARCHAR(10);
  val_espessura numeric;
  dt_colheita VARCHAR(10);
  dt_adubacao VARCHAR(10);
  dt_poda VARCHAR(10);
  dt_biogeo VARCHAR(10);
  final BOOL;
BEGIN
    TRUNCATE TABLE REL_GENERICO;
    FOR planta IN SELECT * FROM PLANTAS ORDER BY PLA_ISN LOOP
        OPEN C_HES(planta.PLA_ISN);
        OPEN C_HCL(planta.PLA_ISN);
        OPEN C_HAD(planta.PLA_ISN);
        OPEN C_HPD(planta.PLA_ISN);
        OPEN C_HBG(planta.PLA_ISN);
        final := FALSE;
        WHILE not final LOOP
            final := TRUE;
            -- Espessura
            FETCH C_HES INTO dt_espessura, val_espessura;
            IF FOUND THEN 
                final := FALSE;
            ELSE
                dt_espessura := NULL;
                val_espessura := NULL;
            END IF;
            -- Colheita
            FETCH C_HCL INTO dt_colheita;
            IF FOUND THEN 
                final := FALSE;
            ELSE
                dt_colheita := NULL;
            END IF;
            -- Adubação
            FETCH C_HAD INTO dt_adubacao;
            IF FOUND THEN 
                final := FALSE;
            ELSE
                dt_adubacao := NULL;
            END IF;
            -- Poda
            FETCH C_HPD INTO dt_poda;
            IF FOUND THEN 
                final := FALSE;
            ELSE
                dt_poda := NULL;
            END IF;
            FETCH C_HBG INTO dt_biogeo;
            IF FOUND THEN 
                final := FALSE;
            ELSE
                dt_biogeo := NULL;
            END IF;
            IF NOT final  THEN
                INSERT INTO REL_GENERICO (ISN, COL1, COL2, COL3, COL4,
COL5, COL6) VALUES (planta.PLA_ISN, val_espessura, dt_espessura,
dt_adubacao, dt_poda, dt_colheita, dt_biogeo);
            END IF;
        END LOOP;
        CLOSE C_HES;
        CLOSE C_HCL;
        CLOSE C_HAD;
        CLOSE C_HPD;
        CLOSE C_HBG;
  END LOOP;
END;
$$ LANGUAGE plpgsql;


still about this, can anyone tell me, how "delimeter" and "delimiter
type" properties work ?

thnx

----------------------------------------------------------------------
antonio · 2007-05-25 01:10

There's a Zeos forum at http://zeos.firmos.at/index.php