SQL dinamico en funcion pgsql

[rProgged]

Estimad@s:

Estoy comenzando con esto del postgre y en estos minutos tengo la duda de si se pueden hacer consultas dinámicas en las funciones pgsql

Me explico, tengo una tabla con registros y sólo quiero obtener los registros activos, la consulta seria:

Código:

SELECT * FROM tabla WHERE activo=1

Ahora, si quisiera obtener todos los registros sin importar su estado, quedaría:

Código:

SELECT * FROM tabla

Mi pregunta es, ¿Necesariamente debo hacer dos consultas distintas o puedo generar la consulta en forma dinámica mediante parámetros?. Porque por ejemplo yo podria pasarle el estado como parametro a la función y según el estado generar la consulta.

En T-SQL se puede hacer algo así

Código:

SELECT @sql = N'SELECT * FROM tabla';
IF ( @estado=1 )
BEGIN
   SELECT @sql = @sql + ' WHERE activo=1';
END
sp_executesql @sql...;

¿Es posible hacer algo así en pgsql?

Muchas gracias por su interés y ayuda.

saludos

[huesos52]

si se puede.

Puedes utilizar esta función

Código sql:

CREATE OR REPLACE FUNCTION consultar_con_where(parametro_where text)
RETURNS SETOF tabla_prueba AS
$BODY$
DECLARE
fila tabla_prueba%rowtype;
SQL text;
BEGIN
SQL = 'select id,nombre FROM tabla_prueba ' || parametro_where;
 
 FOR fila IN EXECUTE SQL
 LOOP
 RETURN NEXT fila;
END LOOP;
RETURN;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
 

Para el caso lo hago con una tabla llamada tabla_prueba

Código sql:

pruebas=> SELECT *FROM consultar_con_where('');
 id | nombre
----+---------
  1 | daniel
  2 | sara
  3 | natalia
(3 filas)
 
pruebas=> SELECT *FROM consultar_con_where('where id=1');
 id | nombre
----+--------
  1 | daniel
(1 fila)
 
pruebas=>
 

saludos

[rProgged]

Gracias Huesos, funciona OK :)

[rProgged]

Hola de nuevo Huesos

Mira al final la función me quedó así y funciona perfecto

Código:

CREATE OR REPLACE FUNCTION BuscarAreas(estado boolean) RETURNS SETOF tbl_area_tematica AS $$
DECLARE
	datos tbl_area_tematica%rowtype;
	sql TEXT := 'SELECT area_tem_id, area_tem_nombre, area_tem_activa FROM tbl_area_tematica';
	sqlEstado TEXT;
BEGIN
	IF ( estado=TRUE ) THEN
		sql = sql || ' WHERE area_tem_activa=TRUE';
	ELSE
		sql = sql || ' WHERE area_tem_activa=FALSE';
	END IF;
	FOR datos IN EXECUTE sql LOOP
		RETURN NEXT datos;
	END LOOP;
	RETURN;
END
$$ LANGUAGE plpgsql VOLATILE;

Sin embargo estuve mirando la documentación y encontré la función EXECUTE.. INTO...USING, la intenté usar pero me funcionaba sólo al no pasar el parámetro, si le paso el parámetro no me devuelve nada. Había quedado así:

Código:

CREATE OR REPLACE FUNCTION BuscarAreas(estado boolean) RETURNS SETOF tbl_area_tematica AS $$
DECLARE
	datos tbl_area_tematica%rowtype;
	sql TEXT := 'SELECT area_tem_id, area_tem_nombre, area_tem_activa FROM tbl_area_tematica';
	sqlEstado TEXT;
BEGIN
	IF ( estado<>NULL ) THEN
		sql = sql || ' WHERE area_tem_activa=$1';
	END IF;
	EXECUTE sql INTO datos USING estado;
END
$$ LANGUAGE plpgsql VOLATILE;

Desplegué la consulta por pantalla y estaba OK, lo único que se me ocurre es que no se haya realizado bien el reemplazo del parámetro, que según entiendo es la fortaleza de usar EXECUTE

¿Algún comentario con respecto a la última función?

Saludos y muchas gracias de nuevo

Rodrigo

PD: ¿Cómo posteo el código en formato SQL? (como lo pusiste en la respuesta), gracias

[huesos52]

Has hecho un gran trabajo rprogged

COn respecto al codigo SQL, fijate en el espacio donde escribimos los mensajes un icono con el simbolo #. Si te paras sobre el, verás que dice highlight.

Si quieres postear código sql, simplemente señalas el codigo y presionas este botón. El te abre una pequeña ventana donde pones sql y listo. También sirve para código php, mysql, csharp y no se cuales mas.

saludos

[rProgged]

jaja no es nada, muy por el contrario si algo bueno he hecho en este caso te lo debo a ti

gracias por la explicación acerca del código, al final no logré que me funcionara la función con el EXECUTE INTO USING asi que termine inclinandome por la primera versión que anda perfecto

en fin, seguiré investigando

gracias de nuevo

[rProgged]

Me respondo yo mismo, en el tema del execute me faltaba el ciclo para recuperar los datos y devolverlos, finalmente queda así

Código PGSQL:

CREATE OR REPLACE FUNCTION BuscarAreas(estado boolean) RETURNS SETOF tbl_area_tematica AS $$
DECLARE
    datos tbl_area_tematica%rowtype;
    sql TEXT := 'SELECT area_tem_id, area_tem_nombre, area_tem_activa FROM tbl_area_tematica';
    sqlEstado TEXT;
BEGIN
    IF ( estado<>NULL ) THEN
        sql = sql || ' WHERE area_tem_activa=$1';
    END IF;
    FOR datos IN EXECUTE sql USING estado LOOP
               RETURN NEXT datos;
    END LOOP;
    RETURN;
END
$$ LANGUAGE plpgsql VOLATILE;
 

Por si a alguien le sirve.

Saludos