LWAYy7Xx

1CREATE OR REPLACE FUNCTION "test.source".folder_select(IN p_id integer DEFAULT NULL::integer,IN p_title text DEFAULT  NULL::text,IN p_parentid integer DEFAULT  NULL::integer,IN p_datecreated timestamp with time zone DEFAULT  NULL::timestamp with time zone,IN p_no integer DEFAULT  NULL::integer,IN p_qualificationid integer DEFAULT  NULL::integer,IN p_standartid integer DEFAULT  NULL::integer,IN p_isdeleted boolean DEFAULT  NULL::boolean,IN p_typefolder integer DEFAULT  NULL::integer,IN p_unitid integer DEFAULT  NULL::integer,IN p_questiontypeid integer DEFAULT  NULL::integer,IN p_questiontitle text DEFAULT  NULL::text)
2    RETURNS TABLE(id integer, title text, parentid integer, no integer, qualificationid integer, standartid integer, isdeleted boolean, isintest boolean, typefolder integer, initialdata text)
3    LANGUAGE 'plpgsql'
4    VOLATILE
5    PARALLEL UNSAFE
6    COST 100    ROWS 1000 
7    
8AS $BODY$
9DECLARE v_parentid int;
10		                            DECLARE temprow record;
11
12                                    begin
13
14									    DROP TABLE if exists idsTable;
15                    				    CREATE TEMP TABLE idsTable (id int);
16
17                                        if (p_isdeleted is null) and (p_id is null) then
18  									        p_isdeleted = false;
19									    end if;
20
21                                        insert into idsTable (id) select main.Id 
22                                        from "test.source".Folder main where 
23                                        (p_isdeleted is null or main.IsDeleted = p_isdeleted) and
24                                        (p_Id is null or main.Id = p_Id) and
25                                        (p_Title is null or main.Title = p_Title) and
26                                        (p_ParentId is null or main.ParentId = p_ParentId) and
27                                        (p_DateCreated is null or main.DateCreated = p_DateCreated) and
28                                        (p_No is null or main.No = p_No) and
29                                        (p_QualificationId is null or main.QualificationId = p_QualificationId) and
30                                        (p_standartid is null or main.QualificationId in (select t2.id from dict.professionalqualification as t2 where t2.standartid = p_standartid)) and
31                                        --(p_sectionTheoryId is null or main.sectionTheoryId = p_sectionTheoryId) and
32                                        (p_typefolder is null or main.typefolder = p_typefolder) and
33                                        (p_unitid is null or main.qualificationid in (select t1.qualificationId from auth.unitqualification as t1 where unitId = p_unitId and t1.isdeleted = false)) and 
34                                        (p_QuestionTitle is null or  (main.id in (select q.folderid from "test.source".question as q where q.isdeleted = false and lower(q.text) like N'%' || lower(p_QuestionTitle) || '%'))) and 
35                                        (p_questionTypeId is null or(main.id in (select q1.folderid from "test.source".question as q1 where q1.isdeleted = false and q1.questiontypeid = p_questionTypeId))); 
36                               
37                                        --выстраиваем недостающие шапки папок, если надо
38                                        for temprow IN
39                                            select tt.id FROM idsTable as tt
40                                            LOOP 
41                                               select folder.parentid from "test.source".folder as folder where folder.id=temprow.id into v_parentid; 
42
43                                                WHILE v_parentid is not null 
44                                                LOOP 
45                                                   insert into idsTable(Id) select folder.id from "test.source".folder where folder.id = v_parentid and folder.id not in (select tt2.id from idsTable as tt2); 
46                                                   select folder.parentid from "test.source".folder as folder  where folder.id = v_parentid  into v_parentid; 
47                                                END LOOP;
48
49                                            END LOOP;
50
51                                        return query select 
52                                        main.Id,
53                                        main.Title,
54									    main.ParentId,									
55            							main.No,
56	    								main.QualificationId,
57		    							ProfessioanlQualification.StandartId,                        
58			    						main.isdeleted,	
59                                        (case when (select count(*) from "test.instance".Question where sourcequestionId in (select quest.id from "test.source".question as quest where folderid in (select * from "test.source".folder_selectall(main.id) )))>0 then true else false end), 
60                                        main.TypeFolder,
61                                        --main.sectionTheoryId,
62                                        main.InitialData
63                                        from "test.source".Folder main 
64                                        join idsTable as jt on jt.id = main.id
65                                        left outer join dict.professionalqualification as ProfessioanlQualification on ProfessioanlQualification.id = main.qualificationid;
66
67                                      end;
68$BODY$;