Foros del Web » Programación para mayores de 30 ;) » Bases de Datos General » PostgreSQL »

plpgsql: filtrar una consulta según otra consulta

Estas en el tema de plpgsql: filtrar una consulta según otra consulta en el foro de PostgreSQL en Foros del Web. Saludos Estoy tratando de hacer una consulta y dependiendo de su resultado, hacer una u otra (segunda) consulta... todo dentro de una función, tal como: ...
  #1 (permalink)  
Antiguo 29/03/2011, 13:23
 
Fecha de Ingreso: agosto-2005
Ubicación: Mérida, Venezuela
Mensajes: 732
Antigüedad: 19 años, 2 meses
Puntos: 7
plpgsql: filtrar una consulta según otra consulta

Saludos

Estoy tratando de hacer una consulta y dependiendo de su resultado, hacer una u otra (segunda) consulta... todo dentro de una función, tal como:

Código:
CREATE OR REPLACE FUNCTION consultar_menu(id_usuario int) RETURNS SETOF menu_permisos AS $$
 DECLARE
   e boolean;
   r menu_permisos%rowtype;
 BEGIN
   e := $S$ SELECT * FROM cc1.usuarios WHERE id = $1$S$;
   IF e.primerizo = true THEN
     RETURN QUERY SELECT * FROM menu_permisos WHERE id_usuario = $1 AND menu.id < 10;
   ELSE
     RETURN QUERY SELECT * FROM menu_permisos WHERE id_usuario = $1;
   END IF;
 END;
$$ LANGUAGE plpgsql;
Pero... seguro, no es así... de entrada me da error de sintaxis:
Código:
ERROR:  la sintaxis de entrada no es válida para tipo boolean: « SELECT * FROM cc1.usuarios WHERE id = $1»
CONTEXT:  PL/pgSQL function "consultar_menu" line 6 at assignment
__________________
Gracias de todas todas
-----
Linux!
  #2 (permalink)  
Antiguo 29/03/2011, 14:11
Avatar de huesos52
Colaborador
 
Fecha de Ingreso: febrero-2009
Ubicación: Manizales - Colombia
Mensajes: 5.980
Antigüedad: 15 años, 8 meses
Puntos: 360
Respuesta: plpgsql: filtrar una consulta según otra consulta

Hola cacr

primerizo es un campo de la tabla cc1.usuarios? es de tipo bolean?

si es así haz esto:
Código SQL:
Ver original
  1. SELECT primerizo INTO e FROM cc1.usuarios WHERE id = $1;

Despues de eso, ya puedes ejecutar la consulta que corresponda.

saludos
__________________
Without data, You are another person with an opinion.
W. Edwads Deming
  #3 (permalink)  
Antiguo 29/03/2011, 14:51
 
Fecha de Ingreso: agosto-2005
Ubicación: Mérida, Venezuela
Mensajes: 732
Antigüedad: 19 años, 2 meses
Puntos: 7
Respuesta: plpgsql: filtrar una consulta según otra consulta

Hola huesos52

Quedando de esta forma:
Código:
CREATE OR REPLACE FUNCTION consultar_menu(id_usuario int) RETURNS SETOF menu_permisos AS $$
 DECLARE
   e boolean;
   r menu_permisos%rowtype;
 BEGIN
   SELECT primerizo INTO e FROM cc1.usuarios WHERE id = $1;
   IF primerizo = true THEN
     RETURN QUERY SELECT * FROM menu_permisos WHERE id_usuario = $1 AND menu.id < 10;
   ELSE
     RETURN QUERY SELECT * FROM menu_permisos WHERE id_usuario = $1;
   END IF;
 END;
$$ LANGUAGE plpgsql;
Obtengo:
Código:
SELECT * FROM consultar_menu(10);ERROR:  no existe la columna «primerizo»
LINE 1: SELECT  primerizo = true
                ^
QUERY:  SELECT  primerizo = true
CONTEXT:  PL/pgSQL function "consultar_menu" line 6 at IF
Sí el IF lo cambio por:
Código:
IF e.primerizo = true THEN
Obtengo:
Código:
SELECT * FROM consultar_menu(10);ERROR:  falta una entrada para la tabla «e» en la cláusula FROM
LINE 1: SELECT  e.primerizo = true
                ^
QUERY:  SELECT  e.primerizo = true
CONTEXT:  PL/pgSQL function "consultar_menu" line 6 at IF
__________________
Gracias de todas todas
-----
Linux!
  #4 (permalink)  
Antiguo 29/03/2011, 14:56
Avatar de huesos52
Colaborador
 
Fecha de Ingreso: febrero-2009
Ubicación: Manizales - Colombia
Mensajes: 5.980
Antigüedad: 15 años, 8 meses
Puntos: 360
Respuesta: plpgsql: filtrar una consulta según otra consulta

prueba así:

Código SQL:
Ver original
  1. SELECT INTO e primerizo FROM cc1.usuarios WHERE id = $1;

y la validación sería:
IF e.primerizo = TRUE THEN
__________________
Without data, You are another person with an opinion.
W. Edwads Deming
  #5 (permalink)  
Antiguo 29/03/2011, 14:58
Avatar de huesos52
Colaborador
 
Fecha de Ingreso: febrero-2009
Ubicación: Manizales - Colombia
Mensajes: 5.980
Antigüedad: 15 años, 8 meses
Puntos: 360
Respuesta: plpgsql: filtrar una consulta según otra consulta

Se me olvidaba...

e declaralo como record no como boolean
__________________
Without data, You are another person with an opinion.
W. Edwads Deming
  #6 (permalink)  
Antiguo 29/03/2011, 14:59
 
Fecha de Ingreso: agosto-2005
Ubicación: Mérida, Venezuela
Mensajes: 732
Antigüedad: 19 años, 2 meses
Puntos: 7
Respuesta: plpgsql: filtrar una consulta según otra consulta

Sorry, cambió el mensaje, deja y reviso
__________________
Gracias de todas todas
-----
Linux!
  #7 (permalink)  
Antiguo 29/03/2011, 15:05
 
Fecha de Ingreso: agosto-2005
Ubicación: Mérida, Venezuela
Mensajes: 732
Antigüedad: 19 años, 2 meses
Puntos: 7
Respuesta: plpgsql: filtrar una consulta según otra consulta

Listo! Luego me salió un error que tenía que ver con la vista... resuelto!

Gracias!
__________________
Gracias de todas todas
-----
Linux!
  #8 (permalink)  
Antiguo 29/03/2011, 15:29
 
Fecha de Ingreso: agosto-2005
Ubicación: Mérida, Venezuela
Mensajes: 732
Antigüedad: 19 años, 2 meses
Puntos: 7
Respuesta: plpgsql: filtrar una consulta según otra consulta

mmm, corrijo...

entra en el IF que corresponde, pero no toma en cuenta la primera parte del WHERE

Eso me parece muy raro...
__________________
Gracias de todas todas
-----
Linux!
  #9 (permalink)  
Antiguo 29/03/2011, 15:37
Avatar de huesos52
Colaborador
 
Fecha de Ingreso: febrero-2009
Ubicación: Manizales - Colombia
Mensajes: 5.980
Antigüedad: 15 años, 8 meses
Puntos: 360
Respuesta: plpgsql: filtrar una consulta según otra consulta

Cita:
RETURN QUERY SELECT * FROM menu_permisos WHERE id_usuario = $1 AND menu.id < 10;
esta?


que es menu.id?
__________________
Without data, You are another person with an opinion.
W. Edwads Deming
  #10 (permalink)  
Antiguo 29/03/2011, 15:45
 
Fecha de Ingreso: agosto-2005
Ubicación: Mérida, Venezuela
Mensajes: 732
Antigüedad: 19 años, 2 meses
Puntos: 7
Respuesta: plpgsql: filtrar una consulta según otra consulta

esa, pero es en el id_usuario = $1 al que le pasa por encima...

menu.id es sólo el identificador de la tabla menu, que por cierto, lo cambié, tanto en la vista como en la función a: menu.id AS id_menu

sí ejecuto la consuta: SELECT * FROM menu_permisos WHERE id_usuario = 10 AND id_menu < 10; directa, sí me funciona...
__________________
Gracias de todas todas
-----
Linux!
  #11 (permalink)  
Antiguo 29/03/2011, 15:46
 
Fecha de Ingreso: agosto-2005
Ubicación: Mérida, Venezuela
Mensajes: 732
Antigüedad: 19 años, 2 meses
Puntos: 7
Respuesta: plpgsql: filtrar una consulta según otra consulta

el 10 que le estoy pasando al id_usuario no se corresponde con el id_menu, es sólo coincidencia
__________________
Gracias de todas todas
-----
Linux!
  #12 (permalink)  
Antiguo 29/03/2011, 15:50
Avatar de huesos52
Colaborador
 
Fecha de Ingreso: febrero-2009
Ubicación: Manizales - Colombia
Mensajes: 5.980
Antigüedad: 15 años, 8 meses
Puntos: 360
Respuesta: plpgsql: filtrar una consulta según otra consulta

Entiendo.

Prueba así:

Código SQL:
Ver original
  1. RETURN QUERY EXECUTE 'SELECT * FROM menu_permisos WHERE id_usuario = ' || $1  || ' AND ID_MENU < 10';
__________________
Without data, You are another person with an opinion.
W. Edwads Deming
  #13 (permalink)  
Antiguo 29/03/2011, 16:01
 
Fecha de Ingreso: agosto-2005
Ubicación: Mérida, Venezuela
Mensajes: 732
Antigüedad: 19 años, 2 meses
Puntos: 7
Respuesta: plpgsql: filtrar una consulta según otra consulta

al tratar de crear:
ERROR: error de sintaxis en o cerca de «'SELECT * FROM menu_permisos WHERE id_usuario = '»
LINE 1: EXECUTE 'SELECT * FROM menu_permisos WHERE id_usuario = ' |...
^
QUERY: EXECUTE 'SELECT * FROM menu_permisos WHERE id_usuario = ' || $1 || ' AND ID_MENU < 10'
CONTEXT: SQL statement in PL/PgSQL function "consultar_menu" near line 8
__________________
Gracias de todas todas
-----
Linux!
  #14 (permalink)  
Antiguo 29/03/2011, 16:19
Avatar de huesos52
Colaborador
 
Fecha de Ingreso: febrero-2009
Ubicación: Manizales - Colombia
Mensajes: 5.980
Antigüedad: 15 años, 8 meses
Puntos: 360
Respuesta: plpgsql: filtrar una consulta según otra consulta

crea una variable text.

sql := 'SELECT * FROM menu_permisos WHERE id_usuario = ' || $1 || ' AND ID_MENU < 10';

en una funcion aparte verifica que esté bien creada y concatenada.

Después haces RETURN QUERY EXECUTE sql;
__________________
Without data, You are another person with an opinion.
W. Edwads Deming
  #15 (permalink)  
Antiguo 30/03/2011, 08:22
 
Fecha de Ingreso: agosto-2005
Ubicación: Mérida, Venezuela
Mensajes: 732
Antigüedad: 19 años, 2 meses
Puntos: 7
Respuesta: plpgsql: filtrar una consulta según otra consulta

Buen día jóvenes!

Código:
pruebas=# CREATE OR REPLACE FUNCTION paso(idu integer) RETURNS SETOF menu_permisos AS $$
pruebas$#  DECLARE
pruebas$#   a text;
pruebas$#  BEGIN
pruebas$#   a := 'SELECT * FROM menu_permisos WHERE id_usuario = ' || $1 || ' AND ID_MENU < 10';
pruebas$#   RETURN QUERY EXECUTE a;
pruebas$# END;
pruebas$# $$ LANGUAGE plpgsql;
ERROR:  error de sintaxis en o cerca de «$1»
LINE 1:  EXECUTE  $1 
                  ^
QUERY:   EXECUTE  $1 
CONTEXT:  SQL statement in PL/PgSQL function "paso" near line 5
__________________
Gracias de todas todas
-----
Linux!
  #16 (permalink)  
Antiguo 30/03/2011, 10:01
Avatar de huesos52
Colaborador
 
Fecha de Ingreso: febrero-2009
Ubicación: Manizales - Colombia
Mensajes: 5.980
Antigüedad: 15 años, 8 meses
Puntos: 360
Respuesta: plpgsql: filtrar una consulta según otra consulta

A mi me funciona


Código SQL:
Ver original
  1. cenicafe=> CREATE OR REPLACE FUNCTION consultar_menu(id_usuario INT) RETURNS SET
  2. OF usuarios AS $$
  3. cenicafe$>  DECLARE
  4. cenicafe$>
  5. cenicafe$>   SQL text;
  6. cenicafe$>  BEGIN
  7. cenicafe$>    SQL := 'SELECT * FROM usuarios WHERE cod_usuario = ' || $1  || ' A
  8. ND 1=1';
  9. cenicafe$>    RETURN query EXECUTE SQL;
  10. cenicafe$>  END;
  11. cenicafe$> $$ LANGUAGE plpgsql;
  12. CREATE FUNCTION
  13. cenicafe=> SELECT *FROM consultar_menu(1);
  14.  cod_usuario | log_usuario |           pas_usuario            | est_usuario |
  15.    mai_usuario      |  emp_usuario  | tip_usuario | fec_cad_usuario
  16. -------------+-------------+----------------------------------+-------------+---
  17. --------------------+---------------+-------------+-----------------
  18.            1 | skcc        | 859f7ec647abe0810a61a2eb5d28ab63 | Activado    | sk
  19. cc@smurfitkappa.com | smurfit Kappa |           2 | 2012-12-31
  20. (1 fila)
__________________
Without data, You are another person with an opinion.
W. Edwads Deming
  #17 (permalink)  
Antiguo 30/03/2011, 13:33
 
Fecha de Ingreso: agosto-2005
Ubicación: Mérida, Venezuela
Mensajes: 732
Antigüedad: 19 años, 2 meses
Puntos: 7
Respuesta: plpgsql: filtrar una consulta según otra consulta

A mi también me funciona, pero, cuando entra en el ELSE... deja y pruebo cambiando algo en el IF a ver qué cosa
__________________
Gracias de todas todas
-----
Linux!
  #18 (permalink)  
Antiguo 30/03/2011, 13:41
 
Fecha de Ingreso: agosto-2005
Ubicación: Mérida, Venezuela
Mensajes: 732
Antigüedad: 19 años, 2 meses
Puntos: 7
Respuesta: plpgsql: filtrar una consulta según otra consulta

Me declaro mentiroso... no me funciona sigo revisando, miéntras... sigo pero alternando con otro "cosito"
__________________
Gracias de todas todas
-----
Linux!
  #19 (permalink)  
Antiguo 30/03/2011, 13:54
Avatar de huesos52
Colaborador
 
Fecha de Ingreso: febrero-2009
Ubicación: Manizales - Colombia
Mensajes: 5.980
Antigüedad: 15 años, 8 meses
Puntos: 360
Respuesta: plpgsql: filtrar una consulta según otra consulta

raro raro...

hazlo así:

Código SQL:
Ver original
  1. CREATE OR REPLACE FUNCTION consultar_menu(id_usuario INT) RETURNS SETOF menu_permisos AS $$
  2.  DECLARE
  3.    e record;
  4.    SQL text;
  5.  BEGIN
  6.  SELECT INTO e primerizo FROM cc1.usuarios WHERE id = $1;
  7.   IF e.primerizo = TRUE THEN
  8.      SQL := 'SELECT *FROM menu_permisos WHERE id_usuario = ' || $1  || ' AND id_menu < 10';
  9.    ELSE
  10.      SQL := 'SELECT * FROM menu_permisos WHERE id_usuario = ' || $1;
  11.    END IF;
  12.    RETURN QUERY EXECUTE SQL;
  13.  END;
  14. $$ LANGUAGE plpgsql;
__________________
Without data, You are another person with an opinion.
W. Edwads Deming
  #20 (permalink)  
Antiguo 06/04/2011, 10:40
 
Fecha de Ingreso: noviembre-2010
Mensajes: 30
Antigüedad: 14 años
Puntos: 0
Respuesta: plpgsql: filtrar una consulta según otra consulta

Hola, veo que habéis tratado con el statement "RETURN QUERY EXECUTE", y yo estoy en un punto similar. Posteo aquí mi duda por si podeis echarme un cable, pero si no es apropiado y considerais que debo abrir otro post lo subsano de inmediato.

La cuestión es que gracias a vuestros avances he conseguido llegar al punto de tener la siguiente función.

Código:
CREATE OR REPLACE FUNCTION trastea(text) RETURNS SETOF temp AS
$BODY$
/*para hacer pruebas*/
DECLARE
	e record;
	sql text;
BEGIN
        sql := 'SELECT ST_SRID('|| $1||'.geom) FROM work.'|| $1||' WHERE gid = 1';
RETURN QUERY EXECUTE sql;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE STRICT
COST 100
ROWS 1000;
ALTER FUNCTION trastea(text) OWNER TO postgres;
que me permite comprobar que el SRID es el correcto (el SRID es un concepto cartográfico, y la función ST_SRID es una función de Postgis). temp es una tabla que creo expresamente en mi bbdd con un solo campo de tipo integer donde poder "volcar" la query.

Mi pregunta es si conoceis algún modo de poder volcar la query directamente sobre una variable declarada como integer.

Os comento también que esta función es ejecutada dentro de la estructura if de una segunda función; y claro, al no manejarme muy bien con el los tipos de datos me está saltando error.

¿Alguna idea para solucionar el problema?

Muchas gracias a ambos por la ayuda. Por la que me podáis prestar y por la ya prestada.
  #21 (permalink)  
Antiguo 06/04/2011, 11:12
Avatar de huesos52
Colaborador
 
Fecha de Ingreso: febrero-2009
Ubicación: Manizales - Colombia
Mensajes: 5.980
Antigüedad: 15 años, 8 meses
Puntos: 360
Respuesta: plpgsql: filtrar una consulta según otra consulta

Interesante.

lo que puedes probar es haciendo un cursor explicito. algo como
Código SQL:
Ver original
  1. CREATE OR REPLACE FUNCTION trastea(text) RETURNS INTEGER AS
  2. $BODY$
  3. /*para hacer pruebas*/
  4. DECLARE
  5.     e INTEGER;
  6.     SQL text;
  7.         CUR cursor;
  8. BEGIN
  9.         SQL := 'SELECT ST_SRID('|| $1||'.geom) FROM work.'|| $1||' WHERE gid = 1';
  10.         OPEN cur FOR EXECUTE SQL;
  11.         loop
  12.         fetch cur INTO e; --Esta variable se sobvreescribe en cada ciclo.
  13.         EXIT WHEN NOT FOUND;
  14.    
  15. RETURN e;
  16. END;
  17. $BODY$
  18. LANGUAGE 'plpgsql' VOLATILE STRICT
  19. COST 100
  20. ROWS 1000;
  21. ALTER FUNCTION trastea(text) OWNER TO postgres;

pero solo funciona si tienes un solo registro en el retorno de la conssulta.

no lo he probado
__________________
Without data, You are another person with an opinion.
W. Edwads Deming
  #22 (permalink)  
Antiguo 06/04/2011, 11:28
 
Fecha de Ingreso: noviembre-2010
Mensajes: 30
Antigüedad: 14 años
Puntos: 0
Respuesta: plpgsql: filtrar una consulta según otra consulta

Muchas gracias huesos52, me pongo con ello y a ver que averiguo. Ya os tengo informados.
  #23 (permalink)  
Antiguo 13/04/2011, 11:55
 
Fecha de Ingreso: noviembre-2010
Mensajes: 30
Antigüedad: 14 años
Puntos: 0
Respuesta: plpgsql: filtrar una consulta según otra consulta

Buenas a todos, ¿que tal huesos52?. Ya he conseguido que 'ande', pero le quedan retoques. Ahí va.

Código SQL:
Ver original
  1. CREATE OR REPLACE FUNCTION trastea1(text) RETURNS INTEGER AS
  2. $BODY$
  3. /*para hacer pruebas*/
  4. DECLARE
  5.     e INTEGER;
  6.     SQL text;
  7.     cur refcursor;
  8. BEGIN
  9.         SQL := 'SELECT ST_SRID('|| $1||'.geom) FROM work.'|| $1||'';
  10.         OPEN cur FOR EXECUTE SQL;
  11.         loop
  12.         fetch cur INTO e;
  13.         IF NOT found THEN
  14.             close cur;
  15.             EXIT;
  16.         END IF;
  17.         RETURN e;
  18.         END loop;
  19. END;
  20. $BODY$
  21. LANGUAGE 'plpgsql' VOLATILE STRICT;
  22. ALTER FUNCTION trastea1(text) OWNER TO postgres;

Lógicamente, tal como está planteado el código devuelve siempre el primer registro y sobra el loop. Pero resulta que si saco 'RETURN e;' fuera del loop, me devuelve null (lógico). Supongo que se solucionará con un do while (todo se andará).

Mi pregunta: ¿sería posible que en la declaración de la función, el tipo de dato a devolver no esté definido?, ¿tal vez una declaración dinámica?.

Me explico: en este caso estoy comprobando el valor de un campo, dado que el campo está definido como integer, la función debe devolver un integer. Pero si quiero comprobar un campo definido como varchar, ya no me valdría. ¿alguna idea?.

Gracias a todos.
  #24 (permalink)  
Antiguo 13/04/2011, 12:23
Avatar de huesos52
Colaborador
 
Fecha de Ingreso: febrero-2009
Ubicación: Manizales - Colombia
Mensajes: 5.980
Antigüedad: 15 años, 8 meses
Puntos: 360
Respuesta: plpgsql: filtrar una consulta según otra consulta

No es posible nessmar.

se me ocurre que según el caso borres y crees nuevamente la función
__________________
Without data, You are another person with an opinion.
W. Edwads Deming

Etiquetas: plpgsql
Atención: Estás leyendo un tema que no tiene actividad desde hace más de 6 MESES, te recomendamos abrir un Nuevo tema en lugar de responder al actual.
Respuesta

SíEste tema le ha gustado a 2 personas




La zona horaria es GMT -6. Ahora son las 05:03.