Problemas con el manejo de comillas en PL/pgSQL

[webness]

Buenos dias, esta es mi duda:

tengo un procedimiento almacenado que ejecuta una consulta como esta:

select p.nombre_pais,
r.nombre,
c.nombre,
l.nom_localidad,
m.nivsocio,
count(m.id) as total
from servcon_manzanas m

INNER JOIN servcon_localidades l ON (m.coddane = l.cod_dane AND m.localidad = l.cod_localidad AND l.cod_localidad IN ('11','01','02','04','06','8','12','13','15'))
INNER JOIN ciudades c ON (m.coddane = c.coddane)
INNER JOIN servcon_regiones r ON (m.codregion = r.cod_region)
INNER JOIN servcon_paises p ON (m.codpais = p.codigo)

where m.coddane = '11001' and
m.localidad IN ('11','01','02','04','06','8','12','13','15') and
m.nivsocio in ('3','2','6','4','5')
group by l.nom_localidad, m.nivsocio,c.nombre,r.nombre,p.nombre_pais
order by m.nivsocio,l.nom_localidad.

Pero los valores de filtro para:

l.cod_localidad IN ('11','01','02','04','06','8','12','13','15')
m.nivsocio in ('3','2','6','4','5')
m.coddane = '11001'

No se como enviarlos como parametro cuando invoco el procedimiento, y tampoco se como colocarlos en el codigo del procedimiento al cual llamo asi:

select * from p_blocks('"11","01","02","04","06","82,"12","13","15"','"2","3","2","6","4","5"','11001')
y no genera error, el query dentro del procedimiento lo cree asi y no genero error tampoco, pero al invocar el procedimiento no genera resultados y estoy esperando 27 filas.

--DEFINICION DE LA CONSULTA DENTRO DE p_blocks
select p.nombre_pais,
r.nombre AS region,
c.nombre AS ciudad,
l.nom_localidad,
m.nivsocio,
count(m.id) as total
from servcon_manzanas m

INNER JOIN servcon_localidades l ON (m.coddane = l.cod_dane AND m.localidad = l.cod_localidad AND l.cod_localidad IN ($1))
INNER JOIN ciudades c ON (m.coddane = c.coddane)
INNER JOIN servcon_regiones r ON (m.codregion = r.cod_region)
INNER JOIN servcon_paises p ON (m.codpais = p.codigo)

where m.coddane = $3 and
m.localidad IN ($1) and
m.nivsocio in ($2)
group by l.nom_localidad, m.nivsocio,c.nombre,r.nombre,p.nombre_pais
order by m.nivsocio,l.nom_localidad

LOOP RETURN NEXT query_result;
END LOOP;

alguien sabe como solucionar el problema

[huesos52]

Que tal webness
postea el procedimiento completo.
También la forma como lo ejecutas.

saludos

[webness]

CREATE OR REPLACE FUNCTION p_blocks(varchar, varchar,varchar) RETURNS SETOF type_block AS $BODY$
DECLARE
query_result type_block;

BEGIN
FOR query_result IN
select p.nombre_pais,
r.nombre AS region,
c.nombre AS ciudad,
l.nom_localidad,
m.nivsocio,
count(m.id) as total
from servcon_manzanas m

INNER JOIN servcon_localidades l ON (m.coddane = l.cod_dane AND m.localidad = l.cod_localidad AND l.cod_localidad IN ($1))
INNER JOIN ciudades c ON (m.coddane = c.coddane)
INNER JOIN servcon_regiones r ON (m.codregion = r.cod_region)
INNER JOIN servcon_paises p ON (m.codpais = p.codigo)

where m.coddane = $3 and
m.localidad IN ($1) and
m.nivsocio in ($2)
group by l.nom_localidad, m.nivsocio,c.nombre,r.nombre,p.nombre_pais
order by m.nivsocio,l.nom_localidad

LOOP RETURN NEXT query_result;
END LOOP;

return;
END $BODY$
LANGUAGE plpgsql;


lo llamo asi:
$db->execute("select * from p_blocks(' ''11","01","02","04","06","8","12","13","15" ', ' "2",''3","2","6","4","5",'11001')");

[huesos52]

que es type_block?

[webness]

Es un tipo que cree para poder retornar el resultado del query, puesto que estoy sacando datos de varias tablas, eso creo que no tiene problema pues si ejecuto el SP con los datos quemados funciona correctamente

[huesos52]

ya entendí el problema.
te pongo un ejemplo para que veas como funciona

Código sql:

pruebas=> SELECT *FROM medico;
 id | nombre
----+---------
  1 | daniel
  2 | daniela
  3 | ana
(3 filas)
 
pruebas=> CREATE OR REPLACE FUNCTION inf_concepto_nombre(nombre text)
pruebas->   RETURNS SETOF medico AS
pruebas-> $BODY$
pruebas$> DECLARE
pruebas$>
pruebas$> fila medico%rowtype;
pruebas$> SQL text;
pruebas$> BEGIN
pruebas$> SQL = 'select id,nombre FROM medico where nombre in(' || nombre || ')'
;
pruebas$>
pruebas$> FOR fila IN EXECUTE SQL
pruebas$> LOOP
pruebas$> RETURN NEXT fila;
pruebas$> END LOOP;
pruebas$> RETURN;
pruebas$> END;
pruebas$> $BODY$
pruebas->   LANGUAGE 'plpgsql' VOLATILE;
CREATE FUNCTION
pruebas=> SELECT *FROM inf_concepto_nombre($$'daniel','ana'$$);
 id | nombre
----+--------
  1 | daniel
  3 | ana
(2 filas)
 
pruebas=>
 

El el llamado de la función se puede valer de $$ para poder ingresar las comillas (') como parte de la cadena.

Nos cuentas como te va

[webness]

BUENO TRANSFORME MI PROCEDEMIENTO ALMACENADO A ESTO:

CREATE OR REPLACE FUNCTION p_get_blocks_count_co(integer, varchar,varchar,varchar) RETURNS SETOF type_block AS $BODY$
DECLARE
filter ALIAS FOR $1;
locales ALIAS FOR $2;
city ALIAS FOR $3;
socioeconomic ALIAS FOR $4;
query_result type_block;
BEGIN
IF filter = 1 THEN
FOR query_result IN
SELECT *
FROM conteo_manzanas_localidad_co
WHERE locale_code IN (' | locales | ') AND
city_code = ' | city | ' AND
socioeconomic_status IN (' | socioeconomic | ')

LOOP RETURN NEXT query_result;
END LOOP;
ELSE
FOR query_result IN
SELECT *
FROM conteo_manzanas_barrios_co
WHERE locale_code IN (' | locales | ') AND
city_code = ' | city | ' AND
socioeconomic_status IN (' | socioeconomic | ')

LOOP RETURN NEXT query_result;
END LOOP;
END IF;
RETURN;
END $BODY$
LANGUAGE plpgsql;

Y EL LLAMADO LO ESTOY HACIENDO DE ESTA FORMA:

SELECT *
FROM get_blocks_count_co(1,$$'11','01','02','04','06',' 8','12','13','15'$$,'11001',$$'3','2','6','4','5'$ $)

PERO ME GENERA EL SIGUIENTE ERROR:

Error de SQL:

ERROR: function get_blocks_count_co(integer, "unknown", "unknown", "unknown") does not exist
HINT: No function matches the given name and argument types. You may need to add explicit type casts.

[webness]

Upsss, no me habia fijado estaba llamando mal al procedimiento, ya no genera error pero no me esta devolviendo resultados

[huesos52]

la concatenación la haces con un solo |
Tengo entendido que debe ser con ||.

ahora bien, si no estas ejecutando una variable, no veo la necesidad de concatenar la consulta si la ejecutas directamente dentro del for.

Los errores que te muestra, puedes ensayar varias cosas:

ERROR: function get_blocks_count_co(integer, "unknown", "unknown", "unknown") does not exist
HINT: No function matches the given name and argument types. You may need to add explicit type casts.

No está reconociendo los 3 ultimos parametros, así que puedes hyudar a la función haciendo los respectivos parseos.

Código sql:

SELECT  *
FROM get_blocks_count_co(1,$$'11','01','02','04','06',' 8','12','13','15'$$::VARCHAR,'11001'::VARCHAR,$$'3','2','6','4','5'$$::VARCHAR)
 

Te recomiendo hacer pruebas sencillas para detectar facilmente donde se presentan los errores. Prueba primero con una consulta simple sin variables de entrada, sino con los datos dentro de la función y asegurate donde detectar los errores

[webness]

como hago para insertar los bloques de codigo:

Cambie el SP quitandole las variables y dejandole los datos quemados, y cuando lo llamo le hice el parseo que me sugeriste y me da los resultados esperados, pero necesito obviamente que estos valores sean dinamicos.

[huesos52]

fijate en el ejemplo que te pasé en el 6to post.

La variable nombre es la de entrada y se ubica en el in de la consulta.
Vos tenés un ejemplo complejo y como no tengo ni el tipo de dato que utilizas (me imagino que lo creaste con create type) ni las tablas relacionadas en la consulta.

El secreto de todo es realizar pruebas de que retorna cada parte de la función.

Yo siempre me creo una pequeña función que me retorne toda la cadena del select para tener claro que no me estoy equivocando en ninguna concatenación. De ahí en adelante, debes detectar que devuelve cada operación del procedimiento e ir corrigiendo las inconsistencias.

[webness]

Bueno, muchas gracias por tu ayuda, creo al final de cuentas mi error era que no lei a cabalidad tu ejemplo: El resultado final fue:

Código PHP:

  CREATE OR REPLACE FUNCTION p_get_blocks_count(integer, varchar,varchar,varchar,varchar) RETURNS SETOF type_block_count AS $BODY$ 
DECLARE  
    filter    ALIAS FOR $1;   
    list    ALIAS FOR $2; 
    city      ALIAS FOR $3; 
    socioeconomic    ALIAS FOR $4; 
    country ALIAS FOR $5; 
     
    sql_query varchar; 
    query_result type_block_count; 
BEGIN 
    IF filter = 1 THEN 
        sql_query := 'SELECT    * 
                       FROM    conteo_manzanas_localidad_'||country||' 
                       WHERE   locale_code IN ('||list||') AND  
                               city_code = '||city||' AND 
                               socioeconomic_status IN ('||socioeconomic||')'; 
        FOR query_result IN EXECUTE sql_query LOOP RETURN NEXT query_result; 
        END LOOP;             
    ELSE 
        sql_query := 'SELECT    * 
                       FROM    conteo_manzanas_barrios_'||country||' 
                       WHERE   locale_code IN ('||list||') AND  
                               city_code = '||city||' AND 
                               socioeconomic_status IN ('||socioeconomic||')'; 
        FOR query_result IN EXECUTE sql_query LOOP RETURN NEXT query_result; 
        END LOOP;                 
        END IF; 
    RETURN; 
END $BODY$ 
LANGUAGE plpgsql;  
   

EL LLAMADO FINAL A LA FUNCION FUE:

Código PHP:

  SELECT    * 
FROM     p_get_blocks_count(1,$$'11','01','02','04','06','8','12','13','15'$$::varchar,'11001',$$'3','2','6','4','5'$$::varchar,'co')  
   

Y EL TIPO QUE CREE FUE ESTE:

Código PHP:

   CREATE TYPE type_block_count AS 
(country_name varchar, 
region_name varchar, 
city_name varchar, 
locale_name varchar, 
locale_code varchar, 
socioeconomic_status varchar, 
city_code varchar, 
total integer)  
   

MUCHAS GRACIAS POR TUS APORTES APRENDI BASTANTE.

[huesos52]

De nada