Procedimiento almacenado con ciclos anidados

[webness]

TENGO un procedimiento almacenado con ciclos o loops anidados, y este no retorna ningun valor, el procediento me lo deja crear sin problema pero cuando lo ejecuto no me genera un error.

El objetivo de este es actualizar los datos de 2 columnas de una tabla si y solo si hay datos para esa columna.

estoy viendo en: http://www.postgresql.org/docs/8.0/s...tructures.html, en la seccion 35.7.4. Looping Through Query Results un ejemplo para guiarme.

Mi codigo:

Código PL/PGSQL:

CREATE OR REPLACE FUNCTION p_update_locales_in_count() RETURNS boolean AS $BODY$
DECLARE 
    query_count         RECORD;
    query_has_locales   RECORD;
    query_locale        RECORD;
BEGIN
    FOR query_count IN SELECT neighborhood_code,city_code FROM  conteo_manzanas_barrio_co LOOP
    
        FOR query_has_locales IN SELECT has_locales FROM sm_city WHERE pk_city = query_count.city_code.city_code LOOP
        
            IF query_locales.has_locales THEN
                SELECT  pk_locale,name FROM sm_locale WHERE fk_pk_city = query_count.city_code;
                
                UPDATE  conteo_manzanas_barrio_co 
                SET     locale_code = pk_locale, locale_name = name 
                WHERE   neighborhood_code = query_count.neighborhood_code;
            END IF; 
        
        END LOOP;
        
    END LOOP;
 
    RETURNS TRUE;   
END $BODY$
LANGUAGE plpgsql;
 

Y EL ERROR QUE BOTA ES:

Código ERROR:

[COLOR="Red"][SIZE="3"][FONT="Comic Sans MS"]
Error de SQL:
 
ERROR:  schema "query_count" does not exist
CONTEXT:  SQL statement " SELECT has_locales FROM sm_city WHERE pk_city = query_count.city_code.city_code"
PL/pgSQL function "p_update_locales_in_count" line 8 at for over select rows
 
En la declaración:
SELECT * FROM p_update_locales_in_count()[/FONT][/SIZE][/COLOR]
 

¿EN SINTESIS QUE TIENE DE MALO MI PROCEDIMIENTO, ES ESA LA FORMA CORRECTA DE LLAMARLO?

[huesos52]

hay varias cosas en las que veo inconsistencias.

IF query_locales.has_locales THEN

Esta comparando esto con que?
no sería:
IF query_has_locales.has_locales = 'algo' THEN?
dentro del if hace esta consulta

SELECT pk_locale,name FROM sm_locale WHERE fk_pk_city = query_count.city_code;

que fin tiene esa consulta? no veo que se salven los valores retornados por la consulta para ser usados.

Donde se usa este record declarado? query_locale

Describe un poco que tratas de hacer. Tal vez haya una forma mas optima de obtener los mismos resultados.

saludos

[webness]

Ya corregi el procedimiento y no ya no existe el problema anterior. quedo asi:

Código SQL:

CREATE OR REPLACE FUNCTION p_update_locales_in_count() RETURNS BOOLEAN AS $BODY$
DECLARE 
    query_count         RECORD;
    query_has_locales   RECORD;
BEGIN
    FOR query_count IN SELECT neighborhood_code,city_code FROM  conteo_manzanas_barrio_co LOOP
    
        FOR query_has_locales IN SELECT has_locales FROM sm_city WHERE pk_city = query_count.city_code LOOP
        
            IF query_has_locales.has_locales THEN
                SELECT  pk_locale,name FROM sm_locale WHERE fk_pk_city = query_count.city_code;
                
                UPDATE  conteo_manzanas_barrio_co 
                SET     locale_code = pk_locale, locale_name = name 
                WHERE   neighborhood_code = query_count.neighborhood_code;
            END IF; 
        
        END LOOP;
        
    END LOOP;
 
    RETURNS TRUE;   
END $BODY$
LANGUAGE plpgsql;
 

AHORA LO QUE NO ES COMO LLAMARLO PUES LO ESTOY LLAMANDO ASI:

SELECT * FROM p_update_locales_in_count()

Y ME ARroja este error:

Código ERROR:

Error de SQL:
 
ERROR:  SELECT query has no destination for result data
HINT:  If you want to discard the results, use PERFORM instead.
CONTEXT:  PL/pgSQL function "p_update_locales_in_count" line 10 at SQL statement
 
En la declaración:
SELECT * FROM p_update_locales_in_count()
 

como debo llamarlo o que debo cambiarle?

[huesos52]

SELECT * FROM p_update_locales_in_count()

esta es la forma de llamar procedimientos que tienen multiples salidas (por así decirlo)

Tu procedimiento solo retorna un valor true o false entonces con llamarlo
select p_update_locales_in_count() bastará.

En el retorno haces uso de la palabra returns, la sintaxis correcta es return

saludos

[webness]

Me salio este error

Error de SQL:

ERROR: SELECT query has no destination for result data
HINT: If you want to discard the results, use PERFORM instead.
CONTEXT: PL/pgSQL function "p_update_locales_in_count" line 10 at SQL statement

En la declaración:
SELECT p_update_locales_in_count()

[huesos52]

hay varias cosas en las que veo inconsistencias.

Cita:
IF query_locales.has_locales THEN
Esta comparando esto con que?
no sería:
IF query_has_locales.has_locales = 'algo' THEN?
dentro del if hace esta consulta
Cita:
SELECT pk_locale,name FROM sm_locale WHERE fk_pk_city = query_count.city_code;
que fin tiene esa consulta? no veo que se salven los valores retornados por la consulta para ser usados.

Donde se usa este record declarado? query_locale

Describe un poco que tratas de hacer. Tal vez haya una forma mas optima de obtener los mismos resultados.

saludos
__________________

[webness]

IF query_locales.has_locales THEN -- EL VALOR CONTENIDO en query_locales.has_locales ES UN BOOLEANO, SUPONGO QUE ASI ES SUFICIENTE O NO?


SELECT pk_locale,name FROM sm_locale WHERE fk_pk_city = query_count.city_code;

lo que quiero es obtener los valores de estos dos campos para despues poder hacer una actualizacion con estos dos valores

UPDATE conteo_manzanas_barrio_co
SET locale_code = pk_locale, locale_name = name
WHERE neighborhood_code = query_count.neighborhood_code;

[huesos52]

ok.

pero la asignación está mal hecha.
Declarate otro record.

Código sql:

record_prueba record;
 
--Y en la consulta haces asi:
SELECT INTO record_prueba pk_locale,name FROM sm_locale WHERE fk_pk_city = query_count.city_code;
 
UPDATE  conteo_manzanas_barrio_co 
                SET     locale_code = record_prueba.pk_locale, locale_name = record_prueba.name 
                WHERE   neighborhood_code = query_count.neighborhood_code;
 

[webness]

Ya se ejecuto el procedimiento, pero no realizo combio alguno en la tabla que se intento modificar con el update

[huesos52]

asegurate que esté entrando al if.
crea una variable al principio de la función con valor de cero.
Dentro del if cambiale el valor a 1 y retornala.

fijate si entra o no al if.

saludos

[webness]

bUENO, POR AHORA te cuento que pase por alto que en el utlimo query no se retorna un solo valor sino muchos, entonces inclui un tercer ciclo asi:

Código SQL:

CREATE OR REPLACE FUNCTION p_update_locales_in_count() RETURNS BOOLEAN AS $BODY$
DECLARE 
    query_count         RECORD;
    query_has_locales   RECORD;
    real_data           RECORD;
BEGIN
    FOR query_count IN SELECT neighborhood_code,city_code FROM  conteo_manzanas_barrio_co LOOP
    
        FOR query_has_locales IN SELECT has_locales FROM sm_city WHERE pk_city = query_count.city_code LOOP
        
            IF query_has_locales.has_locales THEN
                FOR real_data IN SELECT pk_locale,name FROM sm_locale WHERE fk_pk_city = query_count.city_code LOOP
                
                    UPDATE  conteo_manzanas_barrio_co 
                    SET     locale_code = real_data.pk_locale, locale_name = real_data.name 
                    WHERE   neighborhood_code = query_count.neighborhood_code;
                END LOOP;   
            END IF; 
        
        END LOOP;
        
    END LOOP;
 
    RETURNS TRUE;   
END $BODY$
LANGUAGE plpgsql;
 

SI NO FUNCIONA ASI, ENTONCES PROCEDO CON LO QUE QUE ME ACABASTE DE DECIR..


GRACIAS

[webness]

AL IF SI ENTRA, PERO SUPONGO QUE NO ACTUALIZA LOS DATOS, PORQUE EL PROCESO ES DEMORADO Y SE ME PRESENTA UNA CAIDA DE LA COMUNICACION ENTRE MI CLIENTE QUE ES phpPgAdmin y el server.

Ahoro me pregunto yo

¿El commit de las transacciones de actualizacion se hace solo al final del script?, pregunto porque si se cae la conexion puede ser que no este terminado el scrpt y no haga commit

Le añadi comit por fuera del 3er ciclo. estoy ensayando

[webness]

tampoo funciono asi

[huesos52]

No es necesario.

Una función trae consigo una transacción implícita.
o se ejecuta toda la función o no se ejecuta nada.

ya debes enfocar el problema a aumentar el tiempo de vida de la sesión desde el servidor web para que se pueda ejecutar toda la función.

[webness]

Igual tenia el ultimo select mal hecho, muy mal hecho, ahora mi funcion es asi:

Código sql:

CREATE OR REPLACE FUNCTION p_update_locales_in_count() RETURNS INTEGER AS $BODY$
DECLARE 
    query_count         RECORD;
    query_has_locales   RECORD;
    real_data           RECORD;
BEGIN
    FOR query_count IN SELECT neighborhood_code,city_code FROM  conteo_manzanas_barrio_co LOOP
    
        FOR query_has_locales IN SELECT has_locales FROM sm_city WHERE pk_city = query_count.city_code LOOP
        
            IF query_has_locales.has_locales THEN
                
                SELECT  INTO real_data  sl.pk_locale,sl.name
                FROM    sm_locale sl,sm_neighborhood sn,servcon_barrios sb
                WHERE   sn.pk_neighborhood = query_count.neighborhood_code AND
                        sl.fk_pk_city = query_count.city_code AND
                        sb.cod_localidad = sl.locale_code AND
                        sb.cod_barrio = sn.neighborhood_code AND
                        sl.name = sb.nom_localidad LOOP
                
                UPDATE  conteo_manzanas_barrio_co 
                SET     locale_code = real_data.pk_locale, locale_name = real_data.name 
                WHERE   neighborhood_code = query_count.neighborhood_code;
            END IF; 
            
        END LOOP;
        
    END LOOP;
 
    RETURN 0;   
END $BODY$
LANGUAGE plpgsql;
 

y al ejecutar el procedimiento me da arroja este error

ERROR: record "real_data" is not assigned yet
DETAIL: The tuple structure of a not-yet-assigned record is indeterminate.
CONTEXT: PL/pgSQL function "p_update_locales_in_count" line 12 at select into variables
En la declaración:
select p_update_locales_in_count()

[huesos52]

webness definitivamente el que entiende bien el problema sos vos.
Yo te recomiendo algo...
create unas tablas de prueba e inserta algunos registros.
Prueba en una nueva función parte por parte de lo que tienes hecho. Mira primero en un solo for y una sentencia de actualización si los valores por los que pasa el ciclo lo hace correctamente.
Cuando te cerciores de que todo anda bien, añade otro for y haz el respectivo seguimiento de la función. Tomandolo parte por parte, entenderás mejor la panorámica del asunto y podrás identificar fácilmente los errores.

Los errores en plpgsql son muy engañosos y no siempre detectan correctamente el error.

ERROR: record "real_data" is not assigned yet

suena como si no se hubiera ejecutado el select into.
Ahora bien, cuando haces una consulta de varias tablas, lo mejor es hacer uso de sentencias join y no anidación de claves en el where. También es muy recomendable hacer uso de los alias en los campos precedidos por el nombre de la tabla. Fijate que tu campo en la consulta es sl.pk_locale y desde el record lo llamas real_data.pk_locale. Haciendo uso de alias vas a la fija.
sl.pk_locale as pkl y desde el record se llama real_data.pkl.

saludos

[webness]

Logre suprimir un ciclo, optimizando la primera consulta.

esta primera me devolvia 16800 registros, y por cada uno de estos hacia la segunda consulta.

y por cada registro de esta segunda consulta si cumple con la condicion del IF hacia las dos consultas....

ahora, optimeze el primer query devolviendo solo 8600 registros, que son los que cumplen con la condicion del IF. y ejecuto las 2 consultas

[webness]

despùes de todo, no necesite el procedimiento almacenado.

Resulta ser que mi panorama de trabajo es mas o menos con unas 160 tablas, las cuales manejan mucha redundancia y valores nulos, y se hacen muchas consultas de conteo, que son muy ineficientes, mi labor es optimizar, y para ello empeze a crear tablas nuevas como si fueran unas vistas materializadas de oracle, pero son cimplemente unas tablas que creo fruto de un query. como son tantas pues no conoxco todo el sistema.

al fin de cuentas mi solucion fue, crear otra tabla, y con ella actualizar los valores de otras 3 tablas y ya, me vali del uso de vistas.

de todos modos estoy en proceso de aprendizaje con el EL PL/PGSQL, y mi procedimiento quedo asi por si a agluein le sirve:

Código sql:

CREATE OR REPLACE FUNCTION p_update_locales_in_count() RETURNS INTEGER AS $BODY$
DECLARE 
    query_count         RECORD;
    query_has_locales   RECORD;
    real_data           RECORD;
BEGIN
    
    FOR query_count IN  SELECT  co.neighborhood_code,co.city_code 
                        FROM    conteo_manzanas_barrio_co co,sm_city ci
                        WHERE   co.city_code = ci.pk_city AND has_locales = TRUE
                        LIMIT 1 OFFSET 0 LOOP
        
        SELECT  INTO real_data  sl.pk_locale,sl.name
        FROM    sm_locale sl,sm_neighborhood sn,servcon_barrios sb
        WHERE   sn.pk_neighborhood = query_count.neighborhood_code AND
                sl.fk_pk_city = query_count.city_code AND
                sb.cod_localidad = sl.locale_code AND
                sb.cod_barrio = sn.neighborhood_code AND
                sl.name = sb.nom_localidad;
                
        UPDATE  conteo_manzanas_barrio_co 
        SET     locale_code = real_data.pk_locale, locale_name = real_data.name 
        WHERE   neighborhood_code = query_count.neighborhood_code;
        
    END LOOP;
 
    RETURN 0;   
END $BODY$
LANGUAGE plpgsql;
 

[huesos52]

gracias por compartir la solución webness