plpgsql: filtrar una consulta según otra consulta

[cacr]

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

[huesos52]

Hola cacr

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

si es así haz esto:

Código SQL:

SELECT primerizo INTO e FROM cc1.usuarios WHERE id = $1;
 

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

saludos

[cacr]

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

[huesos52]

prueba así:

Código SQL:

SELECT INTO e primerizo FROM cc1.usuarios WHERE id = $1;
 

y la validación sería:
IF e.primerizo = TRUE THEN

[huesos52]

Se me olvidaba...

e declaralo como record no como boolean

[cacr]

Sorry, cambió el mensaje, deja y reviso

[cacr]

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

Gracias!

[cacr]

mmm, corrijo...

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

Eso me parece muy raro...

[huesos52]

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

esta?


que es menu.id?

[cacr]

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...

[cacr]

el 10 que le estoy pasando al id_usuario no se corresponde con el id_menu, es sólo coincidencia

[huesos52]

Entiendo.

Prueba así:

Código SQL:

RETURN QUERY EXECUTE 'SELECT * FROM menu_permisos WHERE id_usuario = ' || $1  || ' AND ID_MENU < 10';
 

[cacr]

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

[huesos52]

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;

[cacr]

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

[huesos52]

A mi me funciona

Código SQL:

cenicafe=> CREATE OR REPLACE FUNCTION consultar_menu(id_usuario INT) RETURNS SET
OF usuarios AS $$
cenicafe$>  DECLARE
cenicafe$>
cenicafe$>   SQL text;
cenicafe$>  BEGIN
cenicafe$>    SQL := 'SELECT * FROM usuarios WHERE cod_usuario = ' || $1  || ' A
ND 1=1';
cenicafe$>    RETURN query EXECUTE SQL;
cenicafe$>  END;
cenicafe$> $$ LANGUAGE plpgsql;
CREATE FUNCTION
cenicafe=> SELECT *FROM consultar_menu(1);
 cod_usuario | log_usuario |           pas_usuario            | est_usuario |
   mai_usuario      |  emp_usuario  | tip_usuario | fec_cad_usuario
-------------+-------------+----------------------------------+-------------+---
--------------------+---------------+-------------+-----------------
           1 | skcc        | 859f7ec647abe0810a61a2eb5d28ab63 | Activado    | sk
[e-mail] | smurfit Kappa |           2 | 2012-12-31
(1 fila)
 

[cacr]

A mi también me funciona, pero, cuando entra en el ELSE... deja y pruebo cambiando algo en el IF a ver qué cosa

[cacr]

Me declaro mentiroso... no me funciona sigo revisando, miéntras... sigo pero alternando con otro "cosito"

[huesos52]

raro raro...

hazlo así:

Código SQL:

CREATE OR REPLACE FUNCTION consultar_menu(id_usuario INT) RETURNS SETOF menu_permisos AS $$
 DECLARE
   e record;
   SQL text;
 BEGIN
 SELECT INTO e primerizo FROM cc1.usuarios WHERE id = $1;
  IF e.primerizo = TRUE THEN
     SQL := 'SELECT *FROM menu_permisos WHERE id_usuario = ' || $1  || ' AND id_menu < 10';
   ELSE
     SQL := 'SELECT * FROM menu_permisos WHERE id_usuario = ' || $1;
   END IF;
   RETURN QUERY EXECUTE SQL;
 END;
$$ LANGUAGE plpgsql;
 

[nessemar]

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.

[huesos52]

Interesante.

lo que puedes probar es haciendo un cursor explicito. algo como

Código SQL:

CREATE OR REPLACE FUNCTION trastea(text) RETURNS INTEGER AS
$BODY$
/*para hacer pruebas*/
DECLARE
    e INTEGER;
    SQL text;
        CUR cursor; 
BEGIN
        SQL := 'SELECT ST_SRID('|| $1||'.geom) FROM work.'|| $1||' WHERE gid = 1';
        OPEN cur FOR EXECUTE SQL;
        loop
        fetch cur INTO e; --Esta variable se sobvreescribe en cada ciclo.
        EXIT WHEN NOT FOUND;
   
RETURN e;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE STRICT
COST 100
ROWS 1000;
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

[nessemar]

Muchas gracias huesos52, me pongo con ello y a ver que averiguo. Ya os tengo informados.

[nessemar]

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

Código SQL:

CREATE OR REPLACE FUNCTION trastea1(text) RETURNS INTEGER AS
$BODY$
/*para hacer pruebas*/
DECLARE
    e INTEGER;
    SQL text;
    cur refcursor;
BEGIN
        SQL := 'SELECT ST_SRID('|| $1||'.geom) FROM work.'|| $1||'';
        OPEN cur FOR EXECUTE SQL;
        loop
        fetch cur INTO e;
        IF NOT found THEN
            close cur;
            EXIT;
        END IF;
        RETURN e;
        END loop;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE STRICT;
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.

[huesos52]

No es posible nessmar.

se me ocurre que según el caso borres y crees nuevamente la función