buscador en postgres

[omar125]

Hola de antemano gracias por su ayuda.
Tengo un procedimiento almacenado en MYSQL que me sirve para buscar datos. Este lo quiero pasar a POSTGRES pero en funciones.

• Este es el procedimiento en MSQL:
CREATE PROCEDURE `USP_Buscador` (op tinyint, Cad1 varchar(100),Cad2 varchar(100),Cad3 varchar(100),
Cad4 varchar(150))
BEGIN
DECLARE consultar varchar(500);
Case op
When 1 then
SET @consultar=CONCAT('SELECT * FROM ',Cad1,' WHERE Estado=0 AND ',Cad2,' LIKE \'%',Cad3,'%\' LIMIT 200');
End case;
PREPARE consultar FROM @consultar;
EXECUTE consultar;
End

• Ahora lo implemento en Postgres así, pero no se que poner en el return:
CREATE OR REPLACE FUNCTION USP_Buscador(op integer, Cad1 varchar(100),Cad2 varchar(100),Cad3 varchar(100),Cad4 varchar(150))

RETURNS Buscador AS
$BODY$
DECLARE consultar varchar(500);
BEGIN
case op
when 1 then
SET @consultar=CONCAT('SELECT * FROM ',Cad1,' WHERE Estado=0 AND ',Cad2,' ILIKE \'%',Cad3,'%\' LIMIT 200');

end case;
PREPARE consultar FROM @consultar;
EXECUTE consultar;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100
ROWS 1000;
ALTER FUNCTION USP_Buscador(integer, varchar(100), varchar(100), varchar(100), varchar(150)) OWNER TO postgres;

• Si ejecuto así en postgres me da un mensaje:

ERROR: no existe el tipo «buscador»

********** Error **********

[huesos52]

Creaste un tipo de dato llamado buscador?

[kitocnc]

queres que el resultado te devuelva de alguna forma si es asi usa record sino asi solo pone void osea vacio y ya esta...

[kitocnc]

CREATE OR REPLACE FUNCTION USP_Buscador(op integer, Cad1 varchar(100),Cad2 varchar(100),Cad3 varchar(100),Cad4 varchar(150))

RETURNS void AS -- asi tenes que poner si queres que no devuelva nada
$BODY$
DECLARE consultar varchar(500);
BEGIN
case op
when 1 then
SET @consultar=CONCAT('SELECT * FROM ',Cad1,' WHERE Estado=0 AND ',Cad2,' ILIKE \'%',Cad3,'%\' LIMIT 200');

end case;
PREPARE consultar FROM @consultar;
EXECUTE consultar;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100
ROWS 1000;
ALTER FUNCTION USP_Buscador(integer, varchar(100), varchar(100), varchar(100), varchar(150)) OWNER TO postgres;

[omar125]

Bueno gracias, por responder. Con respecto a la respuesta de huesos52: Soy nuevo en postgres y no se a que te refieres con
Crear un tipo de dato llamado buscador. Desearía que me lo expliques por favor.

Con respecto a kitocnc si coloco “RETURNS void AS” cuando compilo la función de un mensaje:

ERROR: ROWS no es aplicable cuando una función no retorna un conjunto

********** Error **********


CREATE OR REPLACE FUNCTION USP_Buscador(op integer, Cad1 varchar(100),Cad2 varchar(100),Cad3 varchar(100),Cad4 varchar(150))

RETURNS void AS -- asi tenes que poner si queres que no devuelva nada
$BODY$
DECLARE consultar varchar(500);
BEGIN
case op
when 1 then
SET @consultar=CONCAT('SELECT * FROM ',Cad1,' WHERE Estado=0 AND ',Cad2,' ILIKE \'%',Cad3,'%\' LIMIT 200');

end case;
PREPARE consultar FROM @consultar;
EXECUTE consultar;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100
ROWS 1000;
ALTER FUNCTION USP_Buscador(integer, varchar(100), varchar(100), varchar(100), varchar(150)) OWNER TO postgres;

Gracias por su valiosa ayuda.

[huesos52]

CREATE OR REPLACE FUNCTION USP_Buscador(op integer, Cad1 varchar(100),Cad2 varchar(100),Cad3 varchar(100),Cad4 varchar(150))

RETURNS Buscador AS
$BODY$
DECLARE consultar varchar(500);
BEGIN
case op
when 1 then
SET @consultar=CONCAT('SELECT * FROM ',Cad1,' WHERE Estado=0 AND ',Cad2,' ILIKE \'%',Cad3,'%\' LIMIT 200');

end case;
PREPARE consultar FROM @consultar;
EXECUTE consultar;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100
ROWS 1000;
ALTER FUNCTION USP_Buscador(integer, varchar(100), varchar(100), varchar(100), varchar(150)) OWNER TO postgres;

• Si ejecuto así en postgres me da un mensaje:

ERROR: no existe el tipo «buscador»

********** Error **********

omar125.

Como ves en la función que pones, dices que el el returns devuelva buscador. En una función de postgresql se deben retornar tipos de datos existentes. Los predefinidos en postgresql son varchar,integer,date,timestamp,bytea y unos cuantos mas. Sin embargo, postgresql tiene la capacidad de crear nuestros propios tipos de datos. Cuando retornas buscador, asumí que tal vez habias creado un type llamado buscador y que no lo estaba reconociendo.

En que te basas para poner la palabra buscador en el returns? donde lo viste? algun link?

[omar125]

Mira en las anteriores funciones que he creado el returns devuelva un nombre de una tabla
que antes ha sido declarada osea asi por ejemplo:

CREATE OR REPLACE FUNCTION usp_tipoasociado()
RETURNS tipoasociado AS
$BODY$
DECLARE
retorno tipoasociado;
BEGIN
SELECT * INTO retorno FROM tipoasociado where(Id_TipoAsociado=1);
RETURN retorno;

end;$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;

El RETURNS tipoasociado es el nombre de una tabla que como ves se declara
DECLARE retorno tipoasociado;
Ahora no se porque se trabaja asi, o es que tendria que crear una tabla Buscador ?
-En Mysql lo trabajo como procedimeinto y funciona bien.
Y aqui como lo hago?.

[huesos52]

omar125

creo que estas bastante enredado tratando de comparar entre mysql y postgresql. Yo trabajo en ambos motores y nunca he visto que una función en mysql retorne un procedimiento. Eso no tiene sentido.

Me imagino que en mysql cuando corres el procedimiento te ejecuta esta consulta y te la muestra.
SELECT * FROM ',Cad1,' WHERE Estado=0 AND ',Cad2,' ILIKE \'%',Cad3,'%\' LIMIT 200

Estoy mal?


Si eso es lo que quieres lograr en postgresql te recomiendo veas la sentencia RETURN QUERY.
aca hay un ejemplo detallado donde el autor responde a varias preguntas de como implementar el retorno de una consulta como tal.

Si trabajas en una versión superior a la 8.3.3 esto se hará mas facil. Si trabajas en una inferior, también hay un ejemplo de como hacerlo y que el resultado sea el mismo.

Por lo que pones en tu primer post, me da la impresión que copias y pegas el codigo de mysql a postgresql y tratas de correrlo. En cuanto a desarrollo de funciones, procedimientos, triggers y estas cosas ambos motores son muy pero muy diferentes.

saludos

[omar125]

En realidad tienes razon, en mysql lo implemento mediante procedimientos almacenados pero en postgres no existen procedimientos almacenados sino funciones por eso es que lo ago mediante funciones , en cuanto a:

SET @consultar=CONCAT('SELECT * FROM ',Cad1,' WHERE Estado=0 AND ',Cad2,' ILIKE \'%',Cad3,'%\' LIMIT 200');
se me olvido cambiarlo para concatenar y seria asi sino estoy mal:

SET @consultar=('SELECT * FROM ' || Cad1|| ' WHERE Estado=0 AND ' || Cad2 || ' ILIKE \'%' || Cad3 || '%\' LIMIT 200');
ó estoy mal?

-Es por eso mi confucion con las funciones

[huesos52]

En postgresql la sentencia SET ni la @ tienen validez en su analizador sintactico.
Yo creo que es mejor hacer la pregunta de exactamente que es lo que quieres hacer sin tener en cuenta lo que haces con myswl. No respondiste a mi pregunta cuando digo si lo que requieres es ejecutar una función y que se retorne el resultado de una consulta.

saludos omar

[omar125]

Exacto requiero ejecutar la función y que retorne el resultado de una consulta que son mostrados en una tabla.
Mira lo que quiero hacer es que @consultar sea igual a esa concatenacion, cad1, cad2 y cad3 son valores que ingreso.

Ahora si set No funciona que se usa o como lo haria?. Saludos.

[huesos52]

ya viste el link que te dejé?

ahi dicen como hacerlo....

Hagamos algo... Con el link que te dí, construye tu función. Si tienes problemas con esa, posteas los problemas que te surjan y te ayudamos a darle solución.

saludos

[omar125]

Hola. En el link que pusiste, hay dos ejemplos con diferentes versione de postgres, yo tengo el 8.4. pero me doy cuenta que en:
create or replace function f1()
returns setof prueba as
$$
begin
return query select * from prueba;
end;
$$
language 'plpgsql';
Hace menciona a la tabla prueba