Problema con Store Procedure en PostgreSQL

[jgabrielsinner10]

Tengo un problema al crear la siguiente función en PL/PGSQL. Este es el Script que utilizo para crearlo:

Código:

CREATE FUNCTION sch_general."doSelect"(IN tabla text) RETURNS RECORD AS
$BODY$
DECLARE
	registro RECORD%ROWTYPE;
BEGIN
	FOR registro IN EXECUTE 'select * from ' || tabla  LOOP
		RETURN NEXT registro;
	END LOOP;
	RETURN;
END;
$BODY$
LANGUAGE 'sql' VOLATILE;
ALTER FUNCTION sch_general."doSelect"(IN text) OWNER TO postgres;

Pero, me devuelve que hay un error en la linea 4 "near RECORD%ROWTYPE;" la cual contiene esto:

Código:

registro RECORD%ROWTYPE;

Estoy tratando de realizar algo parecido a un "SELECT * FROM tabla" pero, con SP y para cualquier "tabla" y traerme todos los registros de la misma.

Si Alguien me puede ayudar estaé siempre muy agradecido, de verdad es urgente.

[huesos52]

2 cosas jgabrielsinner10

1. Cuando utilizas una función con language 'sql', No es posible realizar sentencias for ni procedimientos de programación. Por eso es recomendable hacer uso de language 'plpgsql'.
2. Para retornar un record es necesario especificar los campos que se mostraran en la tabla que quieres ejecutar al momento de ejecutarla.

te dejo un ejemplo.

Código sql:

pruebas=> \d sueldos
              Tabla ½public.sueldos╗
      Columna      |     Tipo     | Modificadores
-------------------+--------------+---------------
 id                | INTEGER      |
 categoria         | text         |
 unidad_tributaria | NUMERIC(6,2) |
 
pruebas=> \d sueldos1
                     Tabla ½public.sueldos1╗
      Columna      |            Tipo             | Modificadores
-------------------+-----------------------------+---------------
 id                | INTEGER                     |
 categoria         | text                        |
 unidad_tributaria | text                        |
 id_peti           | INTEGER                     |
 fecha             | TIMESTAMP WITHOUT TIME zone | DEFAULT now()
 tipo_servicio     | text                        |
 factracion        | text                        |
 
pruebas=> CREATE OR REPLACE FUNCTION consulta(text) RETURNS setof record
pruebas-> AS
pruebas-> $body$
pruebas$> DECLARE
pruebas$> registro record;
pruebas$> SQL text;
pruebas$> BEGIN
pruebas$> SQL = 'select *from ' || $1;
pruebas$> FOR registro IN EXECUTE SQL
pruebas$> loop
pruebas$> RETURN NEXT registro;
pruebas$> END loop;
pruebas$> RETURN;
pruebas$> END;
pruebas$> $body$ LANGUAGE 'plpgsql' VOLATILE;
CREATE FUNCTION
pruebas=> SELECT *FROM consulta('sueldos') AS t(a INTEGER, b text, c NUMERIC);
 a | b |  c
---+---+------
 6 | B | 1.12
 3 | A | 1.00
 7 | C | 1.37
 6 | B | 1.12
 3 | A | 1.00
 7 | C | 1.37
(6 filas)
 
pruebas=> SELECT *FROM consulta('sueldos1') AS t(a INTEGER, b text, c text, d INTEGER, e TIMESTAMP,f text, g text);
 a | b |  c   | d  |            e            |      f       |     g
---+---+------+----+-------------------------+--------------+-----------
 6 | B | 1.12 | 20 | 2009-03-16 11:26:57.453 | sin facturar | seleccion
 3 | A | 1.00 | 20 | 2009-03-16 11:26:57.453 | sin facturar | seleccion
 7 | C | 1.37 | 20 | 2009-03-16 11:26:57.453 | sin facturar | seleccion
 6 | B | 1.12 | 20 | 2009-03-16 11:26:57.453 | sin facturar | seleccion
 3 | A | 1.00 | 20 | 2009-03-16 11:26:57.453 | sin facturar | seleccion
 7 | C | 1.37 | 20 | 2009-03-16 11:26:57.453 | sin facturar | seleccion
 8 | A | 1    | 18 | 2009-03-27 08:22:44.953 | facturado    | seleccion
(7 filas)
 

saludos

[jgabrielsinner10]

Oye full gracias por explicarme eso.

Ahora tengo otro Error

Tengo esta tabla tbl_ciudades

Código:

CREATE TABLE sch_general.tbl_ciudades
(
  ciudades_id serial NOT NULL,
  ciudades_descripcion character varying(20) NOT NULL,
  estados_id integer NOT NULL,
  CONSTRAINT tbl_ciudades_pkey PRIMARY KEY (ciudades_id),
  CONSTRAINT tbl_ciudades_estados_id_fkey FOREIGN KEY (estados_id)
      REFERENCES sch_general.tbl_estados (estados_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)

Ya creé el SP sin problemas.

Código:

CREATE OR REPLACE FUNCTION sch_general."doSelect"(tabla text)
  RETURNS SETOF record AS
$BODY$
DECLARE
	registro RECORD;
	sql TEXT;
BEGIN
	sql = 'SELECT * FROM ' || $1;
	FOR registro IN EXECUTE sql  LOOP
		RETURN NEXT registro;
	END LOOP;
	RETURN;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION sch_general."doSelect"(text) OWNER TO postgres;

Ahora cuando llamo al SP de esta forma:

Código:

SELECT * FROM sch_general."doSelect"('sch_general.tbl_ciudades') AS tbl_ciudades(ciudades_id INTEGER, ciudades_descripcion text, estados_id numeric);

Me lanza este error:

RROR: wrong record type supplied in RETURN NEXT
CONTEXTO: PL/pgSQL function "doSelect" line 7 at RETURN NEXT

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

ERROR: wrong record type supplied in RETURN NEXT
SQL state: 42804
Context: PL/pgSQL function "doSelect" line 7 at RETURN NEXT

Ah! Otra Cosa Brother.

Código:

	BEGIN
		IF ciudad_id == 0 THEN
			IF estado_id == 0 THEN
				-- Consulta General de Todos los Registros
				SELECT INTO ciudades * FROM sch_general.tbl_ciudades;
			ELSE
				-- Consulta por FK
				SELECT INTO ciudades * FROM sch_general.tbl_ciudades WHERE sch_general.tbl_ciudades.estados_id = estado_id;
			END IF;
		ELSE
			-- Consulta por PK
			SELECT INTO ciudades * FROM sch_general.tbl_ciudades WHERE sch_general.tbl_ciudades.ciudades_id = ciudad_id;
		END IF;
	END;

Me dá unos errores en las comparaciones... NO entiendo cómo hacer las comparaciones (IF) de datos en SP de Postgres.

[huesos52]

Prueba lanzandolo así:

Código sql:

SELECT * FROM sch_general."doSelect"('sch_general.tbl_ciudades') AS tbl(ciudades_id serial, ciudades_descripcion CHARACTER VARYING, estados_id INTEGER);
 

Me dá unos errores en las comparaciones... NO entiendo cómo hacer las comparaciones (IF) de datos en SP de Postgres.

Las comparaciones no son con doble = como en varios lenguajes. Basta con:

Código sql:

IF ciudad_id = 0 THEN
            IF estado_id = 0 THEN
                -- Consulta General de Todos los Registros
                SELECT INTO ciudades * FROM sch_general.tbl_ciudades;
            ELSE
                -- Consulta por FK
                SELECT INTO ciudades * FROM sch_general.tbl_ciudades WHERE sch_general.tbl_ciudades.estados_id = estado_id;
            END IF;
        ELSE
            -- Consulta por PK
            SELECT INTO ciudades * FROM sch_general.tbl_ciudades WHERE sch_general.tbl_ciudades.ciudades_id = ciudad_id;
        END IF;
 

saludos

[jgabrielsinner10]

OOOOKA Thanks Very much Brother. Me ayudaste full con esto estoy en un proyecto en el que tengo que hacer uso de SP y no he encontrado una buena documentación para esto. Muchas gracias de nuevo.

[huesos52]

Este es un gran problema de plpgsql. Con este lenguaje se pueden hacer grandes cosas, pero toda la información la he encontrado en foros como estos.

Debería haber un manual de referencia dedicado a plpgsql.

Te recomiendo si tienes nuevas dudas, postearlas en el subforo de postgresql, ya que los duros de postgres poco se pasan por este foro.

saludos