PostgreSQL select limit with with as does not work

1

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;
    
asked by Vinicio Gomez 09.07.2018 в 22:08
source

0 answers