Procedimiento con campos de select variables

[webness]

HOLA.


tengo un procedimiento almacenado, el cual puede ejecutar 3 query diferentes, estos a su ves son variables en los campos del select. el procedimiento debe retorna los registros del query que ejecute.

colocar el nombre de la tabla en el tipo de retorno no puedo, porque la conuslta segun el query es a una tabla distinta.

por ahora tengo cree un tipo con la maxima cantidad de datos del select que retorno, lo cual me obligo a usar comodines en los otros querys.

es decir que si en el tipo creado tengo los campos A,B,C,D,E,F Y en el query Uno retorno todos los campos, pero en el query dos retorno SOLO A,B,C,'','',F Y EN el tercer query el select queda por ejemplo asi A,'',B,C,D,'',F.

ADJUNTO EL PROCEDIMIENTO REAL

Código SQL:

CREATE OR REPLACE FUNCTION p_get_blocks_count(INTEGER, VARCHAR,VARCHAR,VARCHAR,VARCHAR) RETURNS SETOF t_blocks_count AS $BODY$
DECLARE 
    FILTER              ALIAS FOR $1;  
    area_list           ALIAS FOR $2;
    city_list           ALIAS FOR $3;
    socioeconomic_list  ALIAS FOR $4;
    country             ALIAS FOR $5;
    
    sql_query VARCHAR;
    query_result t_blocks_count;
BEGIN
    IF FILTER = 1 THEN
        sql_query := '  SELECT  country_name,region_name,city_name,locale_name,locale_code,$$''$$,$$''$$,socioeconomic_status,city_code,total
                        FROM    conteo_manzanas_localidad_'||country||'
                        WHERE   locale_code             IN ('||area_list||') AND 
                                city_code               IN ('||city_list||') AND
                                socioeconomic_status    IN ('||socioeconomic_list||')';
        FOR query_result IN EXECUTE sql_query LOOP RETURN NEXT query_result;
        END LOOP;           
    ELSE
        IF FILTER = 2 THEN
            sql_query := '  SELECT  country_name,region_name,city_name,locale_name,locale_code,neighborhood_name,neighborhood_code,socioeconomic_status,city_code,total
                            FROM    conteo_manzanas_barrio_'||country||'
                            WHERE   neighborhood_code       IN ('||area_list||') AND 
                                    city_code               IN ('||city_list||') AND
                                    socioeconomic_status    IN ('||socioeconomic_list||')';
            FOR query_result IN EXECUTE sql_query LOOP RETURN NEXT query_result;
            END LOOP;
        ELSE
            sql_query := '  SELECT  country_name,region_name,city_name,$$''$$,$$''$$,$$''$$,$$''$$,socioeconomic_status,city_code,total
                            FROM    conteo_manzanas_municipio_'||country||'
                            WHERE   city_code               IN ('||city_list||') AND
                                    socioeconomic_status    IN ('||socioeconomic_list||')';
            FOR query_result IN EXECUTE sql_query LOOP RETURN NEXT query_result;
            END LOOP;
        END IF;
    END IF; 
    RETURN;
END $BODY$
LANGUAGE plpgsql;
 

existe alguna forma de evitar eso?

[huesos52]

Sin mirar muy a detalle el procedimiento

No podrías mandar el nombre de la tabla como parametro? Y en otro campo, mandar la cantidad que retorna el select?

Quedaría un procedimiento mucho mas limpio.

saludos

[webness]

pues la idea es dejar el codigo php lo mas limpio posible, puesto que no quiero calcular en el php el nombre de la tabla. puesto que la idea es que otros programadores utilicen mis metodos php para obtener simplemente la informacion, es decir, la idea es que ellos hagan

$obj->getData($param);

[xdrtas]

Hola webness, mira, no entiendo muy bien, pero por qué no usas SETOF record en vez de un tipo hecho por tí? El Record se construye automáticamente con los campos que tenga la consulta. Si tu primera consulta tiene A,B,C, así será el record devuelto, si la segunda consulta es A,D,E,F así será tu record para esa segunda consulta.

Un ejemplo de una consulta que tengo hecha para una página web que estoy haciendo:

Código sql:

CREATE OR REPLACE FUNCTION fpaginartabla(num SMALLINT, tam INTEGER, reg INTEGER)
  RETURNS SETOF record AS
$BODY$
        DECLARE
            datos record;
        BEGIN
            IF (num = 1) THEN
                FOR datos IN
                    SELECT "alb_ID", "alb_Titulo" FROM "Album" ORDER BY "alb_ID" LIMIT tam OFFSET reg
                LOOP
                    RETURN NEXT datos;
                END LOOP;
            END IF;
            IF (num = 2) THEN
                FOR datos IN
                    SELECT "usr_ID", ("usr_Nombre" || ' ' || "usr_Apellido")::VARCHAR AS "Nombre", "usr_Alias" FROM "Usuario" WHERE "usr_Nivel" < 3 ORDER BY "usr_ID" LIMIT tam OFFSET reg
                LOOP
                    RETURN NEXT datos;
                END LOOP;
            END IF;
        END;
    $BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION fpaginartabla(SMALLINT, INTEGER, INTEGER) OWNER TO postgres;
 

Si te fijas, la primera consulta me devuelve dos parámetros y la segunda consulta me devuelve tres, y no tengo que agregar comodines.

O quieres hacer otra cosa?

Espero tu respuesta, saludos.

[webness]

Cuantas filas de resultados devuelven tus consultas?

Al inicio intente con RECORD pero no me funciono. tiene algo que ver si retorno una fila o varias?. de todos modos voy a ensayar a ver que pasa

[webness]

me salio el suiguiente error

Error de SQL:

ERROR: a column definition list is required for functions returning "record"

En la declaración:
SELECT * FROM p_test(1, $$122,121,120,119,118$$ ,$$739$$,$$'2','3','4'$$,'co')

[xdrtas]

Iniciado por webness

Cuantas filas de resultados devuelven tus consultas?

Al inicio intente con RECORD pero no me funciono. tiene algo que ver si retorno una fila o varias?. de todos modos voy a ensayar a ver que pasa

Desde PHP mando a que me devuelva 10 registros o tuplas por parámetro, y por supuesto eso lo puedo cambiar para que me devuelva desde 1 hasta N usando el comando LIMIT.

Ese error que te salió es porque no creaste la lista de retorno de la consulta. Te pongo el ejemplo con mi función:

Código sql:

//Este ejemplo es con la función que puse yo para que me entiendas:
SELECT * fpaginartabla(1::SMALLINT, 10, 0) AS (id INTEGER, tit VARCHAR);
 
SELECT * fpaginartabla(2::SMALLINT, 10, 0) AS (id INTEGER, nombre VARCHAR, alias VARCHAR);
 

Como puedes ver en tu consulta a la función "SELECT * FROM p_test(1, $$122,121,120,119,118$$ ,$$739$$,$$'2','3','4'$$,'co')" te falta el listado con los valores de retorno de tu consulta.

En mi llamada a función, el 10 es el número de tuplas que quiero que me traiga la función desde la primera que es el 0.

Espero tu respuesta.

[webness]

Me sale un error

ERROR: wrong record type supplied in RETURN NEXT
CONTEXT: PL/pgSQL function "p_test" line 12 at return next

En la declaración:
SELECT *
FROM p_test(1, $$122,121,120,119,118$$ ,$$739$$,$$'2','3','4'$$,'co') AS
(country_name varchar ,
region_name varchar,
city_name varchar,
locale_name varchar,
locale_code integer,
socioeconomic_status varchar,
city_code integer,
total integer) ;

[webness]

Me sale un error

ERROR: wrong record type supplied in RETURN NEXT
CONTEXT: PL/pgSQL function "p_test" line 12 at return next

En la declaración:
SELECT *
FROM p_test(1, $$122,121,120,119,118$$ ,$$739$$,$$'2','3','4'$$,'co') AS
(country_name varchar ,
region_name varchar,
city_name varchar,
locale_name varchar,
locale_code integer,
socioeconomic_status varchar,
city_code integer,
total integer) ;

[webness]

Ya era problema mio con los tipos de datos.

Ahora bien, esta solucion que me planteas le he aprendido, pero no la voy a implementar, pues lo que quiero es ocultarle al programador muchos de talles de la base datos. Igual me sirve para otras cosas que tengo que hacer.

[xdrtas]

Iniciado por webness

ERROR: wrong record type supplied in RETURN NEXT
CONTEXT: PL/pgSQL function "p_test" line 12 at return next

Este error es porque uno de los tipos de datos devueltos por la consulta no coincide con la definición que el pusiste en el listado "AS(country_name varchar ,
region_name varchar,
city_name varchar,
locale_name varchar,
locale_code integer,
socioeconomic_status varchar,
city_code integer,
total integer
);"

Puede que uno de los parámetros devuelto en la consulta no sea varchar, sino char o text, o que no sea integer sino bigint o smallint. Revisa bien Te estás acercando.

[webness]

Ya era problema mio con los tipos de datos.

Ahora bien, esta solucion que me planteas le he aprendido.

Pero lastimosamente no la voy a implementar, pues lo que quiero es ocultarle al programador muchos de talles de la base datos. Igual quiero facilitarle la vida al desarrollador, puesto que de la forma no ideal, llamo de una forma muy facil el procedimiento desde php

Código PHP:

  $this->strSQL =   "SELECT * FROM p_get_blocks_count(".$params[0].", $$".$params[1]."$$ ,$$".$params[2]."$$,$$".$params[3]."$$,'co')"; 
   

Igual me sirve para otras cosas que tengo que hacer.

THANKS

[xdrtas]

Iniciado por webness

Pero lastimosamente no la voy a implementar, pues lo que quiero es ocultarle al programador muchos de talles de la base datos.

No entiendo exactamente que es lo que quieres hacer, o mejor dicho, que es lo que quieres ocultar, sin embargo me alegro de haberte ayudado aunque sea en algo.

Yo uso este tipo de funciones y todo queda oculto, ya que el programador nunca ve como es la función internamente,(o sea, no conoce la estructura de la tabla), solo sabe los valores que tiene que insertar y cuales son los que le va a devolver la función, evidentemente, el programador necesita saber cuales son esos datos para trabajar con ellos.

Pero ya sabes, cualquier cosa y si se puede por aqui estamos para ayudar en lo que se pueda, saludos y suerte con lo que buscas.

[xdrtas]

Iniciado por webness

Igual quiero facilitarle la vida al desarrollador, puesto que de la forma no ideal, llamo de una forma muy facil el procedimiento desde php

Código PHP:

  $this->strSQL =   "SELECT * FROM p_get_blocks_count(".$params[0].", $$".$params[1]."$$ ,$$".$params[2]."$$,$$".$params[3]."$$,'co')"; 
   

Y no quieres que llamen al procedimiento almacenado directamente?