I need to make a limit after inserting an with as but it does not work, the following example, the obj_json is a parameter of a function:
WITH sub_query AS (
SELECT
hce_id_pk,
per_numeroidentificacion,
per_apellidopaterno || COALESCE(' ' || per_apellidomaterno, '') ||
COALESCE(' ' || per_nombres, '') per_nombres_completos,
CASE WHEN hce_estado = FALSE
THEN 'PASIVO'
ELSE 'ACTIVO' END hce_estado_descripcion
FROM sga_adm_historiaclinica hce
LEFT JOIN sga_adm_archivoubicacion arch ON arch.hce_id_fk = hce.hce_id_pk
JOIN sga_adm_paciente pac ON pac.pac_id_pk = hce.pac_id_fk
JOIN sga_adm_persona per ON per.per_id_pk = pac.per_id_fk
ORDER BY aub_ubicacion
)
SELECT json_agg(sub_query)
INTO mensaje
FROM sub_query
WHERE
per_nombres_completos LIKE '%' || coalesce(trim(upper(obj_json ->> 'search')), per_nombres_completos) || '%'
OR
per_numeroidentificacion LIKE '%' || coalesce(trim(obj_json ->> 'search'), per_numeroidentificacion) || '%'
LIMIT 15;