Scribd
Upload
9 views5 pages

SQL Assignment

The document describes the creation of several PostgreSQL functions using PL/pgSQL, including 'sum_prod' for summing and multiplying integers, 'sum_div' for dividing integers with error handling, and 'ins_dept' for inserting department records into a table. It also includes a function 'emp_city' that retrieves employee names based on their city. The document provides examples of function calls and their outputs, demonstrating the functionality of the defined functions.

Uploaded by

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

SQL Assignment

The document describes the creation of several PostgreSQL functions using PL/pgSQL, including 'sum_prod' for summing and multiplying integers, 'sum_div' for dividing integers with error handling, and 'ins_dept' for inserting department records into a table. It also includes a function 'emp_city' that retrieves employee names based on their city. The document provides examples of function calls and their outputs, demonstrating the functionality of the defined functions.

Uploaded by

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

//Set A

a.

create or replace function sum_prod(in x int,in y int,out sumans int,out prodans int) as

$$

begin

if x< 2 then

raise warning 'Information message %', now();

raise notice 'Information message %', now();

raise info 'Information message %', now();

end if;

sumans:=x+y;

prodans:=x*y;

end;

$$

language plpgsql;

// ans

\i [Link]

CREATE FUNCTION

exam1=> select sum_prod(5,6);

sum_prod

----------

(11,30)

(1 row)

exam1=> select sum_prod(1,6);

WARNING: Information message 2026-01-02 13:20:06.247174+05:30

NOTICE: Information message 2026-01-02 13:20:06.247174+05:30


INFO: Information message 2026-01-02 13:20:06.247174+05:30

sum_prod

----------

(7,6)

(1 row)

2. create or replace function sum_div(in x int,in y int,out sumans int) as

$$

begin

if y= 0 then

raise exception 'y is 0 ';

end if;

sumans:=x/y;

end;

$$

language plpgsql;

//ans

select sum_div(10,0);

ERROR: y is 0

CONTEXT: PL/pgSQL function sum_div(integer,integer) line 4 at RAISE

exam1=> \i [Link]

CREATE FUNCTION

exam1=> select sum_div(10,2);

sum_div

---------

5
(1 row)

c.

create table dept(dno int primary key,dname text,empname text,city text);

create or replace function ins_dept(dn int,dnm text,en text,ct text) returns void as

$$

begin

insert into dept values(dn,dnm,en,ct);

end;

$$

language plpgsql;

//ANS

CREATE FUNCTION

exam1=> select ins_dept(1,'IT','VKJ','NASHIK');

ins_dept

----------

(1 row)

exam1=> select ins_dept(2,'IT','LHK','NASHIK');

ins_dept

----------

(1 row)
exam1=> select ins_dept(3,'QC','AHK','PUNE');

ins_dept

----------

(1 row)

exam1=> select * from dept;

dno | dname | empname | city

-----+-------+---------+--------

1 | IT | VKJ | NASHIK

2 | IT | LHK | NASHIK

3 | QC | AHK | PUNE

(3 rows)

create or replace function emp_city(ct text) returns setof record as

$$

declare

r record;

begin

for r in (select * from dept where city=ct)

loop

raise notice '% %',[Link] , [Link];

end loop;

end;

$$

language plpgsql;

CREATE FUNCTION

exam1=> select emp_city('NASHIK');


NOTICE: VKJ NASHIK

NOTICE: LHK NASHIK

emp_city

----------

(0 rows)

You might also like