· 4 years ago · Jun 15, 2021, 08:30 PM
1CREATE OR REPLACE FUNCTION report(id NUMERIC(6,0), state BOOLEAN)
2RETURNS void AS $$
3 DECLARE author_record RECORD;
4 BEGIN
5 EXECUTE format('
6 CREATE TABLE IF NOT EXISTS reports(
7 id NUMERIC(6,0),
8 fio VARCHAR(50) NOT NULL,
9 pub_name VARCHAR(60) NOT NULL,
10 mag_name VARCHAR(60) NOT NULL,
11 year NUMERIC(4,0),
12 num_ed CHAR(10),
13 pages NUMERIC(4,0),
14 co_authors VARCHAR(360))');
15 IF state IS FALSE THEN
16 DELETE FROM reports;
17 END IF;
18 FOR author_record IN (SELECT publications.author, publications.name AS pub_name, magazine.name AS mag_name, mi.year, mi.number, publications.pages FROM magazine m
19 INNER JOIN mag_iss mi USING (index)
20 INNER JOIN publications p USING (id)
21 WHERE p.id = id) LOOP
22 DECLARE
23 pages NUMERIC(4,0) := num_pages(author_record.pages);
24 c_record RECORD;
25 co_authors VARCHAR(360);
26 BEGIN
27 SELECT p.author FROM publications p INTO c_record
28 WHERE p.author <> author_record.author AND p.name = author_record.pub_name;
29 SET co_authors = ISNULL(co_authors) + QUOTENAME(author, ', ')
30 FROM c_record
31 INSERT INTO reports VALUES(id, author_record.author, author_record.pub_name, author_record.mag_name,
32 author_record.year, author_record.number, pages, co_authors);
33 END;
34 END LOOP;
35 RETURN;
36END;
37$$ LANGUAGE plpgSQL;