Scribd
Upload
8 views2 pages

PostgreSQL Procedure Syntax and Examples

The document provides syntax and examples for creating procedures and functions in PostgreSQL using the PL/pgSQL language. It includes examples for adding, updating, and deleting records in a table, as well as a function for adding two numbers. Each procedure demonstrates the use of SQL statements within the procedure body and how to call them afterward.

Uploaded by

Sushanth Reddy
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
8 views2 pages

PostgreSQL Procedure Syntax and Examples

The document provides syntax and examples for creating procedures and functions in PostgreSQL using the PL/pgSQL language. It includes examples for adding, updating, and deleting records in a table, as well as a function for adding two numbers. Each procedure demonstrates the use of SQL statements within the procedure body and how to call them afterward.

Uploaded by

Sushanth Reddy
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd

Postgres:

==============

proceudres

===
Syntax:

CREATE [OR REPLACE] PROCEDURE procedure_name(parameter_list)


LANGUAGE plpgsql
AS $$
DECLARE
-- Variable declarations (optional)
BEGIN
-- Procedure body (SQL statements)
END;
$$;

LANGUAGE plpgsql: Specifies the procedural language. Other languages like SQL and C
can also be used.
===

CREATE TABLE employees (


id SERIAL PRIMARY KEY,
name VARCHAR(100),
age INT
);

CREATE PROCEDURE add_employee(emp_name VARCHAR, emp_age INT)


LANGUAGE plpgsql
AS $$
BEGIN
INSERT INTO employees (name, age) VALUES (emp_name, emp_age);
END;
$$;

CALL add_employee('John Doe', 25);

===

CREATE PROCEDURE update_user_age(IN uid INT, IN new_age INT)


LANGUAGE plpgsql
AS $$
BEGIN
UPDATE users
SET age = new_age
WHERE id = uid;
END;
$$;

CALL update_employee_age(1, 30);

==
CREATE PROCEDURE delete_user(IN uid INT)
LANGUAGE plpgsql
AS $$
BEGIN
DELETE FROM users WHERE id = uid;
RAISE NOTICE 'User with ID % deleted.', uid;
END;
$$;

CALL delete_user(3);

==

CREATE FUNCTION add_numbers(a INT, b INT)


RETURNS INT
LANGUAGE plpgsql
AS $$
BEGIN
RETURN a + b;
END;
$$;

SELECT add_numbers(5, 10);

You might also like