for - funciones y procedimientos en postgresql

Cómo insertar datos en la tabla usando procedimientos almacenados en postgresql

Question

CREATE TABLE app_for_leave
(
sno integer NOT NULL,
eid integer,
ename varchar(20),
sd date,
ed date,
sid integer,
status boolean DEFAULT false,
CONSTRAINT pk_snoa PRIMARY KEY (sno)
);

La inserción básica es:

INSERT INTO app_for_leave(sno, eid, sd, ed, sid, status)
VALUES(1,101,''2013-04-04'',''2013-04-04'',2,''f'' );

...

INSERT INTO app_for_leave(sno, eid, sd, ed, sid, status) VALUES (?, ?, ?, ?, ?, ?);

Mi requisito: ¿Cómo insertar datos en una tabla utilizando procedimientos almacenados ?

Answer 1

A partir de PostgreSQL 11, puede crear procedimientos almacenados e invocarlos utilizando CALL :

CREATE PROCEDURE MyInsert(_sno integer, _eid integer, _sd date,
_ed date, _sid integer, _status boolean)
LANGUAGE SQL
AS $$
INSERT INTO app_for_leave(sno, eid, sd, ed, sid, status)
VALUES(_sno, _eid, _sd, _ed, _sid, _status);
$$;
CALL MyInsert(1,101,''2013-04-04'',''2013-04-04'',2,''f'' );

Además permite manejar la transacción.

Procedimientos almacenados de SQL

PostgreSQL 11 introduce procedimientos almacenados de SQL que permiten a los usuarios utilizar transacciones incrustadas (es decir, BEGIN, COMMIT / ROLLBACK) dentro de un procedimiento. Los procedimientos pueden crearse usando el comando CREAR PROCEDIMIENTO y ejecutarse usando el comando CALL.

Answer 2

PostgreSQL no admitió procedimientos almacenados hasta PG11. Antes de eso, podrías obtener el mismo resultado usando una función. Por ejemplo:

CREATE FUNCTION MyInsert(_sno integer, _eid integer, _sd date, _ed date, _sid integer, _status boolean)
RETURNS void AS
$BODY$
BEGIN
INSERT INTO app_for_leave(sno, eid, sd, ed, sid, status)
VALUES(_sno, _eid, _sd, _ed, _sid, _status);
END;
$BODY$
LANGUAGE ''plpgsql'' VOLATILE
COST 100;

Entonces puedes llamarlo así:

select * from MyInsert(1,101,''2013-04-04'',''2013-04-04'',2,''f'' );

Las principales limitaciones de las funciones almacenadas de Pg, en comparación con los verdaderos procedimientos almacenados, son:

1. incapacidad para devolver múltiples conjuntos de resultados
2. sin soporte para transacciones autónomas (BEGIN, COMMIT y ROLLBACK dentro de una función)
3. no es compatible con la sintaxis CALL estándar de SQL, aunque los controladores ODBC y JDBC traducirán las llamadas para usted.

Example

A partir de PG11, se introduced sintaxis de CREATE PROCEDURE que proporciona soporte para transacciones.

CREATE PROCEDURE MyInsert(_sno integer, _eid integer, _sd date, _ed date, _sid integer, _status boolean)
LANGUAGE SQL
AS $BODY$
INSERT INTO app_for_leave(sno, eid, sd, ed, sid, status)
VALUES(_sno, _eid, _sd, _ed, _sid, _status);
$BODY$;

Que se podría llamar con:

CALL MyInsert(1,101,''2013-04-04'',''2013-04-04'',2,''f'' );

Answer 3

PostgreSQL no admite procedimientos almacenados , pero puede obtener el mismo resultado utilizando una función.

Independientemente de los datos que desee insertar en la tabla, se proporcionan como parámetros a la función que está creando.

CREAR O REEMPLAZAR representa si una función con el mismo nombre (que está usando) ya está presente en la base de datos, luego se reemplazará o, de lo contrario, si no hay una función con el mismo nombre, entonces se creará una nueva función.

Debe escribir la consulta de inserción dentro del cuerpo de la función.

CREATE OR REPLACE FUNCTION Insert_into_table(_sno INTEGER, _eid INTEGER, _ename VARCHAR(20), _sd DATE, _ed DATE, _sid INTEGER)
RETURNS void AS
$BODY$
BEGIN
INSERT INTO app_for_leave(sno, eid, sd, ed, sid)
VALUES(_sno, _eid, _sd, _ed, _sid);
END;
$BODY$
LANGUAGE ''plpgsql'' VOLATILE
COST 100;

Como ya mencionó en la tabla un valor predeterminado para la columna Estado , ahora no es necesario insertar datos en esa columna

Aquí está el enlace de SQLFiddle para su comprensión

Answer 4

CREATE OR REPLACE FUNCTION new_bolshek(parent_id bigint, _key text, _value text, enabled boolean)
RETURNS SETOF bolshekter AS
$BODY$
DECLARE
new_id integer;
returnrec bolshekter;
BEGIN
INSERT INTO bolshekter(parent_id, content_key, content_value, enabled)
VALUES(parent_id, _key, _value, enabled) RETURNING id INTO new_id;
FOR returnrec IN SELECT * FROM bolshekter where id=new_id LOOP
RETURN NEXT returnrec;
END LOOP;
END;
$BODY$
LANGUAGE ''plpgsql'' VOLATILE
COST 100;