return select

death_nemesis · #1 (**permalink**) 09/08/2010, 11:15

hola amigos tengo un problema necesito retornar un select con todas las filas y columnas que pueda traer y no se como hacerlo...
este es el codigo que tengo, ojala me puedan ayudar

Código SQL:

Ver originalCREATE OR REPLACE FUNCTION prueba(idTaller INTEGER, Alias VARCHAR(255)) RETURNS SETOF VEHICULOS AS $$
--DECLARE 
 
BEGIN 
    RETURN QUERY EXECUTE 'select count(VEHICULOS.id_vehiculo)as Vehiculos_taller,locales.capacidad_instalada as Capacidad_Taller from SINIESTROS,COMPANIAS,VEHICULOS,LOCALES,TALLERES
    where SINIESTROS.id_compania = COMPANIAS.id_compania and SINIESTROS.id_vehiculo = VEHICULOS.id_vehiculo and VEHICULOS.id_estado <> 12 and 
    VEHICULOS.id_local   = LOCALES.id_local and LOCALES.id_taller = TALLERES.id_taller and TALLERES.id_taller ='||idTaller||' and COMPANIAS.alias = upper('||Alias||')
    group by locales.capacidad_instalada UNION  select count(VEHICULOS.id_vehiculo),locales.capacidad_instalada from SINIESTROS,COMPANIAS,VEHICULOS,LOCALES,TALLERES
    where SINIESTROS.id_compania = COMPANIAS.id_compania and SINIESTROS.id_vehiculo = VEHICULOS.id_vehiculo and VEHICULOS.id_estado <> 12 and
    VEHICULOS.id_local   = LOCALES.id_local and LOCALES.id_taller = TALLERES.id_taller and TALLERES.id_taller = '||idTaller||' and COMPANIAS.alias <> upper('||Alias||')
    group by locales.capacidad_instalada';
    
END;
$$
LANGUAGE plpgsql;

huesos52 · #2 (**permalink**) 09/08/2010, 12:43

Mirate esto
http://www.depesz.com/index.php/2008...uple_fraction/

En el retorno debes hacerlo como record al ser una consulta que involucra varias tablas.

saludos

death_nemesis · #3 (**permalink**) 09/08/2010, 13:25

oka huesos52 lo estoy revisando apenas tenga resultados ( buenos o malos) comento

death_nemesis · #4 (**permalink**) 09/08/2010, 13:36

me fue mal viejo lo deje así

Código SQL:

Ver originalCREATE OR REPLACE FUNCTION prueba(IN idTaller INTEGER,IN Alias VARCHAR(255),OUT VehiculosTaller BIGINT,OUT CapacidadTaller SMALLINT) RETURNS SETOF RECORD AS $BODY$
DECLARE
use_sql TEXT;
BEGIN
       use_sql := 'select count(VEHICULOS.id_vehiculo)as VehiculosTaller,locales.capacidad_instalada as CapacidadTaller from SINIESTROS,COMPANIAS,VEHICULOS,LOCALES,TALLERES
    where SINIESTROS.id_compania = COMPANIAS.id_compania and SINIESTROS.id_vehiculo = VEHICULOS.id_vehiculo and VEHICULOS.id_estado <> 12 and 
    VEHICULOS.id_local   = LOCALES.id_local and LOCALES.id_taller = TALLERES.id_taller and TALLERES.id_taller =' || idTaller ||' 
    and COMPANIAS.alias = upper(' || Alias ||' )
    group by locales.capacidad_instalada UNION ALL select count(VEHICULOS.id_vehiculo),locales.capacidad_instalada from SINIESTROS,COMPANIAS,VEHICULOS,LOCALES,TALLERES
    where SINIESTROS.id_compania = COMPANIAS.id_compania and SINIESTROS.id_vehiculo = VEHICULOS.id_vehiculo and VEHICULOS.id_estado <> 12 and
    VEHICULOS.id_local   = LOCALES.id_local and LOCALES.id_taller = TALLERES.id_taller and TALLERES.id_taller = ' || idTaller ||' and COMPANIAS.alias <> upper(' || Alias ||')
    group by locales.capacidad_instalada';
    RETURN QUERY EXECUTE use_sql;
$BODY$
LANGUAGE plpgsql;

y me tira este error

Código SQL:

Ver originalERROR:  syntax error at END OF INPUT
LINE 14: $BODY$
         ^
 
********** Error **********

huesos52 · #5 (**permalink**) 09/08/2010, 13:51

Falta el END antes del $BODY$.

saludos

death_nemesis · #6 (**permalink**) 09/08/2010, 14:01

Código SQL:

Ver originalERROR:  syntax error at OR near "END"
LINE 36: END;
         ^
 
********** Error **********
 
ERROR: syntax error at OR near "END"
Estado SQL:42601
Caracter: 2479

huesos52 · #7 (**permalink**) 09/08/2010, 14:07

death_nemesis acabo de hacer una prueba con el ejemplo que te sugerí y una consulta que me involucraba 2 tablas de mi base de datos. He probado y me anda de lujos. Lo que tienes es un error de sintaxis que puede estar escondido en la construcción de tu cadena. Revisa con cuidado donde puede estar fallando tu código.

2 cosas.
Vuelve a postear la totalidad del codigo.
Hazte una función aparte donde puedas retornar como texto la variable use_sql para tener la certeza que la sentencia se está armando correctamente,

saludos

death_nemesis · #8 (**permalink**) 09/08/2010, 14:19

ya mira viejo me funciona pero a medias este es el codigo

Código SQL:

Ver originalCREATE OR REPLACE FUNCTION prueba(idTaller INTEGER,IN Alias VARCHAR(255),OUT VehiculosTaller BIGINT,OUT CapacidadTaller SMALLINT) RETURNS SETOF RECORD AS $BODY$
DECLARE
    use_sql TEXT;
BEGIN  
use_sql := 'select count(VEHICULOS.id_vehiculo)as VehiculosTaller,locales.capacidad_instalada as CapacidadTaller from SINIESTROS,COMPANIAS,VEHICULOS,LOCALES,TALLERES
where SINIESTROS.id_compania = COMPANIAS.id_compania and SINIESTROS.id_vehiculo = VEHICULOS.id_vehiculo and VEHICULOS.id_estado <> 12 and 
VEHICULOS.id_local   = LOCALES.id_local and LOCALES.id_taller = TALLERES.id_taller and TALLERES.id_taller = 5 and 
COMPANIAS.alias = upper(''bci'')group by locales.capacidad_instalada UNION ALL 
select count(VEHICULOS.id_vehiculo),locales.capacidad_instalada from SINIESTROS,COMPANIAS,VEHICULOS,LOCALES,TALLERES 
where SINIESTROS.id_compania = COMPANIAS.id_compania and SINIESTROS.id_vehiculo = VEHICULOS.id_vehiculo and VEHICULOS.id_estado <> 12 and
VEHICULOS.id_local   = LOCALES.id_local and LOCALES.id_taller = TALLERES.id_taller and TALLERES.id_taller = 5 and COMPANIAS.alias <> upper(''bci'')
group by locales.capacidad_instalada';  
RETURN QUERY EXECUTE use_sql;  
END;
$BODY$
LANGUAGE plpgsql;
-- estos valores fueron por poner nomas ya que despues voy a arreglar lo de la concatenacion
SELECT prueba (5,'text');

Código SQL:

Ver originalPrueba
Record
-------------
"(2,30)"
"(1,30)"

la idea es que aparesca asi

Código SQL:

Ver originalVehiculosTaller            CapacidadTaller  
-------------------        -----------------------
        2                               30
        1                               30

huesos52 · #9 (**permalink**) 09/08/2010, 14:20

Ejecutalo así

Código SQL:

Ver originalSELECT *FROM prueba(5,'text');

saludos

death_nemesis · #10 (**permalink**) 09/08/2010, 14:26

vale huesos52 ya esta funcionando bien....ahora termino lo de la concatenacion y posteo el codigo por si a alguien mas le sirve

huesos52 · #11 (**permalink**) 09/08/2010, 14:32

death_nemesis · #12 (**permalink**) 09/08/2010, 14:44

jajaj ahora tengo otro problema resulta que al concatenar la variable me tira un resultado que no me sirve pero al no concatenar me tira bien el resultado

creo que se explica mejor con el codigo

CONCATENADO alias (No me sirve el resultado)

Código SQL:

Ver originalCREATE OR REPLACE FUNCTION prueba(idTaller INTEGER,IN alias VARCHAR(255),OUT VehiculosTaller BIGINT,OUT CapacidadTaller SMALLINT) RETURNS SETOF RECORD AS $BODY$
DECLARE
    use_sql TEXT;
BEGIN  
use_sql := 'select count(VEHICULOS.id_vehiculo)as VehiculosTaller,locales.capacidad_instalada as CapacidadTaller from SINIESTROS,COMPANIAS,VEHICULOS,LOCALES,TALLERES
where SINIESTROS.id_compania = COMPANIAS.id_compania and SINIESTROS.id_vehiculo = VEHICULOS.id_vehiculo and VEHICULOS.id_estado <> 12 and 
VEHICULOS.id_local   = LOCALES.id_local and LOCALES.id_taller = TALLERES.id_taller and TALLERES.id_taller = '||idTaller||' and 
COMPANIAS.alias = upper('||'alias'||')group by locales.capacidad_instalada UNION ALL 
select count(VEHICULOS.id_vehiculo),locales.capacidad_instalada from SINIESTROS,COMPANIAS,VEHICULOS,LOCALES,TALLERES 
where SINIESTROS.id_compania = COMPANIAS.id_compania and SINIESTROS.id_vehiculo = VEHICULOS.id_vehiculo and VEHICULOS.id_estado <> 12 and
VEHICULOS.id_local = LOCALES.id_local and LOCALES.id_taller = TALLERES.id_taller and TALLERES.id_taller = '||idTaller||' and COMPANIAS.alias <> upper('||'alias'||')
group by locales.capacidad_instalada';  
RETURN QUERY EXECUTE use_sql;  
END;
$BODY$
LANGUAGE plpgsql;
 
SELECT * FROM prueba (5,'bci');
 
 
VehiculosTaller           CapacidadTaller
---------------------    ---------------------------
            3                             3

SIN CONCATENAR alias (SI me sirve el resultado)

Código SQL:

Ver originalCREATE OR REPLACE FUNCTION prueba(idTaller INTEGER,IN alias VARCHAR(255),OUT VehiculosTaller BIGINT,OUT CapacidadTaller SMALLINT) RETURNS SETOF RECORD AS $BODY$
DECLARE
    use_sql TEXT;
BEGIN  
use_sql := 'select count(VEHICULOS.id_vehiculo)as VehiculosTaller,locales.capacidad_instalada as CapacidadTaller from SINIESTROS,COMPANIAS,VEHICULOS,LOCALES,TALLERES
where SINIESTROS.id_compania = COMPANIAS.id_compania and SINIESTROS.id_vehiculo = VEHICULOS.id_vehiculo and VEHICULOS.id_estado <> 12 and 
VEHICULOS.id_local   = LOCALES.id_local and LOCALES.id_taller = TALLERES.id_taller and TALLERES.id_taller = '||idTaller||' and 
COMPANIAS.alias = upper(''bci'')group by locales.capacidad_instalada UNION ALL 
select count(VEHICULOS.id_vehiculo),locales.capacidad_instalada from SINIESTROS,COMPANIAS,VEHICULOS,LOCALES,TALLERES 
where SINIESTROS.id_compania = COMPANIAS.id_compania and SINIESTROS.id_vehiculo = VEHICULOS.id_vehiculo and VEHICULOS.id_estado <> 12 and
VEHICULOS.id_local = LOCALES.id_local and LOCALES.id_taller = TALLERES.id_taller and TALLERES.id_taller = '||idTaller||' and COMPANIAS.alias <> upper(''bci'')
group by locales.capacidad_instalada';  
RETURN QUERY EXECUTE use_sql;  
END;
$BODY$
LANGUAGE plpgsql;
 
SELECT * FROM prueba (5,'bci');
 
 
VehiculosTaller           CapacidadTaller
---------------------    ---------------------------
            2                             30
            1                             30

huesos52 · #13 (**permalink**) 09/08/2010, 14:52

Para que las comillas que envulven el alias? No sería igual que con idTaller?

death_nemesis · #14 (**permalink**) 09/08/2010, 15:03

mira lo dejo como en el idTaller y me sale este error...ya me di cuenta que el error esta en la concatenacion del string pero no cacho el por que esta mal concatenado

Código SQL:

Ver originalERROR:  COLUMN "alias" does NOT exist
LINE 4: COMPANIAS.alias = UPPER('||alias||') GROUP BY locales.capaci...

y cuando concateno asi toma el alias y no el valor del alias

Código SQL:

Ver originalSELECT * FROM prueba (7,'xxx');
UPPER('||'alias'||') 
......COMPANIAS.alias = UPPER(alias).......

y deberia ser asi

Código SQL:

Ver originalSELECT * FROM prueba (7,'xxx');
......COMPANIAS.alias = UPPER(xxx).......

death_nemesis · #15 (**permalink**) 10/08/2010, 11:22

ya mira esto quedo así

Código SQL:

Ver originalCREATE OR REPLACE FUNCTION prueba(idTaller INTEGER, aliasCompania text,OUT VehiculosTaller BIGINT,OUT CapacidadTaller SMALLINT) RETURNS SETOF RECORD AS $BODY$
DECLARE
    use_sql TEXT;
BEGIN  
use_sql := 'select count(VEHICULOS.id_vehiculo)as VehiculosTaller,locales.capacidad_instalada as CapacidadTaller from SINIESTROS,COMPANIAS,VEHICULOS,LOCALES,TALLERES
where SINIESTROS.id_compania = COMPANIAS.id_compania and SINIESTROS.id_vehiculo = VEHICULOS.id_vehiculo and VEHICULOS.id_estado <> 12 and 
VEHICULOS.id_local   = LOCALES.id_local and LOCALES.id_taller = TALLERES.id_taller and TALLERES.id_taller = '||idTaller||' and 
COMPANIAS.alias = upper('||aliasCompania||') group by locales.capacidad_instalada UNION ALL 
select count(VEHICULOS.id_vehiculo),locales.capacidad_instalada from SINIESTROS,COMPANIAS,VEHICULOS,LOCALES,TALLERES 
where SINIESTROS.id_compania = COMPANIAS.id_compania and SINIESTROS.id_vehiculo = VEHICULOS.id_vehiculo and VEHICULOS.id_estado <> 12 and
VEHICULOS.id_local = LOCALES.id_local and LOCALES.id_taller = TALLERES.id_taller and TALLERES.id_taller = '||idTaller||' and COMPANIAS.alias <> upper('||aliasCompania||') 
group by locales.capacidad_instalada'; 
raise notice 'Running query: %', use_sql;
RETURN QUERY EXECUTE use_sql;  
END;
$BODY$
LANGUAGE plpgsql;
 
SELECT * FROM prueba (7,'xxx');

pero me da un error me sale lo siguiente

ERROR: column "xxx" does not exist
LINE 4: COMPANIAS.alias = upper(xxx)

siendo que el xxx es un valor de un alias que existe en la bd y no es una columna entonces no se por que me da ese error

huesos52 · #16 (**permalink**) 10/08/2010, 12:16

Prueba así

Código SQL:

Ver originalAND COMPANIAS.alias <> UPPER('''||aliasCompania||''')

death_nemesis · #17 (**permalink**) 10/08/2010, 12:29

gracias huesos52 me fue de gran ayuda tu orientacion...y en gratitud posteo el codigo completo y depurado por si a alguien lo puede necesitar

Código SQL:

Ver originalCREATE OR REPLACE FUNCTION prueba(idTaller INTEGER, aliasCompania text,OUT VehiculosTaller BIGINT,OUT CapacidadTaller SMALLINT) RETURNS SETOF RECORD AS $BODY$
DECLARE
    use_sql TEXT;
BEGIN  
use_sql := 'select count(VEHICULOS.id_vehiculo)as VehiculosTaller,locales.capacidad_instalada as CapacidadTaller from SINIESTROS,COMPANIAS,VEHICULOS,LOCALES,TALLERES
where SINIESTROS.id_compania = COMPANIAS.id_compania and SINIESTROS.id_vehiculo = VEHICULOS.id_vehiculo and VEHICULOS.id_estado <> 12 and 
VEHICULOS.id_local   = LOCALES.id_local and LOCALES.id_taller = TALLERES.id_taller and TALLERES.id_taller = '||idTaller||' and 
COMPANIAS.alias = upper('''||aliasCompania||''') group by locales.capacidad_instalada UNION ALL 
select count(VEHICULOS.id_vehiculo),locales.capacidad_instalada from SINIESTROS,COMPANIAS,VEHICULOS,LOCALES,TALLERES 
where SINIESTROS.id_compania = COMPANIAS.id_compania and SINIESTROS.id_vehiculo = VEHICULOS.id_vehiculo and VEHICULOS.id_estado <> 12 and
VEHICULOS.id_local = LOCALES.id_local and LOCALES.id_taller = TALLERES.id_taller and TALLERES.id_taller = '||idTaller||' and COMPANIAS.alias <> upper('''||aliasCompania||''') 
group by locales.capacidad_instalada'; 
--raise notice 'Running query: %', use_sql;
RETURN QUERY EXECUTE use_sql;  
END;
$BODY$
LANGUAGE plpgsql;
 
SELECT * FROM prueba (6,'xxx');