Работа с таблицами FSPG

Основная документация по работе с таблицами Falcon Space находится здесь. 

Отличия: 

• SELECT 2 пропущен (это поле добавлено в Total следующего SELECT). Т.е. в PG версии SELECT 1 это данные таблицы, а SELECT 2 - это различные настройки таблицы (то, что было в SELECT 3 для TSQL версии). 

Пример процедуры GetItems:

CREATE OR REPLACE PROCEDURE public.crud_formcols_getitems(OUT res text, IN filters text, IN sort text, IN direction text, IN page integer, IN pagesize integer, IN username text)
 LANGUAGE plpgsql
AS $procedure$
declare searchtext text;
formid int;

BEGIN	
	select value->>'value' from json_array_elements(filters::json) 
	where value->>'key' = 'code' limit 1 into searchtext;
	select value->>'value' from json_array_elements(filters::json) 
	where lower(value->>'key') = 'itemid' limit 1 into formid;
	CREATE TEMP TABLE ids ON COMMIT DROP AS                        
	select id 
  	from "as_formCols"
  	where 
    	"formID" = formid 
        and  (isnull(searchtext, '')='' or code like '%'||searchtext||'%');
  
    CREATE TEMP TABLE sel1 ON COMMIT DROP AS                        
	select id,
      	isnull(title,'') title, 
        --'<a href="#" class="as-form-modal" data-code="langField" data-itemid="formCol_'||id::text||'_title" data-big="1"><i class="fa fa-language text-success" aria-hidden="true"></i></a>' as desc_title,
        isnull(code, '') code,  
    	isnull((select name from "as_dataTypes" where id = "datatypeID"), '') "datatypeName",
    	isnull(placeholder, '') placeholder,
      --  '<a href="#" class="as-form-modal" data-code="langField" data-itemid="formCol_'||id::text||'_placeholder" data-big="1"><i class="fa fa-language text-success" aria-hidden="true"></i></a>' as desc_placeholder,
        isnull(tooltip, '') tooltip,
       -- '<a href="#" class="as-form-modal" data-code="langField" data-itemid="formCol_'||id::text||'_tooltip" data-big="1"><i class="fa fa-language text-success" aria-hidden="true"></i></a>' as desc_tooltip,
    	isnull(ord, 0) ord,
    	iif("isRequired"=TRUE, 1, 0) "isRequired" , 
        isnull((select '<a href="#" class="as-sp-link text-primary'||'" data-control="fm" 
           data-entity="'|| f.code || '_'||col.code||'" data-operation="dict" 
           title="'|| f.code || ' - '||col.code||' dict"><i class="fa fa-database"></i></a>'          
         from as_forms f where id = col."formID"), '') "sqlSource",  
    	isnull(width, '') width,
        isnull("dependentCols", '') "dependentCols"
    
    from "as_formCols" col where id in (select id from ids)
	order by id desc
	LIMIT pagesize OFFSET (page - 1) * pagesize;
	

	CREATE TEMP TABLE sel2 ON COMMIT DROP AS                        
	select 'h3' headerTag, (select count(*) from ids) cnt;

	
     select to_jsonb(t1.*) from (
                select                
                (select json_agg(e.*) from sel1 e) sel1 ,
				(select json_agg(e.*) from sel2 e) sel2				
     ) t1 
     into res;         
        
END;
$procedure$

Особое внимание обратите как извлекаются параметры из filters коллекции.