aonestar.public

Parameters

Name	Type	Mode
i_json_info	jsonb	IN
i_page	integer	IN (DEFAULT NULL)
i_limit	integer	IN (DEFAULT NULL)
i_sort_field	text	IN (DEFAULT NULL)
i_sort_order	text	IN (DEFAULT NULL)

Definition

declare
	v_msg_text text;
	v_sqlstate text;
	v_detail text;
	v_hint text;
	v_context text;

v_sql text;
    v_result json;
    v_limit integer := COALESCE(i_limit, 20);
    v_page integer := COALESCE(i_page, 1);
    v_offset integer := (v_page - 1) * v_limit;

v_order_by text := '';
    v_where text := 'WHERE TRUE';

key text;
    val text;
    i int := 1;

sort_fields text[];
    sort_orders text[];
    valid_sort_clauses text[] := '{}';
   
   
	BEGIN
		-- Create by T.Supol 4-Apr-2025
		-- สร้าง WHERE clause จาก i_json_info
    FOR key, val IN SELECT * FROM jsonb_each_text(i_json_info)
    LOOP
        v_where := v_where || format(' AND %I = %L', key, val);
    END LOOP;

-- สร้าง ORDER BY clause ที่สัมพันธ์กับ key ใน i_json_info
    IF i_sort_field IS NOT NULL THEN
        sort_fields := string_to_array(i_sort_field, ',');
        sort_orders := string_to_array(COALESCE(i_sort_order, ''), ',');

FOR i IN 1 .. array_length(sort_fields, 1)
        LOOP
            IF i_json_info ? trim(sort_fields[i]) THEN
                valid_sort_clauses := array_append(
                    valid_sort_clauses,
                    format('%I %s',
                        trim(sort_fields[i]),
                        CASE
                            WHEN i <= array_length(sort_orders, 1)
                                 AND lower(trim(sort_orders[i])) = 'desc' THEN 'DESC'
                            ELSE 'ASC'
                        END
                    )
                );
            END IF;
        END LOOP;

IF array_length(valid_sort_clauses, 1) > 0 THEN
            v_order_by := 'ORDER BY ' || array_to_string(valid_sort_clauses, ', ');
        END IF;
    END IF;

-- สร้าง SQL
    v_sql := format(
        $f$
        WITH filtered AS (
            SELECT * FROM my_table
            %s
        ),
        total_count AS (
            SELECT COUNT(*) AS total FROM filtered
        ),
        paged AS (
            SELECT * FROM filtered
            %s
            LIMIT %s OFFSET %s
        )
        SELECT json_build_object(
            'data', COALESCE(json_agg(paged), '[]'::json),
            'paging', json_build_object(
                'current', %s,
                'limit', %s,
                'total_rows', (SELECT total FROM total_count),
                'row_count', (SELECT COUNT(*) FROM paged)
            )
        )
        FROM paged
        $f$,
        v_where,
        v_order_by,
        v_limit, v_offset,
        v_page, v_limit
    );

EXECUTE v_sql INTO v_result;
    RETURN v_result;
	
   exception
		when others then
		get stacked diagnostics
		v_msg_text = message_text,
		v_sqlstate = returned_sqlstate,
		v_detail = pg_exception_detail,
		v_hint = pg_exception_hint,
		v_context = pg_exception_context;
		
		return fn_handle_error(
		v_sqlstate,
		v_msg_text,
		v_detail,
		v_hint,
		v_context,
		'sp_center_search_json',
		null
);
		
END;