Store procedure con execute y update

[webness]

holita a todos. tengo un procedimiento almacenado que debe:
1) hacer un select
2) crear unos campos geometricos
3) actualizar los valores de estos campos geometricos
4) crear un indice geoespacial

CUANDO EJECUTO EL SP ME GENERA ERROR EN EL PUNTO 3 AL HACER UN UPDATE Y EJECUTARLO CON EXECUTE sql;

Código SQL:

CREATE OR REPLACE FUNCTION p_poblaingas_geoindex(CHARACTER VARYING)
  RETURNS void AS
$BODY$
DECLARE
    rec refcursor;
    SQL VARCHAR;
    city    ALIAS FOR $1;
    year_mi INTEGER;
    year_ma INTEGER;
    i   INTEGER;
 
BEGIN
    SQL :=  'SELECT MIN(periodo) AS min,MAX(periodo) AS max
         FROM   servcon_poblaingas_'||city;
         
    OPEN rec FOR EXECUTE SQL;
    FETCH rec INTO year_mi,year_ma;
    
    i    :=  year_mi;       
    
    LOOP
        SQL := 'SELECT AddGeometryColumn ('||QUOTE_LITERAL('servcon_poblaingas_'||city||'_'||i)||','||QUOTE_LITERAL('geom')||',4326,'||QUOTE_LITERAL('POINT')||',2)';
        EXECUTE SQL;
 
        SQL := 'UPDATE servcon_poblaingas_'||city||'_'||i||' set geom = GeometryFromText('||QUOTE_LITERAL('POINT('||cx||' '||cy||')')||',4326)';
        EXECUTE SQL;
 
        SQL := 'CREATE INDEX idx_servcon_poblaingas_'||city||'_'||i||' ON servcon_info2009 USING GIST (geom)';
        EXECUTE SQL;
        i  := i + 1;
                    
        EXIT WHEN  i > year_ma;
    END LOOP;
 
    RETURN;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;
 

el error que me saca es este:

ERROR: column "cx" does not exist
CONTEXT: SQL statement "SELECT 'UPDATE servcon_poblaingas_'|| $1 ||'_'|| $2 ||' set geom = GeometryFromText('||QUOTE_LITERAL('POINT('||cx||' '||cy||')')||',4326)'"
PL/pgSQL function "p_poblaingas_geoindex" line 23 at assignment

[huesos52]

En vez de ejecutarlo, imprime sql cuando guarde el update. Pruebalo en consola y mira si no tienes errores de concatenaciòn.

[webness]

Pero a simple vista notas algo raro, algo que deba tener en cuenta?

[huesos52]

Pues, aunque no manejo bases de datos geograficas, diría que la función POINT tiene 2 parametros y no los separas por una coma.
No se porque usas la función quote_literal por fuera de las comillas.
Pero no tengo como probar si es o no valido.

[huesos52]

ademas... en esta linea
sql := 'UPDATE servcon_poblaingas_'||city||'_'||i||' set geom = GeometryFromText('||QUOTE_LITERAL('POINT('||cx||' '||cy||')')||',4326)';
creo que te falta concatenar aca:
LITERAL('POINT('

[webness]

Don huesos, el problema esta es que estoy dejando los campos cx y cy como campos de tabla que nunca estoy espceficiando.

la cadena que necesito que se ejecute debe quedar algo asi como esto:


UPDATE servcon_poblaingas_05001_2007 set geom = GeometryFromText('POINT('||cx||' '||cy||')',4326);

[huesos52]

Lo mejor es que la imprimas y la retornes y desde ahí mires donde está el fallo.

[webness]

Hola huesos, ya estoy que soluciono mi problema, y efectivamente en un lio algo complicado con las comillas, para lo cual uso QUOTE_LITERAL

pero siempre lo he usado asi: QUOTE_LITERAL(7) Y ME DEVUELVE '7' o QUOTE_LITERAL('HOLA MUNDO') y añade al string existe 'HOLA MUNDO', pero ahora solo quiero añadir una comilla SIMPLE, mande la funcion sin parametros y genera error.

como lo puedo solucionar?

[huesos52]

A ver si te entiendo.

Cuando haces quote_literal(7) te retorna '7'.
CUando dices que quieres solo una comilla quisieras que te retornara algo así?
'''
3 comillas simples? Eso en la cadena te traería muchsimos problemas al no saber que comillas cierran o abren las cadenas.

Podrías poner un ejemplo simple de lo que quiers lograr?

saludos webness

[webness]

CUANDO DIGO QUE ME QUIERO UNA SOLA COMILLA ES simplemente esto ' entonces asi como hago QUOTE_LITERAL(8) para obtener '8', que debo hacer para obtener simplemente ' ?


OSEA HUESOS: LA VARIABLE sql inicialmente esta asi

Código SQL:

SQL := 'UPDATE servcon_poblaingas_'||city||'_'||i||' t set geom = GeometryFromText('||QUOTE_LITERAL('POINT('||cx||' '||cy||')')||',4326)';
 

poniendo problema por el PL cree el cx y el cy son variables previamente definidas, cuando necesito que sean parte del texto, es decir necesito que la cadena sql, contenga literalmente los valor cx y y cy de tal modo que al ejecutarce quede algo como lo que indique mas arriba:

UPDATE servcon_poblaingas_05001_2007 set geom = GeometryFromText('POINT('||cx||' '||cy||')',4326), para lo cual es necesario meter las barras || y el cx y el cy dentro de mas comillas se me ocurre ami

[huesos52]

Vamos a simplificar esto...
Postea el update que quieres que se ejecute.
sin concatenaciones, sin nada...
UPDATE servcon_poblaingas_bogota_1 t SET ....

+Postealo tal cual sabes que funciona. Con esto, yo te ayudo a armar la cadena para que quede en la variable sql y pueda ser ejecutada.

[webness]

ESTO ES LO QUE NORMALMENTE EJECUTO Y FUNCION

UPDATE servcon_poblaingas_05001_2007 set geom = GeometryFromText('POINT('||cx||' '||cy||')',4326)

[huesos52]

Listo.

Código SQL:

SQL := 'UPDATE servcon_poblaingas_'||city||'_'||i||' set geom = GeometryFromText(''POINT(''||cx||'' ''||cy||'')'',4326)';
 

[webness]

Eselente ya me funciono y asi quedo:

Código SQL:

CREATE OR REPLACE FUNCTION p_poblaingas_geoindex(CHARACTER VARYING)
  RETURNS VOID AS
$BODY$
DECLARE
    rec refcursor;
    SQL VARCHAR;
    city    ALIAS FOR $1;
    year_mi INTEGER;
    year_ma INTEGER;
    i   INTEGER;
 
BEGIN
    SQL :=  'SELECT MIN(periodo) AS min,MAX(periodo) AS max
         FROM   servcon_poblaingas_'||city;
         
    OPEN rec FOR EXECUTE SQL;
    FETCH rec INTO year_mi,year_ma;
    
    i    :=  year_mi;       
    
    LOOP
        EXECUTE 'SELECT AddGeometryColumn ('||QUOTE_LITERAL('servcon_poblaingas_'||city||'_'||i)||','||QUOTE_LITERAL('geom')||',4326,'||QUOTE_LITERAL('POINT')||',2)';
        EXECUTE 'UPDATE servcon_poblaingas_'||city||'_'||i||' set geom = GeometryFromText(''POINT(''||CX||'' ''||CY||'')'',4326)';
        EXECUTE 'CREATE INDEX idx_poblaingas_'||city||'_'||i||' ON servcon_poblaingas_'||city||'_'||i||' USING GIST (geom)';
        i  := i + 1;
                    
        EXIT WHEN  i > year_ma;
    END LOOP;
 
    RETURN;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;